not working in spain (1 Viewer)

[ryetee]

i have an update in some VBA that works perfectly for me but when testing on a spanish network i get a syntax error(3144).
The only thing that looks different is updating a number
In the UK version I get number = me.number where me.number is 2.35
In the Spanish version I get number = me.number where me.number is 2,35
How can I stop this.

 

[MajP]

In windows under Regional Settings, Additional Settings you can change the decimal symbol.

 

[deletedT]

Check control panel's Currency setting and see if Decimal Symbol is set correctly.

Edit:MajP you beat me

 

[ryetee]

In windows under Regional Settings, Additional Settings you can change the decimal symbol.

Is there anyway to do this so it works on my laptop and in Spanish network without doing that. If I change it on their network then other systems may not work. Be good if . and , were treated the same in access.

 

[ryetee]

Check control panel's Currency setting and see if Decimal Symbol is set correctly.

Edit:MajP you beat me

see reply to mapj

 

[ryetee]

So if I look at the table on my laptop I can see data held as nn.nnn
If I copy the table to the Spanish network it looks like nn,nnn so why can't I update to number = nn,nnn
Access can obviously differentiate between regions until I update. I could inderstand if i was trying to update spanish side with nn.nnn.
It's ludicrous
I don't want to change control panel as it will screw all their other data up there must be something else I can do

 

[theDBguy]

Hi. I get the impression that you are executing an UPDATE query using VBA, is this correct? Just as a test, try creating an UPDATE query like so:

UPDATE TableName SET NumberField=2,35

and execute it in the Spanish version. Does it work?

 

[ryetee]

Hi. I get the impression that you are executing an UPDATE query using VBA, is this correct? Just as a test, try creating an UPDATE query like so:

UPDATE TableName SET NumberField=2,35

and execute it in the Spanish version. Does it work?

Yes I am an no it doesn't

 

[theDBguy]

Yes I am an no it doesn't

So, to my mind, that sort of explains the problem. You are trying to update a numeric field and Access couldn't decipher the value with a comma. As another test, create another query as such:

UPDATE TableName SET NumberField=Forms!FormName.ControlName

In this case, put in a form's textbox the value 2,35 and let's see if this works or not.

 

[ryetee]

...and if i change it to 2.35 it works but it is then held as 2,35 and displayed as 2,35 and if i update it it fails because it's 2,35

 

[theDBguy]

...and if i change it to 2.35 it works but it is then held as 2,35 and displayed as 2,35 and if i update it it fails because it's 2,35

Hi. Did you try what I suggested last? Basically, I wanted to change SET Field=2,35 to Field=FormReference. Depending on your result, I was going to request next for you to use a Table Field reference. For example SET T1.Field=T2.Field, where T2.Field would contain 2,35. My thinking was, if Access doesn't like seeing a literal value with a comma, I was hoping it wouldn't mind it so much if the value is being pulled from a stored location.

 

[ryetee]

So, to my mind, that sort of explains the problem. You are trying to update a numeric field and Access couldn't decipher the value with a comma. As another test, create another query as such:

UPDATE TableName SET NumberField=Forms!FormName.ControlName

In this case, put in a form's textbox the value 2,35 and let's see if this works or not.

I've sort of done that already.
I've got a form that are the sales items.
On the spanish network all the figures are n,nn. If I try and out n.nn in the price I get it translated to nnn,00 so I HAVE to put n,nn. Looking at the table it is stored as n,nn. When I close the form I update a sales record on a different table with the totals on the sales items form.
So
UPDATE sales SET with sumprice = me.sumprice, sumtax = me.sumtax, where salesid = me.salesid

Me.sumprice and me.sumtax have n,nn in them in them.
Catch 22 all over it

 

[theDBguy]

I've sort of done that already.
I've got a form that are the sales items.
On the spanish network all the figures are n,nn. If I try and out n.nn in the price I get it translated to nnn,00 so I HAVE to put n,nn. Looking at the table it is stored as n,nn. When I close the form I update a sales record on a different table with the totals on the sales items form.
So
UPDATE sales SET with sumprice = me.sumprice, sumtax = me.sumtax, where salesid = me.salesid

Me.sumprice and me.sumtax have n,nn in them in them.
Catch 22 all over it

Hi. If I understand it correctly, the two approaches are not the same. As I said earlier, I had a feeling you were using VBA. So, if you construct a SQL statement with field=Me.control, then the SQL statement will actually have field=2,35. What I want to figure out is if Access will be happy with not seeing the literal value. Noticed I didn't use Me in my suggestion but Forms!FormName instead.

 

[MajP]

Found this on the internet. You could test for the decimal character

Option Compare Database
Option Explicit
 
Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
 
Private Const LOCALE_USER_DEFAULT = &H400
Private Const LOCALE_SDECIMAL As Long = &HE
Private Const LOCALE_ILDATE As Long = &H22
Private Const LOCALE_ICOUNTRY As Long = &H5
Private Const LOCALE_SENGCOUNTRY = &H1002 ' English name of country
Private Const LOCALE_SENGLANGUAGE = &H1001  ' English name of language
Private Const LOCALE_SNATIVELANGNAME = &H4  ' native name of language
Private Const LOCALE_SNATIVECTRYNAME = &H8  ' native name of country
 
Public Function GetInfo(ByVal lInfo As Long) As String
    Dim Buffer As String
    Dim Ret As String
    Buffer = String$(256, 0)
    Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
    If Ret > 0 Then
        GetInfo = Left$(Buffer, Ret - 1)
    Else
        GetInfo = ""
    End If
End Function
 
Private Sub testit()
   ' MsgBox "You live in " & GetInfo(LOCALE_SENGCOUNTRY) & _
   ' " (" & GetInfo(LOCALE_SNATIVECTRYNAME) & ")," & vbCrLf & "and you speak " & GetInfo(LOCALE_SENGLANGUAGE) & _
   ' " (" & GetInfo(LOCALE_SNATIVELANGNAME) & ").", vbInformation 'WORKS CORRECTLY
   ' MsgBox ("UserDefault: " & GetInfo(LOCALE_USER_DEFAULT)) 'STILL EMPTY STRING
    MsgBox ("SDecimal: " & GetInfo(LOCALE_SDECIMAL)) 'RETURNS "."
   ' MsgBox ("ILDate: " & GetInfo(LOCALE_ILDATE))     'RETURNS "0"
   ' MsgBox ("Country: " & GetInfo(LOCALE_ICOUNTRY))  'RETURNS "1"
End Sub

 

[Mark_]

ryetee,

On a lark, can you try using FORMAT( Me.Control, "###.00") to see if that removes the offending comma?

SQL is expecting to be passed text that it can parse. FORMAT( ) should let you put your number in the right format for SQL to understand.

 

[ryetee]

Hi. If I understand it correctly, the two approaches are not the same. As I said earlier, I had a feeling you were using VBA. So, if you construct a SQL statement with field=Me.control, then the SQL statement will actually have field=2,35. What I want to figure out is if Access will be happy with not seeing the literal value. Noticed I didn't use Me in my suggestion but Forms!FormName instead.

ok so
UPDATE sales SET with sumprice = forms!salesitem.sumprice?

Can I use formname as I'm using instances so don't know wht the form is called!

Anyway I'll give it ago

 

[ryetee]

Found this on the internet. You could test for the decimal character

Option Compare Database
Option Explicit
 
Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
 
Private Const LOCALE_USER_DEFAULT = &H400
Private Const LOCALE_SDECIMAL As Long = &HE
Private Const LOCALE_ILDATE As Long = &H22
Private Const LOCALE_ICOUNTRY As Long = &H5
Private Const LOCALE_SENGCOUNTRY = &H1002 ' English name of country
Private Const LOCALE_SENGLANGUAGE = &H1001  ' English name of language
Private Const LOCALE_SNATIVELANGNAME = &H4  ' native name of language
Private Const LOCALE_SNATIVECTRYNAME = &H8  ' native name of country
 
Public Function GetInfo(ByVal lInfo As Long) As String
    Dim Buffer As String
    Dim Ret As String
    Buffer = String$(256, 0)
    Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
    If Ret > 0 Then
        GetInfo = Left$(Buffer, Ret - 1)
    Else
        GetInfo = ""
    End If
End Function
 
Private Sub testit()
   ' MsgBox "You live in " & GetInfo(LOCALE_SENGCOUNTRY) & _
   ' " (" & GetInfo(LOCALE_SNATIVECTRYNAME) & ")," & vbCrLf & "and you speak " & GetInfo(LOCALE_SENGLANGUAGE) & _
   ' " (" & GetInfo(LOCALE_SNATIVELANGNAME) & ").", vbInformation 'WORKS CORRECTLY
   ' MsgBox ("UserDefault: " & GetInfo(LOCALE_USER_DEFAULT)) 'STILL EMPTY STRING
    MsgBox ("SDecimal: " & GetInfo(LOCALE_SDECIMAL)) 'RETURNS "."
   ' MsgBox ("ILDate: " & GetInfo(LOCALE_ILDATE))     'RETURNS "0"
   ' MsgBox ("Country: " & GetInfo(LOCALE_ICOUNTRY))  'RETURNS "1"
End Sub

I saw that too but I've got fields using decimal points all over the place so this would be painful to implement

 

[ryetee]

ryetee,

On a lark, can you try using FORMAT( Me.Control, "###.00") to see if that removes the offending comma?

SQL is expecting to be passed text that it can parse. FORMAT( ) should let you put your number in the right format for SQL to understand.

OK was also thinking along that idea but I'm using this all over the place (controls holding a decimal) so it's going to be a pain the the you know what.
Surely access can be used in multiple regions without this. The field itself is coming off the table in that format

 

[theDBguy]

ok so
UPDATE sales SET with sumprice = forms!salesitem.sumprice?

Can I use formname as I'm using instances so don't know wht the form is called!

Anyway I'll give it ago

Hi. For now, we're just testing. So, create a single instance form and try it out. If it works, we'll tackle the problem with multiple instances of the form. Also, might as well try the other idea I was going to suggest. That is, rather than use a form, use another table where the value to use for the update is stored. Again, this is just to test if Access can handle references instead of literal values.

 

[Mark_]

OK was also thinking along that idea but I'm using this all over the place (controls holding a decimal) so it's going to be a pain the the you know what.
Surely access can be used in multiple regions without this. The field itself is coming off the table in that format

Alternate would be to use fnAnySQL, a nice bit of code Arnel put together. That should allow you to avoid regional issues.

Yes, your posterior will be sore for a bit, but it should avoid these kinds of issues going forward.