Data type mismatch using sql 1

[chidori]

Hi,

When I want to compare a field to a number I get an error saying there is a datatype missmatch. I think the problem is that the MS-Access field that I am comparing to is a number field instead of a text field. So how can I get around this?
The code I used is:

SELECT pd_svgrp, pd_active, Count(pd_active) AS CountOfpd_active
FROM PRODUCTS
GROUP BY pd_svgrp, pd_active
HAVING pd_active='1'; <------ that is the problem



This query would run perfectly if the pd_active field was a text field instead of a number field in Access. However it is not and I dont have the ability to convert the fields in Access into a text field. How can I make the above query work?


Any help is appreciated,
Thanks!

 

[Sarky78]

if your field pd_active is a numeric field then all you have to do is to change the query like this:

SELECT pd_svgrp, pd_active, Count(pd_active) AS CountOfpd_active
FROM PRODUCTS
GROUP BY pd_svgrp, pd_active
HAVING pd_active=1

ie remove the ' from around the 1.

 

[Wes98765]

Here is a nice function that I use, it keeps you from ever getting this datatype error.

(#PreserveSingleQuotes(variable)#) this will add single quotes when needed