Decimals giving problems (Db intended for Italy)

[mo]

The following code works perfectly on my pc (with regional settings for UK).
Decimals are therefore saved as 3.65 etc. However, the Db is meant for Italy
where regional settings mean that the same decimal is 3,65.

To test whether the code below is working, I changed the regional settings
for my pc to Italian. The code now gives me a runtime '3075' error, telling
me that the is a 'syntax error (comma) in query expression 'DateOfMerge >=
#1/1/2003# AND DateOfMerge <=#31/12/2003# AND (FreeT4) <=7,15'.

I know that it is the comma portion of the '7,15' that is giving me the
problem.

My question is this: Are decimals always going to give me problems like this
and if so is there a straightforward solution?

I'm sure that when I tested the same code when I was in Italy a few weeks
back, everything was fine on a Italian PC with the Italian version of
Windows2K, Office2K and regional settings for Italy.

Can someone please enlightten me as to what exactly the rules are here.

Thanks very much.


------------------------------------------Code------------------------------
------------------
strSQL = "SELECT Surname, Forename, DoB, DateOfMerge, FreeT4, TSH,
SampBarCode "
strSQL = strSQL & "FROM TBL_REGISTRATION0003 "
strSQL = strSQL & "WHERE DateOfMerge >= #" & Month(Me!txt_start_date) & "/"
& Day(Me!txt_start_date) & "/" & Year(Me!txt_start_date) & "#"
strSQL = strSQL & " AND DateOfMerge <= #" & Month(Me!txt_end_date) & "/" &
Day(Me!txt_end_date) & "/" & Year(Me!txt_end_date) & "#"
strSQL = strSQL & " AND TSH >= " & (Me!txt_TSH) & ""
strSQL = strSQL & " AND FreeT4 <= " & (Me!txt_FT4) & ""
strSQL = strSQL & " ORDER BY DateOfMerge, Surname ASC"

 

[Emilia Maxim]

The following code works perfectly on my pc (with regional settings for UK).
Decimals are therefore saved as 3.65 etc. However, the Db is meant for Italy
where regional settings mean that the same decimal is 3,65.

To test whether the code below is working, I changed the regional settings
for my pc to Italian. The code now gives me a runtime '3075' error, telling
me that the is a 'syntax error (comma) in query expression 'DateOfMerge >=
#1/1/2003# AND DateOfMerge <=#31/12/2003# AND (FreeT4) <=7,15'.

I know that it is the comma portion of the '7,15' that is giving me the
problem.

My question is this: Are decimals always going to give me problems like this
and if so is there a straightforward solution?

mo,

I just answered a very similar question yesterday. The solution is the
built-in function BuildCriteria. It is meant to construct SQL criteria
according to SQL syntax:

BuildCriteria("FreeT4", dbDouble, "<=" & Me!txt_FT4)

returns the string:

"FreeT4 <= 7.15"

if Me!txt_FT4 contains 7,15

See also Help on BuildCriteria for detailed explanation. It is a very
useful function for dates too, it takes care of the # signs and of the
correct US date format.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[mo]

Thanks very much for the helpful response Emilia.

I have one other question which you might like to answer.

In my VB code for the Italy Db, I have changed all occurences of decimals
where they were written as '7.15' to '7,15', so that for e.g 'FreeT4 >=7.16'
has now become 'FreeT4 >=7,16'. Do I need to use the BuildCriteria function
to format these back to '7.15' or should I keep them as '7,15'?

I am a little confused as to the formats of decimals when the regional
settings display them in different ways!

I hope you will understand my question.

Many thanks again.

 

[Emilia Maxim]

In my VB code for the Italy Db, I have changed all occurences of decimals
where they were written as '7.15' to '7,15', so that for e.g 'FreeT4 >=7.16'
has now become 'FreeT4 >=7,16'. Do I need to use the BuildCriteria function
to format these back to '7.15' or should I keep them as '7,15'?

I am a little confused as to the formats of decimals when the regional
settings display them in different ways!

I hope you will understand my question.

mo,

if you're using real VB and not VBA, there might be issues I don't
know. But AFA VBA is concerned, all explicitly written decimals in
code have to be written with dots, like this:

dblMyVariable = 7.15
Const conPI As Double = 3.14

Writing them with comma would give a compiler error. VBA is after all
a worldwide valid programming language.

However, if you use form controls or variables, you don't need to
format them, Access/VBA convert them internally. So for ex things like
this are OK:

dblVariable = Me![MyControlWithDecimal]
dblVariable = dblVariable + 100

sngResult = Forms("X")![MyControlWithDecimal] * 2
sngResult = Forms("X")![MyControlWithDecimal] * 2.5 'and NOT ... *
2,5
Forms("X")![MyControlWithDecimal] = 7.15

So I guess you don't have to worry about code, except for real
constants one uses variables for all kinds of data anyway.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[mo]

Thanks again Emelia, you've been very helpful.

Mo