Problem with Update query

G

Gina

Hi

this is my update syntax and i get a syntax error

SQL = "UPDATE Invoice SET Invoive.InvoiceDate = '" & datum & "',
Invoive.InvoiceSum = " & FormatNumber(sum, 2) & ", Invoice.Tax= " &
FormatNumber(tax, 2) & ", Invoice.Total = " & FormatNumber(total, 2) & "
WHERE (Invoice.ID = " & id & ")"

please help
Thanks
Gina
 
A

Alex Dybenko

Try to debug.pring your SQL (or response.write in case you use ASP) - and
then check constructed SQL, probably you missed some parameter. also you can
create a new query in access and paste SQL there - then access will tell you
what is wrong there
 
G

Gina

Thanks Alex,

I've tried what you suggested (access - query - SQL)
it worked fine as long as the currency values didn't contain a decimal
separator ....
when I typed values 22,6 and 30,8 ( well I work in Germany where we use a ,
for decimal separation)

well but now I know what the reason for the syntax error was

any idea how I could solve that ... I do not want to change the table field
where these values are stored

Thanks so much

great idea to use access to check the SQL!!!!!!

Gina
 
A

Alex Dybenko

Hi,
yes, SQL need dot as separator.
you can replace comma with dot like this:

.... & replace(FormatNumber(sum, 2) ,",",".") & ...

or just make your own function with formats data and replcae comma with dot
and use it instead of FormatNumber
 
G

Gina

Thanks, Alex.

solved it :)
Gina

Alex Dybenko said:
Hi,
yes, SQL need dot as separator.
you can replace comma with dot like this:

... & replace(FormatNumber(sum, 2) ,",",".") & ...

or just make your own function with formats data and replcae comma with dot
and use it instead of FormatNumber
Click to expand...
 
Top