SQL Query question

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 - :(

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"

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

Confusing...

:confused: :(

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...

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


This is the sql generated by MSA

Quote:

---

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

---

***Bump***

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

Try:

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

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

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


:(
Rudy

What type of field is CustomerRefNumber?

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'"

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

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 "

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

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

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...

Quote:

---

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.

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 ..

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

Quote:

---

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)

Thanks alot for all the help.. and info.

Rudy