Using Or statements from a string

[justice8965]

Hey all

I have a form where a user can select, using check boxes, what types of defects from a sheet of metal they want. They then hit a button and it brings up the query results, showing all sheets with, for example, defect type 1 and type 4. The defect types are labeled as numbers.

What I've been trying to do is send the query a string based on what they choose. For example, the string would be "1 Or 4 Or 5" if they selected 1, 4, and 5.

I am able to make the string, and send it to the query by calling a function in the criteria. However, every time I try to include more than one defect type, I get a data mismatch error. If I choose just one defect, it works fine. I can confirm by stepping thru the code it is getting sent the correct string ie "1 Or 4", but it won't accept it.

Any ideas?

 

[ChrisO]

You can not use "1 Or 4 Or 5" in the query string.

Try doing it in the query grid with those numbers and when you get it working switch to SQL view to see what is created.

 

[dcb]

The syntax looks like:
[field] = 1 or [field] = 3

 

[justice8965]

The SQL view gives me the following:

SELECT tblSheetInspect.Date, tblSheetInspect.Passed, tblSheetInspect.DefectLocation, tblSheetInspect.DefectType
FROM tblSheetInspect INNER JOIN tblDefectType ON tblSheetInspect.DefectType = tblDefectType.Number
WHERE (((tblSheetInspect.Date)>"#1/20/2010#") AND ((tblSheetInspect.Passed)=Passed()) AND ((tblSheetInspect.DefectType)=1 Or (tblSheetInspect.DefectType)=4 Or (tblSheetInspect.DefectType)=5));

How do I translate this to VBA?

 

[justice8965]

Ok I think I figured out basically how to do it, but I have a problem with my syntax

strsql = "SELECT tblSheetInspect.Date, tblSheetInspect.Passed, tblSheetInspect.DefectLocation, tblSheetInspect.DefectType " _
& "FROM tblSheetInspect INNER JOIN tblDefectType ON tblSheetInspect.DefectType = tblDefectType.Number " _
& "WHERE (((tblSheetInspect.Date)>=theStart() And (tblSheetInspect.Date)<=theEnd()) AND ((tblSheetInspect.Passed)=Passed()) AND (newString));"

newString is a variable that contains the defects wanted ie (tblSheetInspect.DefectType)=6

But I get a "too few parameters error" when I try to run it. I know the newString is the problem because if i replace it with (tblSheetInspect.DefectType)=6 it works fine. Whats wrong with my syntax?

 

[DCrake]

If the user selects more than one item the why not use In(1,4,5) instead of Or

tblSheetInspect.DefectType) In(1,4,5)

The only concern is building up the "1,4,5" string prior to running the query. To do this you need to evaluate each of the form controls that count towards your filter condition and parse them together if they have a value in them.

David

 

[justice8965]

Theres actually code above what i posted thats doing the string parsing. I figured out my problem. I just needed (" & newstring & ") instead of just (newstring). Works great now. Thanks all!