compare string to a number in sql

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="condition" 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

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="condition" 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.com" wrote in message
news:821e3dc13e4b3@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="condition" and field2>1000
> Do I have to convert field2 to numeric at sql? If so what function
> do i have to use?

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="condition" 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="condition" 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.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200804/1

Re: compare string to a number in sql

On Apr 3, 5:03=A0am, "sigava77 via AccessMonster.com"
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=3D"condition" and field2>1000
> Do I have to convert field2 to numeric at sql? If so what function do i ha=
ve
> 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.

Re: compare string to a number in sql

On Apr 3, 5:35=A0am, "Allen Browne" wrote:
> Use Val() to get the value of the field.
>
> Val() doesn't cope with null, so something like this:
>
> =A0 =A0 Select * from table1
> =A0 =A0 where field1=3D"condition" 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.

Re: compare string to a number in sql

"lyle" wrote

> Null is not less than 1000.

It will certainly be _treated_ as less than 1000, if you have opted to use
NZ (which for numbers, means "treat null as zero"). The quite reasonable
assumption is, that if you opt to use the NZ function, you know how you are
using Null, and regardless of the official definition, elect to treat it as
a zero value.

Larry

Re: compare string to a number in sql

"Larry Linson" wrote in
news:s9bJj.18751$bC6.2194@trnddc08:

> "lyle" wrote
>
> > Null is not less than 1000.
>
> It will certainly be _treated_ as less than 1000, if you have opted to
> use NZ (which for numbers, means "treat null as zero"). The quite
> reasonable assumption is, that if you opt to use the NZ function, you
> know how you are using Null, and regardless of the official
> definition, elect to treat it as a zero value.
>
> Larry

Larry

I'm sure you use NZ knowledgeably and carefully.
I'm sure Allen uses NZ knowledgeably and carefully.
I’m not sure the Original Poster will use NZ knowledgeably and carefully,
(but it’s entirely possible.)
I believe it’s worthwhile to remind (or explain to) the casual reader
that Null is not zero. This has nothing to do with Access, Bill Gates or
the vagaries of binary contraptions. It just IS. When someone with
Larry’s experience and expertise or Allen’s experience and expertise
decides to play with the universe, things may turn out OK. When someone
without those qualities does so, they may not.
I try never to equate Null with zero. (I fail sometimes, of course,
vowing to come back and make things right, later.) I believe that
theoretically sound databases are likely to be sound in circumstances
foreseen, and in circumstances unforeseen.

BTW, if you’ve dated one hundred girls and have asked twenty of those to
go to bed with you and ten have said, “Yes”, and ten have said, “No!” are
you batting .500 or .100?

Lyle

Re: compare string to a number in sql

"lyle fairfield" wrote in message
news:Xns9A75ADED9384E6666646261@216.221.81.119...
> 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.

Re: compare string to a number in sql

"Allen Browne" wrote

>> 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.

Re: compare string to a number in sql

"Larry Linson" wrote in message
news:63hJj.34407$Eq.16954@trnddc05...
> > ... 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.