Problem with the decimal sign "," or "." in WHEREcondition

[sraymake]

I have a problem with creating a QBF in VBA code.

In my case I want to apply a filter to a table that selects the records "= 4,2", as you see a decimal number. I want to do this through a form where the user can fill this in, and then click the created search button.
This button calls a procedure that first selects the filled in field value 4,2 and afterwards creates the WHEREcondition. The condition is created as follows:

strTemp = "((" & "Thdsize" = Thdsize_FieldValue & "))"

and looks like "((Thdsize = 4,2))". When this filter is applied to the table with "DoCmd.OpenForm" it goes wrong. The filters selects no records. It only works when I fill in the WHEREcondition manually as follows: "((Thdsize = 4.2))" --> WATCH THE DECIMAL SIGN.

Does anybody know where this problem is comming from and how I can manage it?

Thanx in advance

Stijn Raymakers

 

[crosmill]

Your syntax is wrong to start with try this

strTemp = "(Thdsize = " & Thdsize_FieldValue & ")"

If it's changing the comma to a decimal then try this

Temp1 = Replace(Thdsize_FieldValue, ".", ",")
strTemp = "(Thdsize = " & Temp1 & ")"


It might be changing the comma to a decimal because your using a number field, a comma may be inccorect for this?

 

[sraymake]

Thanks for your reply,

but I think I didn't explain the problem well. The comma has to change to a decimal sign because the filter doesn't work with a comma in a decimal comma.
Is it possible that this has something to do with General Settings of the decimal sign? And can you change these settings?

Thanks


Stijn Raymakers

 

[crosmill]

where is the comma, is it in the table or coming from a form?

If it's the table then you can change the format in the table design. If it's coming from the form then you can change it in the properties of the element on the form in the from design.

 

[Rich]

I think you'll have to change them on your regional settings

 

[sraymake]

The comma is indeed coming from the table. And this is indeed depending on the regional settings, as in the help:

". , : ; - / Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel). "

But why does the filter property doesn't except a comma, and thereby doesn't depend on the regional settings?

 

[Rich]

I tend to suspect that as with dates in strings you have to use the Format function to switch to the US format.
There was a post some time back with the same problem but I couldn't find it, searching for comma may help

 

[sraymake]

Thanks for your help everybody,

I think I'm gonna fix it like crosmill told me, and work with a replace action:

Temp1 = Replace(Thdsize_FieldValue, ",", ".")
strTemp = "(Thdsize = " & Temp1 & ")"

In this way the Regional Settings doesn't affect the result of the of the Filter Operation


Stijn Raymakers