Currency Field Format Correction

[Louise]

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,

 

[Oliver Seiffert]

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.

 

[J.Dusart]

I have the same kind of problem, and the solution i've used is this one :

first of all, i import the text file in one import table, cleaned at each
import, where all columns are "text"

Then i open two recordsets : one on the import table, the other on the final
table ( where columns are correctly defined ), and add a line in the final
table for each line in the import table, carrying the right conversions with
cinbt, cdbl, ... and a function that removes all ".", then converts the ","
with "."

hope this helps.