Problem with Regional Settings decimal separator and SQL

[juvi]

Hello,

I have got a little problem. My form has a textbox where the user can enter
a decimal value and after LostFocus event a second textbox gets a value from
a SQL query.

The problem is that the user insert "10,36" and the SQL query only works
with "10.36". How could I change the separator "," to "." only for this Event?

The regional settings from the target machine is "," so it seems that only
SQL need the "."

Thx for any help

 

[Alex Dybenko]

Hi,
you can just replace comma with dot in this event:

strNewVal=replace(me.textbox1,",",".")

and then use strNewVal in your query

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[juvi]

hello, thank you for your reply. This workaround works to avoid the SQL-query
error but I receive wrong result for query.

The data is stored as "10,36" in the table and if I want to change it to
"10.36" access converts the field automatically to "1036" so the value in my
query is also converted to a value without separator!? what can I do to get
this work?

thx

 

[Alex Dybenko]

When you enter data in a table - access recognize decimal as it set in
regional settings. but when you type it in SQL or VBA - then it should be
dot. so depending on where you enter this value you need to convert it to .
or ,

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[juvi]

thank you for your reply. So how can I get this work?

my query is something like:

SELECT * FROM tbl_Prices WHERE weight=2.5

here I get the records for weight= 25!?!

SELECT * FROM tbl_Prices WHERE weight=2,5

here I get an exception for comma (3075)

So how can I now make my query to get results for 2,5???

thx