Comparison of text field values wtih Decimal symbol gives error

[chris]

Hi,

I have a text field with numeric values (values have dot(.) as decimal
symbol ex. 5.3). Unfortunately it must be text as sometimes includes text
values also.

Now for numeric values I would like to compare values and get records
between a range.

When my decimal symbol is dot(.) the query below executes without any
problem.

SELECT *
FROM AD
WHERE (CSng([AD].[d_name])>=0.5 And CSng([AD].[d]))<=1.8)

When user's decimal point in control panel is not dot(.) then error "Data
type mismatch in criteria expression" appears.

Can anyone help me ? Is there a solution ?

Thanks a lot in advance!

 

[Dorian]

Replace other characters with a dot. User Replace() function, look up in
Access Help.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer]

Check and see if the value can be interpreted as a number. If so, convert it,
if not return null (or zero or some out of range number value if that is what
you want).

SELECT *
FROM AD
WHERE IIF(IsNumeric([AD].[d_name]),[AD].[d_name],Null) Between 0.5 And 1.8

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County