RE: Currency Field Format Correction

Hello,

try following functions in ms access 2000+.

First one, I have found in the web.

Function SF_removeAllOnce(ByVal Haystack As String, ByVal Needle As String)
As String
'remove all occurrences of needle in haystack exactly once
'if needle is empty or not found, haystack is returned
'if needle is equal to haystack, a zero-length string is returned
'SF_removeAllOnce("1122a1122","12") returns "12a12"
Dim i As Long
If SF_isNothing(Needle) Then
SF_removeAllOnce = Haystack
Else
i = InStr(1, Haystack, Needle, vbBinaryCompare)
Do While i > 0
Haystack = Left(Haystack, i - 1) & Mid(Haystack, i + Len(Needle))
i = InStr(i, Haystack, Needle, vbBinaryCompare)
Loop
SF_removeAllOnce = Haystack
End If
End Function

This will remove any given needle from a given haystack. Now the second
function.

function fctRemove(strValue as string) as string
if instrrev(strValue,",") then
strValue=sf_removeallonce(strValue,".")
strValue=left(strvalue,len(strvalue)-3) & "[Your decimal separator" &
right(strvalue,2)
else
strValue=sf_removeallonce(strValue,",")
strValue=left(strvalue,len(strvalue)-3) & "[Your decimal separator" &
right(strvalue,2)
end if
fctRemove=strvalue
End Function 'Sorry not tested, just written

Now start the query editor, and select update, double click the field and
write following into the update with part fctRemove([Your selected
Fieldname]) and run the query. Now you have a table with a correct formated
string value. If you have to do further calculations with this field you have
to add a column to your import table of the type double, and run another
update query, to fill the new field with the value now you have to type
following in the query cDbl([Your string Fieldname]) an run the query.

Hope that will help you.



--
Regards

Oliver Seiffert


"Louise" wrote:

> Hi,
>
> I have a daily source mainframe text file that I import into a table with
> field delimiters etc. 2 columns of this file are local currency values and
> within one file they can show as formatted 0,000.00 or 0.000,00 dependant on
> the currency format that market uses. This totally messes up any
> calculations that need to be done with the final output to Excel (I can't run
> the import etc in Excel as it is far too large approx 100,000 lines plus)
>
> Is there a way to standardise these? The only way I could think to do it is
> to have a macro that 'finds and replaces' all the comma's with full stops and
> then all the full stops with nothing, then multiply all the numbers by 100 to
> bring them into UK currency setting. If this is the only way, can someone
> help me by advising on some code I can use rather than manually having to do
> a find replace in the table every day, or alternatively a better way of doing
> this if there is one?
>
> Thanks so much in advance for any help,
> Kind Rgds,
.

Relevant Pages

  • Re: Implementing strstr
    ... that string doesn't appear at all). ... const char* strstr(const char* haystack, const char* needle) ...
    (comp.programming)
  • Re: PHP global namespace clogged up
    ... -string strchr(string haystack, string needle) ... int strpos(string haystack, string needel ): ...
    (comp.lang.php)
  • Documentation on strpos
    ... "Returns the numeric position of the first occurrence of needle in the ... full string as the needle parameter and the entire string will be used." ... I find the reference to "strrpos" confusing, ... haystack string." ...
    (comp.lang.php)
  • Re: need some help
    ... a pointer to the first occurrence of 1st string in 2nd string or NULL ... char *ashstrstr(char *haystack, char *needle) ... It should be strstr(needle, haystack). ... the student to write the internals of strstr. ...
    (comp.lang.c)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)