numbers stored in text data type. Can't query with <= criteria 2

[TheSouthCole]

I know there must be a simpler solution than to change the field's data type to number (I hope)!

I have numbers in a text data type field. I need to query for numbers <=998. Didn't work. I queried to remove blanks, then queried using Expr1:Val([fieldname]) in a blank field cell. Works wonderfully.

Next, I try adding criteria to the same field cell, and I get a Data Type Mismatch error. If the Val function sees numeric values, why wouldn't criteria of <=998 work?

What am I missing? Thanks!!!

 

[scriverb]

Use the functions CInt, CLng, CDate, CByte, etc. There are a long list of coversion functions that can be found in ACCESS Help. Look under FUNCTIONS. This is the syntax for doing what you want to do if the text is a representation of a long integer:

WHERE CLng([txtFieldName]) <= 998;

Post back with any questions that you may have.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jrbarnett]

What criteria were you using?

If you are applying functions or criteria to numeric data, you have to use Function(Val(Fieldname)) to ensure the data is treated as numeric when processed by &quot;Function&quot;.

If however it works on the text, you have to use:
Val(Function(Fieldname)) to have it work on the numeric then convert to text.

John

 

[TheSouthCole]

Hi, Bob, and thanks very much for the help!

I must be missing something...
Here's my SQL code:

SELECT People.LName, People.FName, People.StateLife
FROM People WHERE CLng([StateLife]) <=998;

when I run it, I get this error: &quot;Invalid Use of Null&quot;

(maybe I should downgrade my own user status )

 

[scriverb]

You must have some Null values in the field [StateLife]. Try this SQL:

SELECT People.LName, People.FName, People.StateLife
FROM People WHERE CLng(NZ([StateLife],0)) <=998;

This NZ function converts a Null value to 0 before we try to convert to a Long Integer. The error occurs when you try to apply the CLng function to a Null value.

Post back with the results.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TheSouthCole]

Hi, again, Bob! I do have some null values in that field.

That code got me &quot;Data Type mismatch in criteria expression&quot;.

I also tried this:

I ran a query that pulled only records with non-null values in the StateLife field. Then I ran your code in a new query that pulled from the non-null-values query. For that attempt, I got &quot;Invalid Use of Null&quot; again.

And I HUGELY appreciate your assistance, BTW! ~~Nancy Cole

 

[scriverb]

Okay, I think I have it now. The NZ function needs to return a zero(0) as a string first and then the CLng value will work just fine. The error was occuring when the NZ was trying to return a numeric into a text field.

SELECT People.LName, People.FName, People.StateLife
FROM People WHERE CLng(NZ([StateLife],&quot;0&quot) <=998;

Let's see how this works.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TheSouthCole]

I get &quot;Data Type Mismatch&quot; in both instances (described below). Here's the weird thing...in Instance #2, I see the data briefly, then I get the error, then the dynaset changes so all fields display &quot;#Name?&quot;.

Instance #1 - query pulling directly from table

Instance #2 - query table only for non-null-values in StateLife, then run your code in a new query that pulls from the &quot;non-null-values&quot; query instead of from the table.

 

[scriverb]

Can you send me a database of just your table with just the field in question. (&quot;[StateLife]&quot. See my email address in my profile. I will get it working and then post back the changes necessary. It has to be something specific to your data.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[CosmoKramer]

Nancy,

See if this works for you, I've used similiar statements in the past:

SELECT People.LName, People.FName, People.StateLife
FROM People WHERE Val(nz([StateLife],0)) <= 998;

 

[scriverb]

The problem here is you have two records that have non-numeric data entered into them. (# sign prior to the number.) Use the following SQL to find the records for repair purposed:

SELECT People.[ContactID#], People.LName, People.FName, People.StateLife
FROM People
WHERE (((People.[ContactID#])=11248)) OR (((People.[ContactID#])=11250));

This problem has nothing to due with the nulls. It simply had to do with comparing a numeric value(998) to a text field with a mixture of text and numbers in two records. It was the comparing #750 <= 998 that caused the problem. (data type mismatch)

Use the following SQL for your query after you fix the data:
SELECT People.LName, People.FName, People.StateLife
FROM People
WHERE (CLng(NZ([StateLife],&quot;0&quot))<=998 and Not IsNull([StateLife])

Post back with questions.





Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TheSouthCole]

Bob, thanks V~E~R~Y much! both of those processes worked beautifully!

Question -- know any good books where I can learn more &quot;obscure&quot; things like these?

Gratefully, Nancy Cole

 

[scriverb]

No not really. It just was experience and deductive reasoning. It made no sense that code wouldn't work and the error Data-type mismatch just didn't fit given the experession.

Also, when I ran the code against the table the query filled with data and then after a while the error occured. I knew then that somewhere down near the end of the table there was bad data. I have run into this stuff before and it clicked. Queries give errors immediately if it is a bad expression usually. Because the query output screen filled with data and then a few seconds later the error occured I knew we had bad data somewhere in the file. You file being 16,000+ records helped us to figure this out. Without the delay it might have taken a little longer.

Good luck on your project. Thanks for the Star. It is appreciated.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???