Comma as a decimal symbol (Regional and Language settings)

I think u could replace the comma with a decimal, if that could help.
Here are some links that could possibly help.
https://www.experts-exchange.com/questions/21038300/Replace-String-function-in-Access.html

or

https://www.experts-exchange.com/questions/20543391/Replacing-Characters-in-a-Field-of-a-Table-Access-97.html?

Hope this helps.
Thanx

Won't do. As previously stated a dot is ignored with my regional settings.

Correction: I'm using Swedish MS Access 2003. You would expect it to comprehend national standards.

Access SQL is a pure US-bread cowboy. It will *never* accept the comma as a decimal separator. Under the pressure of ISO, it does accept ISO dates, along with US formatted dates (even using US month names!), but that is about all.

Switch to SQL view of your query, and you will notice that everything has been properly translated back to US conventions.

You can also try these funny queries in SQL view:

    SELECT 1,2;
    SELECT 1.2;
    SELECT 1'2;

or, with dates:

    SELECT #1/2/3#;
    SELECT #July 4#;
    SELECT #28 février#;

The query design grid, however, attempts to translate back and forth between regional settings and US-SQL, and interprets what you type as following regional settings. Hence "1.2" --> "12" if the dot is the thousands separator.

In international contexts, you need to pay attention to the VB functions CLng(), CDbl(), CCur(), CDate(), etc, when used with string arguments. For example:

    ? CCur("1,2"); CCur("1.2"); CDate("1 avril")

all rely on regional settings, so they are not reliable. But you don't need to worry about SQL, it's safe in that respect.

Cheers!
(°v°)

Thanks for the info harfang but it doesn't solve my problem with the comma. Tried cDbl( Cstr(num) ) but it returns the number with a decimnal comma as well. This problem cannot be unique for me, it must apply for all countries using the decimal comma. So SQL is not "safe" in that respect.

I live in Geneva, where computers can have many combination of settings (OS/App interface language and regional settings). I know these problems quite well.

But... What exactly *is* your "problem with the comma"? When working in the query assistant, use your regional settings; when switching to SQL view, use US standards. Have you tried some of the SQL samples?

When you say "it returns the number with a decimal comma", all you are saying is that whenever your computer shows you a number, it will use the regional settings to do so. For example, if you try: CDbl(CStr(1/2)), CStr will return "0.5" with US or Swiss settings, but "0,5" with Swedish or French settings. CDbl() is aware of these settings and will interpret the dot or the comma correctly. But when showing the value on screen (in the immediate pane or in a datasheet), it will again display "0.5" or "0,5" according to the same settings. In effect, this is running in circles.

Please try this... Create a new blank query without a table and type: "1,5" in the first column. You get "Expr1: 1,5", right? Now click on the "SQL view" button, you will see: "SELECT 1.5 AS Expr1;".

It's really the same thing in VB. You cannot use 1,5 for one and a half in VB either. And you can use CDbl("1,5") *only* if the regional settings specify the comma as decimal separator.

(°v°)

Your suggestions work alright. Querie Assistant allow me to use the dot. But, what I need to do is use an SQL expression in a form to select certain records. When I input a decimal value from a text box in the form it requires me to use the comma. That value is then used in a dynamically build SQL expression, which will not run as the number contains a comma. As I stated in my original post I cannot use a dot in the input field as Access is removing the dot.
Hope that makes it a bit more clear.

I see now. If you build your SQL through code, you get an implicit CStr() call to convert numbers and dates, which is language dependent.

This works:

    strSQL = "... Where Amount>=Forms!frmSearch!txtMinAmount"

because there is no conversion. This doesn't:

    strSQL = "... Where Amount>=" & Me.txtMinAmount

because the concatenation produces an implicit call to CStr() to convert the value to a string, using local settings. You would end up with "Amount=1,2". Instead, you could explicitly format your criteria:

    strSQL = "... Where Amount>=" & Format(Me.txtMinAmount, "0\.00")

Notice the backslash. In a format string, "." means "system's decimal separator", while "\." means dot.

As a matter of fact, you should never build your criteria "by hand". There is a method for that, namely BuildExpression(), which takes care of all problems related to localization, and always returns a string with SQL compatible syntax.

    strCriteria = BuildCriteria("Amount", dbDouble, _
        ">=" & Nz(Me.txtMinAmount, "Null")
    strSQL = "... Where " & strCriteria)

This would be the most portable solution, so make a habit of it. It's also very convenient for dates, by the way. Play with it in the immediate pane, e.g.:

    ? BuildCriteria("[my date]", dbDate, Now())
    ? BuildCriteria("something", dbText, Now())
    ? BuildCriteria("amount", dbDate, "between " & 10 & " and " & 10.99)
    ? BuildCriteria("category", dbText, "blue or green")
    ? BuildCriteria("category", dbText, "Not Null")

Does this help?
(°v°)

Thanks for your efforts harfang. I have a silly Swedish version of Access. It implies that I cannot get any help whatsoever with finding "immediate window" as it's "lost in translation" by non-knowing academic people who translates by ignorance. I need to find an English version to keep working.

Sorry, I should have known. I have had enough problems with French and German version...

The "immediate pane" (or "window") is part of the VB editor. You can open it from anywhere (Access or VB) using Ctrl+G

If you don't know it, you can type VB commands and expressions there, which are executed as soon as you press [Enter]. For example:

    ? 2+4 [Enter]
    4

Good luck!
(°v°)

Thank for the tip with the immediate window. Quick way to test your expressions in VB. However, the BuildCriteria method cannot solve my problem with the comma. The variable, if put there, is quoted.

Here's the filter funtion which works if decimal separator is a dot and fails when it's a comma:
Me.Breddrapport.Form.Filter = "[Bredd m] >= " & min & " AND [Bredd m] <= " & max
It's to show all boats within a certain width span. min and max are typically 2,3 and 2,7 and input by the user from a form. (As stated previously, if the user types a dot when the separator is a comma, Access simply removes the dot) If I put it in a SQL statement the results are the same, works with dot, fails with comma.

I've come up with a feasible, still not beautiful solution: I multiply all numbers with 100 and convert to integers.

What bugs me really is that there must be thousands of people who have experienced this, yet noone seem to know or react.

Thanks harfang
Me.Breddrapport.Form.Filter = BuildCriteria("[Bredd m]", dbSingle, Expression:="Between " & min & " and " & max) actually WORKS with comma as decimal separator. I've also learnt a great deal from this.
This didn't work however: Me.Breddrapport.Form.Filter = "[Bredd m] Between [min] And [max]"
.. but it's quite alright with the BuildCriteria version.

Merci bien encore harfang.
/Peter

De rien, et bonne chance! -- (^v°)

Hi,

for me the above mentioned solution didn't work : Format(Me.txtMinAmount, "0\.00"), Acces will use in that formula only the numbers before de comma, because this way you don't specify the "system comma" anywhere, so, Access dosen't give you any decimals of that number.  But.. this problem has a solution.

It was a very good idea to begin with, so, I found a workaround that works very nice for me: Format(Me.txtMinAmount*100, "####0\.00")

This way I can use the two decimals (multiplying by 100 the source value) and I don't need the system's decimal comma anymore.
Of course you can personalize this for your necesities, like the number of the decimal points (just multiply by 1000 or more), or the number of digits of the integral part of the number.

Thank you guys, you saved my life :)
Hope this helps too!