Problem with Update query

  • Thread starter Thread starter Gina
  • Start date Start date
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
 
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
 
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
 
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
 
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...
 
Back
Top