converting data types

[rocco]

Hello,
i need to convert values in one column in integer dtaa type to use in SQL
statment.
it seems both CAST or CONVERT wont work in JET.
How can I solve my problem?
i have tried
SELECT ID
FROM table
WHERE CAST(visit as integer)>13

But it doesn't work.

visit is a string datatype that can have values going from 1 to 21. it is
the visit number of my patients (don't ask why who built the Db hasn't used
integer data type...)
i want to find ID that had been visited more than 13 times.
How can i convert visit data-type to use the code posted above?

i'm using Access 2003 with JET (should work also on Access 2000)

thanks
Rocco

 

[Allen Browne]

VBA has a number of type conversion functions, such as CInt(), CDbl(),
CLng(), CVDate().

If you are expecting "integer" to be long, try:
SELECT ID
FROM table
WHERE CLng([visit]) > 13

 

[rocco]

i wrote this code:
SELECT ID
FROM DSPC_CONTACT
WHERE CInt(VISIT)> 13

 

[rocco]

I have tried this:

SELECT CONTACT1.ID, CONTACT1.VISIT
FROM (SELECT ID, VISIT FROM DSPC_CONTACT WHERE VISIT IN
('10','11','12','13','14')) AS CONTACT1
WHERE Cint(CONTACT1.VISIT)>12

just to be sure to have only values that can be convertyed as numbers.
It still wont work!!!!!!!!!!!!!!!!!!!!!!
'data type mismatch in criteria expression'...AGAIN?!

VBA has a number of type conversion functions, such as CInt(), CDbl(),
CLng(), CVDate().

If you are expecting "integer" to be long, try:
SELECT ID
FROM table
WHERE CLng([visit]) > 13

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,
i need to convert values in one column in integer dtaa type to use in SQL
statment.
it seems both CAST or CONVERT wont work in JET.
How can I solve my problem?
i have tried
SELECT ID
FROM table
WHERE CAST(visit as integer)>13

But it doesn't work.

visit is a string datatype that can have values going from 1 to 21. it is
the visit number of my patients (don't ask why who built the Db hasn't
used
integer data type...)
i want to find ID that had been visited more than 13 times.
How can i convert visit data-type to use the code posted above?

i'm using Access 2003 with JET (should work also on Access 2000)

thanks
Rocco

 

[John Vinson]

I have tried this:

SELECT CONTACT1.ID, CONTACT1.VISIT
FROM (SELECT ID, VISIT FROM DSPC_CONTACT WHERE VISIT IN
('10','11','12','13','14')) AS CONTACT1
WHERE Cint(CONTACT1.VISIT)>12

just to be sure to have only values that can be convertyed as numbers.
It still wont work!!!!!!!!!!!!!!!!!!!!!!
'data type mismatch in criteria expression'...AGAIN?!

You're using a Subquery - the parenthesized (SELECT ID, VISIT...)
stuff. Allen did not suggest a Subquery, and it will in fact not work,
and it is in fact not necessary. In addition, your criteria in the
subquery contradict the >12 criterion in the main query. Where did the
subquery come from!? It's just making life harder for you!

Try

SELECT Contact1.ID, Contact1.Visit
FROM DSPC_Contact
WHERE Cint(Contact1.Visit) > 12;

Copy and paste this into the SQL window of a query, or (alternatively)
create a new query based on Contact1 and put

CInt([Visit])

in a vacant Field cell; put

12

on the criteria line underneath it.

John W. Vinson[MVP]

 

[rocco]

I have used a subquery because the field visit can hold null values.
The subqyery needs to filter only data that can be converted into numerica
data types.
Where is the contraddiction? The subquery will give any visit numbered from
10 to 14 and then the criteria in the where clause should filter only the
ones greater than 12.
Thanks.

I have tried this:

SELECT CONTACT1.ID, CONTACT1.VISIT
FROM (SELECT ID, VISIT FROM DSPC_CONTACT WHERE VISIT IN
('10','11','12','13','14')) AS CONTACT1
WHERE Cint(CONTACT1.VISIT)>12

just to be sure to have only values that can be convertyed as numbers.
It still wont work!!!!!!!!!!!!!!!!!!!!!!
'data type mismatch in criteria expression'...AGAIN?!

You're using a Subquery - the parenthesized (SELECT ID, VISIT...)
stuff. Allen did not suggest a Subquery, and it will in fact not work,
and it is in fact not necessary. In addition, your criteria in the
subquery contradict the >12 criterion in the main query. Where did the
subquery come from!? It's just making life harder for you!

Try

SELECT Contact1.ID, Contact1.Visit
FROM DSPC_Contact
WHERE Cint(Contact1.Visit) > 12;

Copy and paste this into the SQL window of a query, or (alternatively)
create a new query based on Contact1 and put

CInt([Visit])

in a vacant Field cell; put

12

on the criteria line underneath it.

John W. Vinson[MVP]