Change field data type in make table query

[LaurieW]

I have a text field that contains numbers that I want to change to a numeric field during a make table query. (Please note that I do not not have the option of changing the original table's data type.) (Also, I'm using Access 2000.)

I've been searching forum posts and see people changing data types during queries with operators like CInt, CDbl, CLng. I've tried all of these and still get the error "data type mismatch in criteria expression." What am I doing wrong? I've also tried using Val, and that works, but it gives me extra records I don't want.

Here is my SQL code using CInt:
SELECT CInt([CONINFO]![Contract/PO #]) AS PONum, CONINFO.[Title/Description], CONINFO.[Spec Writer], CONINFO.[Award Date] INTO tblPOCAO
FROM CONINFO
WHERE (((CInt([CONINFO]![Contract/PO #])) Between [Enter low number range] And [Enter high number range]))
ORDER BY CInt([CONINFO]![Contract/PO #]);

Here is my SQL code without trying to change the data type:
SELECT CONINFO.[Contract/PO #] AS PONum, CONINFO.[Title/Description], CONINFO.[Spec Writer], CONINFO.[Award Date] INTO tblPOCAO
FROM CONINFO
WHERE (((CONINFO.[Contract/PO #]) Between [Enter low number range] And [Enter high number range]))
ORDER BY CONINFO.[Contract/PO #];

Thanks!

 

[dmonney]

What is the orignial datatype, Does it ever include letters?

 

[LaurieW]

The original data type is text and yes, it does include letters, but not for the ranges I am working with.

 

[dmonney]

I may be way off but i have had instances when it gives me dataype errors when i have a \ in the name (it thinks i want it to devide)

 

[LaurieW]

That wasn't it ... I removed the "/" in the field name and renamed the field to PONumNew and it still gives me the mismatch error...thanks for the suggestion tho!

 

[LaurieW]

Here is what I figured out: I have to do this in two steps, I believe, because some of the records have letters in them. Even though the number ranges I am collecting do not have letters, Access won't let me change the data type in my new table. So, if I have my first query make a table with the desired number range I can then run a second make table query that will change the data type. Oui la!