Update query doesn't like value formatted for a different region. (1 Viewer)

[bushwacka666]

Hello again all,

We have a form with a text box called txtEditHaul (format is Fixed) and when a user types a figure in this field it needs to update a couple of Currency fields in a table. This is the original VBA code in the After Update of the box:

DoCmd.RunSQL ("UPDATE [Order Details] SET ODHaulageRate = " & Me.txtEditHaul & " Where [ODMN] = " & Me.ODMN)
   If Me.chkUsePallet = True Then
         DoCmd.RunSQL ("UPDATE [Order Details] SET ODJobRate = " & Me.txtEditHaul * Me.ODPallets & " Where [ODMN] = " & Me.ODMN)
   Else
         DoCmd.RunSQL ("UPDATE [Order Details] SET ODJobRate = " & Me.txtEditHaul * Me.ODQty & " Where [ODMN] = " & Me.ODMN)
   End If

Seems straightforward enough and, indeed, when this is run on a computer set to UK region, it works flawlessly. The catch is that a user needs to have their computer’s region set to Italy - in this case any entered figure with a decimal then causes the code to fail with a syntax error on the very first line.

Now I know that the crux of the issue seems to be that the Italians reverse their commas and decimal points, so for example 1,000.50 in the UK would be written as 1.000,50 in Italy. I’ve been testing with the figure 4.25/4,25 (i.e. no 1000 separator) so the problem seems to be the comma as a decimal. I could easily enter 4,25 directly into the table fields without issue so I know it’ll accept the figure, but the Update query doesn’t want to know.

From what I’ve been able to figure out thus far in trying to research the issue, the DoCmd.RunSQL command can’t really deal with these kind of scenarios. Finding a solution is very difficult but I did come across one suggestion which I have tried, using a string and Val function. The amended code is as follows:

Dim strSql as string
Dim strSql2 as string

strSql = ("UPDATE [Order Details] SET ODHaulageRate = Val(" & Me.txtEditHaul & ") Where [ODMN] = " & Me.ODMN)
   If Me.chkUsePallet = True Then
        strSql2 = ("UPDATE [Order Details] SET ODJobRate = Val(" & Me.txtEditHaul * Me.ODPallets & ") Where [ODMN] = " & Me.ODMN)
   Else
        strSql2 = ("UPDATE [Order Details] SET ODJobRate = Val(" & Me.txtEditHaul * Me.ODQty & ") Where [ODMN] = " & Me.ODMN)
   End If

' check your sql and post if does not work
    debug.print strsql
    currentDb.execute strSql
    currentDb.execute strSql2

The syntax error no longer occurs, however I now get a new error about having the wrong number of arguments. The debug again takes me to the first line (after the Dims):

This possibility wasn’t mentioned on the site where I found this solution, and I can’t see any other issues with the formatting of this code, so I presume the comma “decimal” is still tripping things up despite the Val formatting. The debug.print strsql line in the code results in UPDATE [Order Details] SET ODHaulageRate = Val(4,25) Where [ODMN] = 1394063248

I've also tried the above using CDec instead of Val, but no joy there either.

Really struggling to find a solution that’s going to work here. If anybody could please provide any suggestions, I would sincerely appreciate it. Many thanks in advance.

 

[Gasman]

What does val(4,25) show on the errant computer?

 

[bushwacka666]

It's mentioned in the first post: UPDATE [Order Details] SET ODHaulageRate = Val(4,25) Where [ODMN] = 1394063248

 

[Gasman]

No, if you print that val in the debug window, what does it show?
If I try that on a uk computer I get wrong number of arguments or invalid property assignments.

Have you tried setting Me.txtEditHaul to Currency?

 

[Pat Hartman]

Is the currency field defined as Currency as the data type? Is it also defined as Currency as the format? If yes to both, try changing the data type to Decimal or Single.

 

[MajP]

Resolve the function ahead of time not in the query.

= (" & val( Me.txtEditHaul * Me.ODPallets) & ")
but instead of val i would use CCur. But if both of these are currencies or numerics then even that is not necessary.

 

[Pat Hartman]

Is the BE SQL Server? Maybe that doesn't have the correct country version defined so it is conflicting with Access. If so, MajP's solution will probably work. Or use that solution but instead of CCur, convert to Single or Decimal instead.

Given that the punctuation is never stored with the data in a numeric data type, this is really confusing.

 

[bushwacka666]

Okay...

What does val(4,25) show on the errant computer?

So you're right: trying to print Val(4,25) in the Immediate window also gives me wrong number of arguments or invalid property assignments.

If I try ?Val(txtEditHaul) it returns 4 and if I try ?CDec(txtEditHaul) it returns 4,25

So it may be that Val is the wrong way to go here? Although I did try CDec before and had no luck with that either: then I get a completely new error:

Adding dbSeeChanges after the CurrentDb.Execute strSql line then allows the whole thing to run without erroring, but the required table fields don't get updated.

Is the currency field defined as Currency as the data type? Is it also defined as Currency as the format? If yes to both, try changing the data type to Decimal or Single.

The fields in the table that need updating (ODHaulageRate and ODJobRate) are both set as data type Currency. Both fields show in the form with format as Fixed, (same as txtEditHaul,) if that's what you meant.


Will have to look at this further tomorrow when I have more time. Thanks all for your input so far.

 

[Josef P.]

Use Str(...)

SqlText = "UPDATE [Order Details] SET ODHaulageRate = " & CStr(1.2345) & " Where ..."
Debug.Print SqlText ' => 1,2345

SqlText = "UPDATE [Order Details] SET ODHaulageRate = " & Str(1.2345) & " Where ..."
Debug.Print SqlText ' => 1.2345

BTW:
@MajP: one minor fix in CSql: remove C from CStr(Value)

           Case sdt_Numeric
                 If IsNumeric(Value) Then
                    Sql = Str(Value)
                 Else
                    MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation
                    Exit Function
                 End If

 

[bushwacka666]

Resolve the function ahead of time not in the query.

= (" & val( Me.txtEditHaul * Me.ODPallets) & ")
but instead of val i would use CCur. But if both of these are currencies or numerics then even that is not necessary.

Sorry, MajiP, I'm trying to wrap my head around your suggestion here. Is this the sort of thing you mean?

        Main = (" & CCur(Me.txtEditHaul) & ")
        MainAndPallets = (" & CCur(Me.txtEditHaul * Me.ODPallets) & ")
        MainAndQty = (" & CCur(Me.txtEditHaul * Me.ODQty) & ")
        
        strSql = ("UPDATE [Order Details] SET ODHaulageRate = Main Where [ODMN] = " & Me.ODMN)
        If Me.chkUsePallet = True Then
            strSql2 = ("UPDATE [Order Details] SET ODJobRate = MainAndPallets Where [ODMN] = " & Me.ODMN)
        Else
            strSql2 = ("UPDATE [Order Details] SET ODJobRate = MainAndQty Where [ODMN] = " & Me.ODMN)
        End If

 

[Josef P.]

Maybe this description will help:

VBA-(SQL)-String Tutorial

Companion text to an extensive VBA-(SQL)-String Video-Tutorial for Beginners

codekabinett.com

/edit:
Perhaps a look at the example application Northwind 2 will also help. In NW2, the StringFormatSQL function is responsible for converting values into SQL text.

 

[MajP]

My point is if you are writing SQL strings in VBA, I wouldnot write a function that gets resolved in SQL. Resolve the values ahead of time in VBA.

Although it works (and there may be a case to do it such as you do not want to deal with delimiters), but often you can debug and error check ahead of time and usually a lot easier.
(I will purposely put a mistake in the string)

StrSql = "Select ..... where SomeField = [Forms]![SomeForm]![SomControl]"

Instead resolve the value ahead of time
StrSql = "Select ... where SomeField = " & me.SomeControl

Now I can debug that
debug.print strSql
and I can see something like
Select ... where SomeField = 7

So in your example when I debug I see something like
UPDATE [Order Details] SET ODJobRate = 4.25
instead of something like
UPDATE [Order Details] SET ODJobRate = Val(4,25)
forcing the Val to occur in SQL instead of ahead of time.

 

[Josef P.]

So in your example when I debug I see something like
UPDATE [Order Details] SET ODJobRate = 4.25

.. Because you use a decimal point instead of a decimal comma in Windows settings.

If , is set as decimal separator in Windows:

Dim strSQL as String
dim dblODJobRate as double
dblODJobRate = 4.25
strSql = "UPDATE [Order Details] SET ODJobRate = " & dblODJobRate
Debug.Print strSql
' => Output: UPDATE [Order Details] SET ODJobRate = 4,25

' use Str(...) instead of implicit CStr(...)
strSql = "UPDATE [Order Details] SET ODJobRate = " & Str(dblODJobRate)
Debug.Print strSql
' => Output: UPDATE [Order Details] SET ODJobRate =  4.25

'use StringFormatSQL from NW2
strSQL = "UPDATE [Order Details] SET ODJobRate = {0}"
strSQL = StringFormatSQL(strSQL, dblODJobRate)
Debug.Print strSQL
' => Output: UPDATE [Order Details] SET ODJobRate = 4.25

 

[bushwacka666]

Okay, after going through both MajP & Josef P.'s last posts, we may finally be making some progress. What I've now got is this:

Dim strSql As String, strSql2 As String
Dim dblODHaulageRate As Double

dblODHaulageRate = Me.txtEditHaul

   strSql = "UPDATE [Order Details] SET ODHaulageRate = " & str(dblODHaulageRate) & " WHERE [ODMN] = " & Me.ODMN
   If Me.chkUsePallet = True Then
       strSql2 = "UPDATE [Order Details] SET ODJobRate = " & str(dblODHaulageRate) * Me.ODPallets & " WHERE [ODMN] = " & Me.ODMN
   Else
       strSql2 = "UPDATE [Order Details] SET ODJobRate = " & str(dblODHaulageRate) * Me.ODQty & " WHERE [ODMN] = " & Me.ODMN
   End If
  
Debug.Print strSql
Debug.Print strSql2
 CurrentDb.Execute strSql, dbSeeChanges
 CurrentDb.Execute strSql2, dbSeeChanges

In initial testing it appears as though ODHaulageRate is now getting set correctly when I enter 4,25 into the txtEditHaul box. The Debug.Print line returns UPDATE [Order Details] SET ODHaulageRate = 4.25 WHERE [ODMN] = 1394063248 and 4,25 is showing in the table itself. No errors occurred.

ODJobRate is another matter, however. For this example case, the chkUsePallet criteria is True so it should be doing SET ODJobRate = " & str(dblODHaulageRate) * Me.ODPallets ODPallets in this example is just 1, so it should be 4.25*1 = 4.25

Unfortunately that very basic maths is somehow being ignored. The Debug.Print line for this returns UPDATE [Order Details] SET ODJobRate = 425 WHERE [ODMN] = 1394063248 and indeed 425 is what gets inserted into the table. Essentially this part is completely removing the decimal from the figure as if it was never there to begin with?

 

[MajP]

Because you use a decimal point instead of a decimal comma in Windows settings

My point was you will create a resolved value in the sql string and not a function that then gets resolved when the sql executes.

 

[Gasman]

What do you get in the immediate window?

? str(4.25) * 1
 4.25

 

[arnelgp]

maybe create a function that will convert your numbers:

Option Compare Database
Option Explicit


#If VBA7 Then
Declare PtrSafe Function GetUserDefaultLCID Lib "kernel32" () As Long
Declare PtrSafe Function GetSystemDefaultLCID Lib "kernel32" () As Long
Declare PtrSafe Function GetLocaleInfoA Lib "kernel32" ( _
    ByVal Locale As Long, _
    ByVal LCType As Long, _
    ByVal lpLCData As String, _
    ByVal cchData As Long) As Long
#Else
Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long
Declare Function GetLocaleInfoA Lib "kernel32" ( _
    ByVal Locale As Long, _
    ByVal LCType As Long, _
    ByVal lpLCData As String, _
    ByVal cchData As Long) As Long
#End If

Const LOCALE_SDECIMAL As Long = &HE   ' Decimal separator
Const LOCALE_STHOUSAND As Long = &HF  ' Thousand separator
Const LOCALE_SCURRENCY As Long = &H14 ' Currency symbol
Const LOCALE_SSHORTDATE As Long = &H1F ' Short date format

Sub CheckSystemLocale()
    Dim userLCID As Long
    Dim systemLCID As Long

    userLCID = GetUserDefaultLCID()
    systemLCID = GetSystemDefaultLCID()

    MsgBox "User Locale ID (LCID): " & userLCID & vbCrLf & _
           "System Locale ID (LCID): " & systemLCID
End Sub

Public Function SystemLocaleID() As Long
SystemLocaleID = GetSystemDefaultLCID
End Function


Function GetLocaleSetting(ByVal LCType As Long) As String
    Dim lcid As Long
    Dim buffer As String * 256
    Dim ret As Long

    lcid = GetUserDefaultLCID()
    ret = GetLocaleInfoA(lcid, LCType, buffer, Len(buffer))

    If ret > 0 Then
        GetLocaleSetting = Left(buffer, ret - 1)
    Else
        GetLocaleSetting = "Unknown"
    End If
End Function

Private Sub ShowLocaleSettings()
    MsgBox "Decimal Separator: " & GetLocaleSetting(LOCALE_SDECIMAL) & vbCrLf & _
           "Thousand Separator: " & GetLocaleSetting(LOCALE_STHOUSAND) & vbCrLf & _
           "Currency Symbol: " & GetLocaleSetting(LOCALE_SCURRENCY) & vbCrLf & _
           "Short Date Format: " & GetLocaleSetting(LOCALE_SSHORTDATE)
End Sub


Public Function ToEnglishDecimal(ByVal vNum As Variant) As Double
    vNum = vNum & ""
    If SystemLocaleID() = 1040 Then  'Italy
        vNum = Replace$(Replace$(vNum, ".", ""), ",", ".")
    Else
        vNum = Replace$(vNum, ",", "")
    End If
    ToEnglishDecimal = Val(vNum)
End Function

Your code:

Dim strSql As String, strSql2 As String
Dim dblODHaulageRate As Double

dblODHaulageRate = Me.txtEditHaul

   strSql = "UPDATE [Order Details] SET ODHaulageRate = " & ToEnglishDecimal(dblODHaulageRate) & " WHERE [ODMN] = " & Me.ODMN
   If Me.chkUsePallet = True Then
       strSql2 = "UPDATE [Order Details] SET ODJobRate = " & ToEnglishDecimal(dblODHaulageRate) * ToEnglishDecimal(Me.ODPallets) & " WHERE [ODMN] = " & Me.ODMN
   Else
       strSql2 = "UPDATE [Order Details] SET ODJobRate = " & ToEnglishDecimal(dblODHaulageRate) * ToEnglishDecimal(Me.ODQty) & " WHERE [ODMN] = " & Me.ODMN
   End If
  
Debug.Print strSql
Debug.Print strSql2
 CurrentDb.Execute strSql, dbSeeChanges
 CurrentDb.Execute strSql2, dbSeeChanges

 

[Josef P.]

In initial testing it appears as though ODHaulageRate is now getting set correctly when I enter 4,25 into the txtEditHaul box. The Debug.Print line returns UPDATE [Order Details] SET ODHaulageRate = 4.25 WHERE [ODMN] = 1394063248 and 4,25 is showing in the table itself. No errors occurred.

SQL is correct now.
If 4,25 ist shown in table, this is also correct, because, is the decimal separator.

str(dblODHaulageRate) * Me.ODPallets

String * Numeric = ?
=> implicit conversion (with decimal comma in Windows settings):
CDbl("4.25") * 1 = 425 * 1 = 425
CDbl("4,25") * 1 = 4.25 * 1 = 4.25

Str(..) is to convert a number into a string with decimal point.
=>Calc first, then convert to string.

.. = "..." & Str(dblODHaulageRate * Me.ODPallets) & "..."

@arnelgp: Str(..) does the job well. You don't need anything more complex

 

[bushwacka666]

What do you get in the immediate window?

? str(4.25) * 1
 4.25

Yeah, the immediate window for this gave:

? str(4.25) * 1
 425

It will return 4.25 after I implement Josef P.'s recommendation above though.

maybe create a function that will convert your numbers...

I see that suggestion makes use of a Replace command, attempting to manually swap out commas with decimal points. I know a colleague of mine (who admittedly knows far more about this stuff than I ever will) already attempted something along those lines, and he didn't have any joy with it.

SQL is correct now.
If 4,25 ist shown in table, this is also correct, because, is the decimal separator.


String * Numeric = ?
=> implicit conversion (with decimal comma in Windows settings):
CDbl("4.25") * 1 = 425 * 1 = 425
CDbl("4,25") * 1 = 4.25 * 1 = 4.25

Str(..) is to convert a number into a string with decimal point.
=>Calc first, then convert to string.

.. = "..." & Str(dblODHaulageRate * Me.ODPallets) & "..."

@arnelgp: Str(..) does the job well. You don't need anything more complex

Okay, we might be in business here! Moved that closing bracket and now ODJobRate looks like it's getting set as expected as well.

Both Debug.Print returns:

UPDATE [Order Details] SET ODHaulageRate = 4.25 WHERE [ODMN] = 1394063248
UPDATE [Order Details] SET ODJobRate = 4.25 WHERE [ODMN] = 1394063248

Table updated:

Will definitely want to do further testing with a few other examples, and also make sure it still behaves itself if the computer is set on the default UK region, but so far this looks promising - certainly a hell of a lot closer than we were a week ago! Thank you!

 

[Josef P.]

A few examples with the familiar problems:

Private Sub BasicPrinciples()

    Dim SqlCriteria As String
 
'
' convert numeric values to a SQL string
' --------------------------------------

    SqlCriteria = "NumericField = " & 5
    Debug.Print """NumericField = "" & 5          =>", SqlCriteria
    'output: "NumericField = " & 5          =>         NumericField = 5
    ' data types logic: String = String & numeric => implicit conversion from numeric to string
    ' ... is equal to explicit conversion with CStr function
    SqlCriteria = "NumericField = " & CStr(5)
    Debug.Print """NumericField = "" & CStr(5)    =>", SqlCriteria
    'output: "NumericField = " & CStr(5)    =>         NumericField = 5
 
    ' now with a decimal number
    SqlCriteria = "NumericField = " & CStr(1.23)
    Debug.Print """NumericField = "" & CStr(1.23) =>", SqlCriteria
    'output: "NumericField = " & CStr(1.23) =>         NumericField = 1,23
    ' CStr(1.23) => with decimal comma => "1,23"
    ' => Sql needs 1.23 not 1,23 for a decimal number => use Str function
    SqlCriteria = "NumericField = " & Str(1.23)
    Debug.Print """NumericField = "" & Str(1.23)  =>", SqlCriteria
    'output: "NumericField = " & Str(1.23)  =>         NumericField =  1.23
    Debug.Print
 
'
' convert date values to a SQL string
' -----------------------------------

    ' date format for Access/DOA/Ansi89 SQL dialect: #month/day/Year#
    ' => use format(...)
    ' This is often suggested: "#" & Format(DateValue, "mm/dd/yyyy") & "#"
    ' => What happens if . is the date separator?
    SqlCriteria = "DateField = #" & Format(#2/3/2025#, "mm/dd/yyyy") & "#"
    Debug.Print """DateField = #"" & Format(#2/3/2025#, ""mm/dd/yyyy"") & ""#""   =>", SqlCriteria
    'output: "DateField = #" & Format(#2/3/2025#, "mm/dd/yyyy") & "#"   =>         DateField = #02.03.2025#
    ' => DateField = #02.03.2025# ... and in de/at default date format this is 2nd March 2025 (3/2/2025)!
    ' reason: / is the placeholder for the date separator of the format function!
    ' correct format:
    SqlCriteria = "DateField = #" & Format(#2/3/2025#, "mm\/dd\/yyyy") & "#"
    Debug.Print """DateField = #"" & Format(#2/3/2025#, ""mm\/dd\/yyyy"") & ""#"" =>", SqlCriteria
    'output: "DateField = #" & Format(#2/3/2025#, "mm\/dd\/yyyy") & "#" =>         DateField = #02/03/2025#
 
    'easy to read alternative: use ISO date format: yyyy-mm-dd + mask # with \# inside format string
    SqlCriteria = "DateField = " & Format(#2/3/2025#, "\#yyyy-mm-dd\#")
    Debug.Print """DateField = "" & Format(#2/3/2025#, ""\#yyyy-mm-dd\#"")      =>", SqlCriteria
    'output: "DateField = " & Format(#2/3/2025#, "\#yyyy-mm-dd\#")      =>         DateField = #2025-02-03#
 
    ' a very poor solution: use the numeric value of date
    ' Although this works for Access tables, it will return the wrong day when using linked SQL server tables, for example.
    SqlCriteria = "DateField = " & CStr(CLng(#2/3/2025#))
    Debug.Print """DateField = "" & CStr(CLng(#2/3/2025#))                    =>", SqlCriteria
    'output: "DateField = " & CStr(CLng(#2/3/2025#))                    =>         DateField = 45691
 
End Sub

And to avoid having to write it out in such a complicated way every time, you just create helper functions.