compare string to a number in sql

[sigava77 via AccessMonster.com]

Hi,

I have a table with a string field that can store numeric field. How do I
make a query to compare this field with a numeric value.
Example:
Select * from table1 where field1="conditi on" and field2>1000
Do I have to convert field2 to numeric at sql? If so what function do i have
to use?

Thanks,

Carla

--
Message posted via http://www.accessmonster.com

[Allen Browne]

Re: compare string to a number in sql

Use Val() to get the value of the field.

Val() doesn't cope with null, so something like this:

Select * from table1
where field1="conditi on" and Val(Nz([field2],0)) 1000;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sigava77 via AccessMonster.c om" <u11137@uwewrot e in message
news:821e3dc13e 4b3@uwe...

>
I have a table with a string field that can store numeric field. How
do I make a query to compare this field with a numeric value.
Example:
Select * from table1 where field1="conditi on" and field2>1000
Do I have to convert field2 to numeric at sql? If so what function
do i have to use?

[sigava77 via AccessMonster.com]

Re: compare string to a number in sql

THANK YOU!!!!

Allen Browne wrote:

>Use Val() to get the value of the field.
>
>Val() doesn't cope with null, so something like this:
>
Select * from table1
where field1="conditi on" and Val(Nz([field2],0)) 1000;
>

>I have a table with a string field that can store numeric field. How
>do I make a query to compare this field with a numeric value.
>Example:
>Select * from table1 where field1="conditi on" and field2>1000
>Do I have to convert field2 to numeric at sql? If so what function
>do i have to use?

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200804/1

[lyle]

Re: compare string to a number in sql

On Apr 3, 5:03 am, "sigava77 via AccessMonster.c om" <u11137@uwe>
wrote:

Hi,
>
I have a table with a string field that can store numeric field. How do I
make a query to compare this field with a numeric value.
Example:
Select * from table1 where field1="conditi on" and field2>1000
Do I have to convert field2 to numeric at sql? If so what function do i have
to use?

Band-aided databases are bad and dangerous databases.
Convert the field.
If you tell us what version of Access you are using, and what database
engine you are using (eg Access Default/Jet or MS-SQL Server) we'll
tell you how to convert.

[lyle]

Re: compare string to a number in sql

On Apr 3, 5:35 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

Use Val() to get the value of the field.
>
Val() doesn't cope with null, so something like this:
>
    Select * from table1
    where field1="conditi on" and Val(Nz([field2],0)) 1000;

This might select [Records having Field2 greater than 1000] correctly.

But it will select [Records having Field2 less than 1000] incorrectly
if there are any nulls in Field2.
Null is not less than 1000.

[Allen Browne]

Re: compare string to a number in sql

"lyle fairfield" <lylefa1r@yah00 .cawrote in message
news:Xns9A75ADE D9384E666664626 1@216.221.81.11 9...

I believe it’s worthwhile to remind (or explain to) the casual
reader that Null is not zero.

Actually, I appreciate that, Lyle, and I think you are right. Better to
explain than take it for granted.

Sometimes it gets awkward when you're trying to answer one question, and
have to introduce another (typecasting) which leads to an explanation of why
our dear friend Val can't handle Nulls, and then we're off explaining how
Nulls work. I'm never sure how far down the rabbit hole to go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[Larry Linson]

Re: compare string to a number in sql

"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te

>I believe it’s worthwhile to remind (or explain to) the casual
>reader that Null is not zero.

>
Actually, I appreciate that, Lyle, and I think you are right. Better to
explain than take it for granted.
>
Sometimes it gets awkward when you're trying to answer one question, and
have to introduce another (typecasting) which leads to an explanation of

why

our dear friend Val can't handle Nulls, and then we're off explaining how
Nulls work. I'm never sure how far down the rabbit hole to go.

Good thinkin', Allen. Gotta watch those rabbits... some are attack rabbits,
you know. Former U. S. President Jimmy Carter ran into one of those while
boating on the pond on his farm, a swimming attack rabbit, no less, and it
took the whole Secret Service protective team to save him from severe
physical harm.

[Allen Browne]

Re: compare string to a number in sql

"Larry Linson" <bouncer@localh ost.notwrote in message
news:63hJj.3440 7$Eq.16954@trnd dc05...

... I'm never sure how far down the rabbit hole to go.

>
Good thinkin', Allen. Gotta watch those rabbits... some are attack
rabbits, you know. Former U. S. President Jimmy Carter ran into one of
those ...

Far out, Larry!
I'd never heard that story:

http://en.wikipedia.org/wiki/Jimmy_Carter_rabbit_incident

I do respect Jimmy Carter though. Even since his presidency, he continues to
work on making a difference in places where it counts.

(Sorry: this is really OT now.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.