CAST or CONVERT in Access

owain · July 3rd, 2003, 10:58 AM

The CAST and CONVERT column functions do not seem to work in MS Access. How can I convert a text column to an integer column?

For example, I have read that in SQL2 you can use the function CAST(Col1 AS int) and in T-SQL you can use CONVERT(Col1, int) to convert Col1 to an integer.

Neither of these seem to work in Access, am I doing something wrong, is it not possible, or is there another function I can use?

Regards
Owain Williams

rgerald · July 3rd, 2003, 11:05 AM

Try the CInt or the CLng function, depending upon whether you want an integer (16 bit) or a long integer (32 bit).

Rand

owain · July 3rd, 2003, 11:09 AM

Thank you for your swift reply. That worked a treat ;)

Regards
Owain Williams

rgerald · July 3rd, 2003, 11:11 AM

Since CInt and CLng are VB functions, they WILL NOT WORK in SQL Server directly.

Rand

owain · July 4th, 2003, 04:01 AM

Thanks for the advice, however I have hit another problem. The field can contain NULL values, so using CLng can raise a run-time error '94': Invalid use of Null. Just to clarify, the statement I am using is:

Code:

SELECT COUNT(*) FROM Image WHERE CLng(User1) > 0

I have tried adding the clause 'User1 IS NOT NULL' to the statement:

Code:

SELECT COUNT(*) FROM Image WHERE User1 IS NOT NULL OR CLng(User1) > 0

however the run-time error still occurs. Is there any way round this?

Regards
Owain Williams

owain · July 4th, 2003, 04:26 AM

I have found a solution. Because the field is a text field that can contain Null values, and I am only interested in the fields that contain a number I am using the following SQL statement:

Code:

SELECT COUNT(*) FROM Image WHERE Val(User1 & "") > 0

So a Null length string is concatenated to the field to eliminate any Null values, then it is Val'ed to produce a number.

Regards
Owain Williams

KenSchaefer · July 6th, 2003, 10:18 PM

SELECT COUNT(*) FROM Image WHERE IsNumeric(User1) AND User1 IS NOT NULL

will work. I don't think you want to use VAL() if you can avoid it.

Cheers
Ken

www.adOpenStatic.com

owain · July 7th, 2003, 03:48 AM

Ken, your query will work, however the acceptable values for my column are a positive integer and -1. I need to be able to differentiate between the two so I am using 2 queries:

Code:

SELECT COUNT(*) FROM Image WHERE Val(User1 & "") > 0

to test for positive integers and

Code:

SELECT COUNT(*) FROM Image WHERE Val(User1 & "") = -1

to test for -1. The query you suggested will only test for the existence of a number.

Regards
Owain Williams

Jeff Mason · July 7th, 2003, 08:55 AM

You could also try the 'nz' function:

Code:

SELECT COUNT(*) FROM Image WHERE nz(User1, 0) > 0

or

SELECT COUNT(*) FROM Image WHERE nz(User1, 0) = -1

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · July 7th, 2003, 09:14 AM

Thanks Jeff that works very well, however I have not got a clue what it is doing! Would you mind pointing me in the direction of the syntax and meaning of the 'nz' function?

Regards
Owain Williams