Update gives error

[Steven]

Hi all,

I got an error that says Syntax error, but what is wrong here.
If I use a MsgBox on Totaal4 then I see the correct number.
If I use only the statement after the ELSE that works fine.

Private Sub GrootteExplGeb_AfterUpdate()
Dim SQL As String
Dim Deel4a_MR As Double
Dim Deel4b_MR As Double
Dim Totaal4 As Double

Deel4a_MR = DLookup("Invlfact1_MR", "Basis_InvloedsfactorA_MR_Hulp", "INVLFCTR_A_MR_SK=2204")

Deel4b_MR = DLookup("Invlfact2_MR", "Basis_InvloedsfactorA_MR_Hulp", "INVLFCTR_A_MR_SK=2204")
Totaal4 = Me!GrootteExplGeb * Deel4a_MR + Deel4b_MR

If GrootteExplGeb > 70 And GrootteExplGeb <= 150 Then
SQL = "UPDATE Invloedsfactoren " & _
"SET InvlFctr_Perc = " & Totaal4 & _
"WHERE PROJNR_SK = " & Me!PROJNR_SK & _
" AND INVLFCTR_SK = 2401"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
'Else
' If GrootteExplGeb > 150 Then
' SQL = "UPDATE Invloedsfactoren " & _
' "SET InvlFctr_Perc = 0.8 " & _
' "WHERE PROJNR_SK = " & Me!PROJNR_SK & _
' " AND INVLFCTR_SK = 2401"
' DoCmd.SetWarnings False
' DoCmd.RunSQL SQL
' DoCmd.SetWarnings True
' End If
End If
End Sub

 

[pbaldy]

What are the data types of the field being set and the fields in the criteria? Values for text fields need to be surrounded by '.

 

[ChrisO]

Another thought...

Your regional settings may have the decimal separator set as a comma rather than a period.

So, Totaal4 may be 75,9 rather than 75.9 (for example)

"SET InvlFctr_Perc = " & Totaal4 & _
Would embed 75,9 in the string and DoCmd.RunSQL may reject it (probably would)


Whereas with 0.8: -
"SET InvlFctr_Perc = 0.8 " & _
it’s a string literal and would be okay.


If the above is correct then change: -
"SET InvlFctr_Perc = " & Totaal4 & _
to
"SET InvlFctr_Perc = " & Str(Totaal4) & _

because the Str() function bypasses regional settings and uses a period.
(The leading space, which the Str() function puts on positive numbers, will not matter.)

Hope that helps.

Chris.

 

[Steven]

Hi ChrisO,

Thx for your answer, it solved the problem. Only needed an extra " " at the end, because else he added the number on the where, something like "0.8674463WHERE" and access doesn't like that.
But it sucks that they still haven't resolve the old , and . problem in the regional settings.

But anyway it works thx alot,
Steven.

 

[ChrisO]

>>But it sucks that they still haven't resolve the old , and . problem in the regional settings.<<

Yes, in some ways I agree with that but it’s not Microsofts place to resolve it.

The comma/period thing is what is displayed in that region and Microsoft tries to conform with that display.

So, when we write: -
SQL = "UPDATE Invloedsfactoren " & _
"SET InvlFctr_Perc = " & Totaal4 & _
"WHERE PROJNR_SK = " & Me!PROJNR_SK & _
" AND INVLFCTR_SK = 2401"
we are building a string and that’s all it is, not an SQL statement, just a string.

Strings are normally used for display so, Microsoft trying to be helpful, looks up what is being used for display in regional settings.

However, on this occasion, we don’t use it for display but rather execute it as an SQL statement.

SQL was once called SEQUEL (Structured English Query Language).
But, even though it was later changed to SQL it is still only, as far as I know, parsed in English.

Now the Str() function is a very old BASIC function and probably goes back to the original BASIC language created in the 60’s. It was used to format positive/negative numerical lists where a negative had a preceding minus sign and a positive number was appended to a leading space to compensate for the position of the minus sign in negative numbers.

But the SQL parser does not mind excessive spaces so the Str() function is okay.
And, since the Str() function is so old, it is not inclined to dash off to regional settings simply because regional settings didn’t exist when it was written.

But the same is not true of the CStr() function. The CStr() is aware of regional settings and can not be used in this case. In this instance then, the much older Str() function does exactly what we want whereas the new CStr() function would fail.

We can think of Str() and CStr() as a triumph of age over youth and, because it happens so rarely, the aged are compelled to mention it when they can.

By the way, the lack of a space before the WHERE was also present in the original code.
So you may like to consider putting a space at the start of the line as you did with the following AND.
An extra space at the start of the line should not cause any problems but may save some head scratching latter on.

Chris.

 

[Steven]

Hi Chris,

Thx for the explaination, learned again a few things ;-)
It can be confusing writing a period as decimal seperator and seeing the comma in the table.

And I put the Str after the AND, this way I don't need an extra space.

Thx again and good luck,
Steven.