Decimal seperator and Doubles in an Update Query

  • Thread starter Thread starter Ward Visser
  • Start date Start date
W

Ward Visser

Dear all, who knows whats going on here?

If i use a "," in my Windows Language Settings as Decimal Seperator, and a
"." as thousand seperator (which is standard in the Netherlands), my Update
Query generates the Error message:
"Runtime Error 3144 -The Update instruction has a syntax error"
Changing "," and "." to US style solves my problem, but then my customer
starts complaining ...

Simplified set up is like:
My Access has a Table "tblTest" which has a tree Fields:
"Jear" (Data Type: Text). And the Value for the first record is "1995"
"ReadField" (Data Type: Number, Field Sze Double, Decimal Places: Auto),
with Value 10.023,25.
"WriteField" (Data Type: Number, Field Sze Double, Decimal Places: Auto).
should become the same as ReadField

My VBA looks like:
Sub Test()

Dim db As DAO.Database
Dim dblReadField As Double
Set db = CurrentDb

dblReadField = DLookup("[ReadField]", "tblTest", "[Jear] ='1995'")
db.Execute "UPDATE tblTest SET [WriteField] = " & dblReadField & " WHERE
Jear ='1995'"

End Sub


In Debug Mode with my mouse on dblReadField is shows f.e. "10023,25" (mind
te comma)
My WindowsXP is Dutch an AccessXP is English, but thats might not be the
problem.

How can i solve this problem?
Thanks for your help

Ward Visser
 
Hi,

The easiest way is to push the value into a control (hidden) and use the
FORMS syntax:


db.Execute " UPDATE test SET
writeField=FORMS!FormNameHere!ControlName "

Otherwise, try to look for Replace, such as with:

db.execute "UPDATE test SET writeField=" & Replace( myVariable, ",",
".")



Hoping it may help,
Vanderghast, Access MVP
 
I would guess the language version don't play well together. Instead of
creating the SQL in the VBA code. Create a parameter query.

i.e. UPDATE tblTest SET [WriteField] = prmRead WHERE Jear ='1995'"

In the Parameters, set the prmRead to be a Double

Execute the query by using the following procedure.

Function RunQueryParameter(strQuery As String, strParameter As String,
varValue As Variant, _
Optional ByRef rlngRecAff As Long) As Boolean
' Comments : Run an Action query that requires a parameter
' Parameters: strQuery - a saved ACTION query to run
' strParameter - name of parameter to specify
' varValue - the value to assign the parameter
' Returns : True if successful, False otherwise

Dim cmd As ADODB.Command
Dim cat As New ADOX.Catalog

'Open the Catalog to be able to see the stored Queries
cat.ActiveConnection = Application.CurrentProject.Connection
'Set the Command Object to the desired Query
Set cmd = cat.Procedures(strQuery).Command
'Set the Parameter value. Don't forget the brackets!
'cmd.Parameters("[" & strParameter & "]") = varValue
'Run the query

'Because there is only one parameter, send with the execute method
cmd.Execute rlngRecAff, varValue

RunQueryParameter = True

End Function

*** This function is brought to you by:

Total Visual Sourcebook
http://www.fmsinc.com/products/sourcebook/index.html ***


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Ward Visser said:
Dear all, who knows whats going on here?

If i use a "," in my Windows Language Settings as Decimal Seperator, and a
"." as thousand seperator (which is standard in the Netherlands), my Update
Query generates the Error message:
"Runtime Error 3144 -The Update instruction has a syntax error"
Changing "," and "." to US style solves my problem, but then my customer
starts complaining ...

Simplified set up is like:
My Access has a Table "tblTest" which has a tree Fields:
"Jear" (Data Type: Text). And the Value for the first record is "1995"
"ReadField" (Data Type: Number, Field Sze Double, Decimal Places: Auto),
with Value 10.023,25.
"WriteField" (Data Type: Number, Field Sze Double, Decimal Places: Auto).
should become the same as ReadField

My VBA looks like:
Sub Test()

Dim db As DAO.Database
Dim dblReadField As Double
Set db = CurrentDb

dblReadField = DLookup("[ReadField]", "tblTest", "[Jear] ='1995'")
db.Execute "UPDATE tblTest SET [WriteField] = " & dblReadField & " WHERE
Jear ='1995'"

End Sub


In Debug Mode with my mouse on dblReadField is shows f.e. "10023,25" (mind
te comma)
My WindowsXP is Dutch an AccessXP is English, but thats might not be the
problem.

How can i solve this problem?
Thanks for your help

Ward Visser
Click to expand...
 
Literal constants in Jet SQL statements must use US separators for numbers
and US formats for dates. Two suggestions might be:

1. Use a parameter query instead of dynamically building your SQL statement
each time. This could have positive effects on performance, too.

2. Do the locale-aware conversion in the SQL statement, for example,
something like:

db.Execute "UPDATE tblTest SET [WriteField] = CDbl(""" & dblReadField &
""") WHERE Jear ='1995'"
 
Thanx all guys,

I started with the Brian's "locale-aware conversion in the SQL statement":
It worked, Great!
(I thried this before, but placed the conversion before the SQL
statement: didn't work.)

You are right: I'am gonna put this in a paramater query.

Regards,

Ward Visser
 
Back
Top