Using comma - not period in number format 1

[nkiefer]

I have a VB/Access application that is in the United States. When a user enters the fraction one quarter they they enter 0.25 I need to move the database to Europe where in some countries they would enter 0,25 for the same one quarter fraction. Is there a way in Access or even SQL server to allow this to happen. The fields in the database are defined as number(double) and there are many queries that operate on numeric data. Ideally I would create a new database that allows for the comma and still works on numeric data.

 

[ArkM]

I think, comma/period number separator is element of Windows/Access installation, not a database attribute. If your VB(A) code does not picking with string representations, it's no problem. Doubles have 8 bytes and 64 bits in Europe too...

 

[jrbarnett]

I am in the UK where the dot is used as a numeric separator from whole to fraction (and the comma is the thousand separator), but have worked with systems in mainland Europe where the situation is reversed and no changes are required to application code.

My suggestion to you is to set up a separate test server and client with European settings, keyboard etc and install your existing software on there, and test it extensively to see what works - and what doesn't (if anything).

John

 

[pweegar]

Wouldn't it depend on the regional settings???? Dates can also be different.

 

[jrbarnett]

Pweegar,
That's the essence of what I said in simple terms.

From my experience, no modifications were required at all to the code provided that the computers were configured correctly for their location.

John

 

[Dimandja]

John is right.

Unless the programmer explicitly overrides it, the local (machine) default number (or date) formats will be used by the application.

Dimandja

 

[CCLINT]

Concerning VB(A):
There are some specific guidelines which you need to follow:

1. Validate all user numeric input using the IsNumeric funtion (or some similar method available to your programming language)

2. If it is valid numeric data, then assign the input value to a number variable.

3. Hard coded numeric values are coded always in US format:

dMyNumber = 1.234@

4. When saving values (held in number variables) to the DB, such as in an Action query, use the Str() function.
Since we know that the number variable will always return a valid number (even if it is 0), then we can use the Str() function, which will convert a number to US format:

?Str("1,23&quot returns 1.23 if the system uses a comma as a decimal seperator

SqlStringForActionQuery = "UPDATE....SET SomeNumberField = " & Str(myNumberVar)


5. NEVER use the Val() function on numbers UNLESS you are only interested returning in the Integer portion, because:

?Val("5,23ABC&quot

will return 5


*******************************
The same holds true for dates:
1. Validate all user date input using the IsDate() funtion (or some similar method available to your programming language)

2. If it is valid Date data, then assign the input value to a Date variable.

3. Hard coded numeric values are coded always in US format:

dtMyDate = #09/20/2003#
(or the recommended ISO standard: #2003/09/20#)

4. When saving values (held in date variables) to the DB, such as in an Action query, use the Format() function.
Since we know that the Date variable will always return a Date (even if it is 12/30/1899 00:00:00), then we can use the Format() function, to convert a date to US format:

(force seperators using \)
strSQLDate = Format$(dtMyDate, "MM\/dd\/yyyy&quot

or use the ISO format:

strSQLDate = Format$(dtMyDate, "yyyy-MM-dd&quot

You can also add the date markers if the DB may change:

For JET:
strSQLDate = Format$(dtMyDate, "\#yyyy-MM-dd\#&quot

Or SQL Server:

strSQLDate = Format$(dtMyDate, "\'yyyy-MM-dd\'&quot

SqlStringForActionQuery = "UPDATE....SET SomeDateField = " & strSQLDate



Use the ADODB Command and Parameter object when ever possible.
It will help alot with these problems, also with problems such as strings containing single or double quotes.

 

[Dimandja]

Very nice explanation, CCLINT!

 

[CCLINT]

Thank you Dimandja

 

[CCLINT]

I did make a typo:

>(or the recommended ISO standard: #2003/09/20#)

Should read:
(or the recommended ISO standard: #2003-09-20#)

One other thing concerning Dates:
You may always have a date - meaning, a date such as a completion date, is not available until completion.
So, so may want to use this date field, or just want to pass a NULL.

Dates are slighly more difficult to work with, when they are not a required field.
Dates are really a number (64 bit floating point), where a date of Zero equals 12/30/1899 00:00:00 ( ?Cdate(0) ).
All dates prior to this are represented as a negative (but the time portion remains positive - careful when calculating dates this way - use the DateValue first to cut off the time portion), and calculate the time seperately), and all dates after this are positive.

In this case (of the need for NULLs, or no date) you can use a variant, which will hold a Date, or a NULL. You just need to be careful that this is all that get's held in this variable.

Probably better is storing the date in a string, and always using IsDate() and, if the value is really a date, use a date conversion functions where needed, but when saving to a DB record, check if it is a date, and if not, set it to NULL:

"UPDATE...SET SomeDateField=" & IIF(IsDate(strMyDate),Format$(strMyDate, "\#yyyy-MM-dd\#&quot,"NULL&quot

This will place a Date in the sql string, if the string holds a literal date, otherwise it places a NULL in it.