SQL Query question

**RudyL** · Mar 4th, 2003, 01:51 PM

Can anyone tell me why this sql string gives me an error?

VB Code:

"SELECT * FROM WorkOrders WHERE CustomerRefNumber = 'MM-0000001' AND JobCompleted = '1'"

I am using a MSA 2000 db and ADO.

I get an error of "Data type mismatch"

Thanks,
Rudy -

**radum** · Mar 4th, 2003, 01:53 PM

maybe because the [JobCompleted] field is declared as some sort of numeric in the database. if it is, take off the quotes around the value:

VB Code:

"SELECT * FROM WorkOrders WHERE CustomerRefNumber = 'MM-0000001' AND JobCompleted = 1"

**RudyL** · Mar 4th, 2003, 01:55 PM

The jobcompleted field is a checkbox. I have tried it with a 0 (=false) and a False parameter. I get the same error...

Confusing...

**radum** · Mar 4th, 2003, 01:59 PM

i'm affraid i dont get the idea then. when you submit the query, you have to put in the name of the field as it is declared in the database (the 'column' name), not the name of the checkbox...

**RudyL** · Mar 4th, 2003, 02:03 PM

Yea. "JobCompleted" is the field. Its datatype is a checkbox (yes/no).

This is the sql generated by MSA

SELECT *
FROM WorkOrders
WHERE (((WorkOrders.CustomerRefNumber)="MM-0000001") AND ((WorkOrders.JobCompleted)=True));

**RudyL** · Mar 4th, 2003, 08:01 PM

***Bump***

**RudyL** · Mar 4th, 2003, 08:13 PM

I think it is how I am using the "AND" to select based on 2 different values. Can someone verify that the SQL string is correct?

Thanks,
Rudy

**Mc Brain** · Mar 4th, 2003, 08:17 PM

Try:

"SELECT * FROM WorkOrders WHERE CustomerRefNumber = 'MM-0000001' AND JobCompleted = -1"

**RudyL** · Mar 4th, 2003, 08:33 PM

Nope, that didn't work either.....

I tried another field from the table and get the same error... Is the SQL string correct?? It can't be...

Rudy

**Mc Brain** · Mar 4th, 2003, 08:34 PM

What type of field is CustomerRefNumber?

**RudyL** · Mar 4th, 2003, 08:45 PM

CustomerRefNumber is a String.
JobCompleted is Yes/No.

I tried it with just CustomerRefNumber and it works fine.

VB Code:

SELECT * FROM WorkOrders WHERE CustomerRefNumber = '" & CustomerNumber & "'"

Then I tried these and they didn't work...

VB Code:

"SELECT * FROM WorkOrders WHERE JobCompleted = '0'"
and 
"SELECT * FROM WorkOrders WHERE JobCompleted = '1'"
and 
"SELECT * FROM WorkOrders WHERE JobCompleted = '-1'"

**RudyL** · Mar 4th, 2003, 08:56 PM

GOT IT! Wew, finally..

The key is the "True" statement.. I hade 'True' and it needed to be True without the quotes....

VB Code:

"SELECT * FROM WorkOrders WHERE CustomerRefNumber = '" & CustomerNumber & "' AND JobCompleted = True"

This a new one to me.... I think. I don't remember ever having this problem before..

Oh-well Thank to Mc Brain and Radum for the help..

Rudy

**RudyL** · Mar 4th, 2003, 09:02 PM

I am getting even more confused...

It seems that I can only have the quotes around the first value..

VB Code:

"SELECT * FROM WorkOrders WHERE CustomerRefNumber = '" & CustomerNumber & "' AND Status = " & WorkOrderStatus & " AND JobCompleted = True "

**radum** · Mar 4th, 2003, 09:02 PM

aw jeez... isnt it funny how each sql dialect loves to mess around with the syntax for these boolean values...

a couple of years ago i spent a few good days to figure out a syntax like

VB Code:

myField = .TRUE.

when running a sql statement a foxpro database

**RudyL** · Mar 4th, 2003, 10:19 PM

So let me get this straight..

Time/Date fields are souronded by ##
String/Number fields are souronded by ''
Boolean/True-False as souronded by ..

What else?

Is this an ADO thing? I Never had this problem with RDO...

Rudy

**radum** · Mar 4th, 2003, 10:30 PM

sorry tom dissapoint you, but honestly i dont know all the syntax combinations myself either. that thing with dots works for the foxpro odbc driver, but i doubt it works for something else. im almost sure it doesnt work for ms access in fact. i only worked with sql lately, so trust anybody else giving answers on that. i kinda bailed out of the thread at the point i saw other answers for you...

**Mc Brain** · Mar 4th, 2003, 10:35 PM

Originally posted by RudyL
So let me get this straight..

Time/Date fields are souronded by ##
String/Number fields are souronded by ''
Boolean/True-False as souronded by ..

What else?

Is this an ADO thing? I Never had this problem with RDO...

Rudy

Number fields should not be surrounded at all.

**manu487** · Mar 4th, 2003, 10:58 PM

IN ACCESS,
Time/Date fields are souronded by ##
String/Number fields are souronded by ''
Boolean/True-False as souronded by ..

IN MYSQL

Time/Date fields are souronded by ''
String/Number fields are souronded by ''
Boolean/True-False as souronded by ..

**gaffa** · Mar 4th, 2003, 11:11 PM

If it's a string, wrap single quotes around it. If it's a number, don't use quotes. If it is a boolean value, pass it in as a number (True = 1, False = 0) with no quotes around it.

Passing in True or False will work against an Access database, but not against many others. SQL Server requires it ot be 1 or 0.

Don't forget that CInt(True) in VB6 returns -1, not 1, so you might want to use Abs(CInt(BooleanVariable)) to make sure it works.

- gaffa

**Mc Brain** · Mar 4th, 2003, 11:20 PM

Originally posted by gaffa
...

Don't forget that CInt(True) in VB6 returns -1, not 1, so you might want to use Abs(CInt(BooleanVariable)) to make sure it works.

Or just

VB Code:

-CInt(BooleanVariable)

**RudyL** · Mar 4th, 2003, 11:38 PM

Thanks alot for all the help.. and info.

Rudy

Thread: SQL Query question

Thread Tools

Display

SQL Query question

Posting Permissions