SQL UPDATE currency field problem

[JakeNL]

I try to update a currency field in my ms acess db from a program in VB6 (sp5). Herefor I execute a SQL string-command over the active connection.
The variable total is also declared a currency type variable.

The error message tells me that there is an error in the command and that it is about the comma in the expression. This must be the comma in the variable as the decimal separator. SQL needs that to be a dot instead of a comma. I tried multiple options on the variable but it keeps ending up with the comma.

The command string :

sSQLfin = "UPDATE tbl_members SET Financialx = Financialx + " & CDbl(total)


All advice and help is welcomed !!

Thanks in advance, Jack

 

[PHV]

Have tried this ?
sSQLfin = "UPDATE tbl_members SET Financialx = Financialx + " & Format(total, "0.00")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JakeNL]

Nope,

No result it comes up with a syntaxis error.

 

[PHV]

Don't know how we use personalized format in VB, however I guess it's how you'll solve your issue.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Just another guess:
& Replace(CDbl(total), ",", ".")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JakeNL]

Thanks PHV,

I only had to adjust one thing like :

Replace(CStr(totaal), ",", ".") and now it is running.

case closed and Thanks for your help and time !!!

Jack