Text to Int

[Peter Carlson]

I'd like to convert text to int to retrieve bad records. The records
are basically Last, First, ID, Img#. All Img# will be either a real
number, or a text field like 1411/1412 or 1411-1415. I'd like to
retrieve all records that have either no img# or a img number like the 2
above. I use the below sql to do it:
SELECT Last, First, Img, val(Img) as vi from Student_List
where val(Img) = 0;

However I am getting a data type mismatch error. If I remove the where
clause it works just fine, converting the Img# to either a real int or
to #error. How can I get it to work in the where clause?

Peter

 

[Brendan Reynolds]

Could be Null values causing the problem. Try ...

WHERE Val(NZ(

 

[MGFoster]

I'd like to convert text to int to retrieve bad records. The records
are basically Last, First, ID, Img#. All Img# will be either a real
number, or a text field like 1411/1412 or 1411-1415. I'd like to
retrieve all records that have either no img# or a img number like the 2
above. I use the below sql to do it:
SELECT Last, First, Img, val(Img) as vi from Student_List
where val(Img) = 0;

However I am getting a data type mismatch error. If I remove the where
clause it works just fine, converting the Img# to either a real int or
to #error. How can I get it to work in the where clause?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Val("1411/1412") = 1411
Val("abcd") = 0

If the Img# is ALWAYS formatted like 1411/1412 or 1411-1415 then you can
use this WHERE clause:

WHERE Img Like "[0-9][0-9][0-9][0-9]/[0-9][0-9][0-9][0-9]"
OR Img Like "[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"
OR Img Is Null

No Image # is "Img Is Null."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzCARIechKqOuFEgEQLyVwCghgKZ4786AgNLH2D+f3LbAdwCjkoAoI5S
9EsGfGz4tL8wvkkDpZrWYwTR
=SRLP
-----END PGP SIGNATURE-----

 

[Peter Carlson]

Did the trick thanks!
Peter

 

[Peter Carlson]

Val("1411/1412") = 1411
This is bad!....is there anyway to retrieve records where the field is
truly not a number? The format is typically #/#, but could be any
number of digits. A correct record should simply be a #. I guess I
could edit them all as some text character before the #/#

Peter

I'd like to convert text to int to retrieve bad records. The records
are basically Last, First, ID, Img#. All Img# will be either a real
number, or a text field like 1411/1412 or 1411-1415. I'd like to
retrieve all records that have either no img# or a img number like the
2 above. I use the below sql to do it:
SELECT Last, First, Img, val(Img) as vi from Student_List
where val(Img) = 0;

However I am getting a data type mismatch error. If I remove the
where clause it works just fine, converting the Img# to either a real
int or to #error. How can I get it to work in the where clause?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Val("1411/1412") = 1411
Val("abcd") = 0

If the Img# is ALWAYS formatted like 1411/1412 or 1411-1415 then you can
use this WHERE clause:

WHERE Img Like "[0-9][0-9][0-9][0-9]/[0-9][0-9][0-9][0-9]"
OR Img Like "[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"
OR Img Is Null

No Image # is "Img Is Null."

 

[John Spencer (MVP)]

How about using the criteria

[Img#] LIKE "*[!0-9]*" or [Img#] is Null

That should return all records that have a character that is not zero to nine.
If Img is always filled or you don't worry about blanks then you can drop the
"OR Img is Null" criteria

Val("1411/1412") = 1411
This is bad!....is there anyway to retrieve records where the field is
truly not a number? The format is typically #/#, but could be any
number of digits. A correct record should simply be a #. I guess I
could edit them all as some text character before the #/#

Peter

I'd like to convert text to int to retrieve bad records. The records
are basically Last, First, ID, Img#. All Img# will be either a real
number, or a text field like 1411/1412 or 1411-1415. I'd like to
retrieve all records that have either no img# or a img number like the
2 above. I use the below sql to do it:
SELECT Last, First, Img, val(Img) as vi from Student_List
where val(Img) = 0;

However I am getting a data type mismatch error. If I remove the
where clause it works just fine, converting the Img# to either a real
int or to #error. How can I get it to work in the where clause?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Val("1411/1412") = 1411
Val("abcd") = 0

If the Img# is ALWAYS formatted like 1411/1412 or 1411-1415 then you can
use this WHERE clause:

WHERE Img Like "[0-9][0-9][0-9][0-9]/[0-9][0-9][0-9][0-9]"
OR Img Like "[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"
OR Img Is Null

No Image # is "Img Is Null."