DLookup and the Windows Regional Setting decimal symbol

[chris klein]

I have application that contains a function which, in turn, passes numeric arguments to the VBA function DLookup()

The function was written and runs fine on a computer with the Windows Regional and Language Option set to English (United States). The Decimal symbol of this setting is a period (.)

I have installed the app in Portugal, where the Windows Regional and Language Option is set to Portuguese (Portugal), and the decimal symbol of that setting is a comma (,)

(d) This confuses DLookup(), which apparently sees the (decimal place) commas and treats them as argument separators. The function aborts to the VBA editor.

(e) Other operations may also be affected by the decimal place separator confusion, but the app has not been fully tested with the Portuguese setting.

(e) Access Help seems to say that the only solution to this is to change the decimal symbol of the Portuguese setting to a period. This is not normal usage in Portugal, and so the user is faced with making the change to a period and back to a comma every time Access is used.

IS THERE ANY BETTER WAY TO HANDLE THIS PROBLEM? Any help appreciated as always from this great World Forums resource.

 

[The_Doc_Man]

What I don't understand is why DLookup would do this from normal calls.

I suspect that when you use this function that "in turn calls DLookup" that you somehow didn't build the DLookup string correctly. You are perhaps concatenating something in a string. In so doing, you must watch for the placement of the data in such a way as to avoid the confusion caused by the Regional Settings stuff. If you are running into that, you must have exposed the data into a place where it should be seen (yet).

Like, if you were trying to do a DLookup for which you were building a criterion clause (3rd argument) as a comparison to a numeric field, you would be tempted to just concatenate the value into the string. That comma that you describe shouldn't be visible to VBA (where the syntax of DLookup is evaluted); it should only be visible to DLookup (where the semantics of the criterion clause get evaluated). In other words, the string containing the comma should be data but instead VBA thinks it is metadata.

Can you post at least a snippet of the code leading up to and including the DLookup call?

 

[Kiwiman]

Howzit

Found a similar thread that may be useful

http://www.access-programmers.co.uk/forums/showthread.php?t=154492

 

[chris klein]

Dear Doc_Man
Here is the function that calls DLookup(), up to the abort point

e.g. with infield = "psi"
inval = 31,9 (Portuguese setting)

it aborts at the calculation of ninupval = DLookup(....
giving the error msg:

"Run-time error '3075'
syntax error (comma) in query expresssion '[psil]<31,9 and [psi]>=31,9'

Maybe I just need to search the inval and replace any commas with periods? Thanks for looking at this.
Thanks too to Kiwiman: obviously an identical issue.

==============

Public Function StmTblLookUp(ByVal infield, ByVal inval, ByVal getfield)

'ByVal declarations for variables causes processing of value sent instead of address variable sent
'This keeps StmTblLookUp from changing the variable values in other functions that call this one.
'as would otherwise happen, for example, to infield and inval in the Select Case statement below.

'050625: add bar, jou inputs
'does lookup on English steam table, returning interpolated value
'allowable infield and getfield parameters are
'
' "tf" or "tc" or
' "psi" or "bar" or
' "hlbtu" or "hvbtu" or "hljou" or "hvjou"
'
'returns Null if inval is Null or out of range of steam table
'produces run time error if infield or getfield are not allowed
'

If IsNull(inval) Then
StmTblLookUp = Null
Exit Function
End If

'Adjust input as needed
Select Case infield
Case "tc"
inval = 1.8 * inval + 32
infield = "tf"
Case "bar"
inval = 14.5 * inval
infield = "psi"
Case "hljou"
inval = inval / 2.326
infield = "hlbtu"
Case "hvjou"
inval = inval / 2.326
infield = "hvbtu"
End Select

'Flag output as needed
Select Case getfield
Case "tc"
getfield = "tf"
getflag = "tc"
Case "bar"
getfield = "psi"
getflag = "bar"
Case "hljou"
getfield = "hlbtu"
getflag = "hljou"
Case "hvjou"
getfield = "hvbtu"
getflag = "hvjou"
End Select

'find upper value of infield range
ninupval = DLookup("[" & infield & "]", _
"[TOOLS: Reference: Data: STMTABLE_Eng]", _
"[" & infield & "l] < " & inval & _
" and [" & infield & "] >= " & inval)
'Debug.Print ninupval