Problem with number seperator in sub form (1 Viewer)

[PicassoB]

Help please, can anyone see a problem with the code below
I needed to change the decimal symbol from a dot to comma for use in Europe. On changing the the settings to a comma everything works fine but for the field that I have the code in the AfterUpdate properties

Private Sub SprayCfuelC_AfterUpdate()
Me.Dirty = False
CurrentDb.Execute ("UPDATE ProcessData SET ProcessData.SprayCFuelC =" & Me.SprayCfuelC & " WHERE (((ProcessData.AuditNo)=" & Parent.[AuditNo] & "));")
Me.Recalc
End Sub

Now when the data is changed I get a run time error 3144 - syntax error in UPDATE statement, any Ideas what I am missing? when the settings were for a dot no problem!!!

 

[spikepl]

You are missing the proper way of debugging any errors in creating SQL in VBA. http://www.baldyweb.com/ImmediateWindow.htm

When you have identified the error I can give you the solution.

 

[Beetle]

What is the data type of SprayCfuelC? If it's text then you need string delimiters. Also, you don't really need all the parentheses. It looks like you built the SQL with the QBE. The QBE seems to be in love with unnecessary parentheses.

CurrentDb.Execute "UPDATE ProcessData SET ProcessData.SprayCFuelC =""" & Me.SprayCfuelC & """ WHERE ProcessData.AuditNo=" & Parent.[AuditNo]

The above assumes [AuditNo] is a number data type. If [AuditNo] is text then you will need delimiters for that as well.

 

[spikepl]

@Beetle - he's got a decimal number in SQL with non-US formating and that bites him. But what bites him more is chewing of too much in one go, hence the link to Pauls page.

 

[Beetle]

@spikepl

Point taken. Didn't read the op closely enough on the first pass.

 

[PicassoB]

First thanks for looking at this - the problem is that on changing the decimal separator to a comma, I get the error message in the field with the above code. this was supplied by bob fitz on the forum which I greatly appreciate, and worked perfectly until I changed the decimal separator
Is there something that needs changing to make this work with a comma decimal separator?