Combining strWhere in VB (1 Viewer)

[proballin]

I have two strWhere statements in some VB code, each representing a list box. Individually they work but when I try to combine them I get either #Error when I make a selection from both list boxes or a syntax error 3075 when only one list box is selected. Currently I tried combining the two strWhere statements as such:

strWhere = strWhereMaterial & " And " & strWherePlant

Then I put the "strWhere" into the open report command:

DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere, OpenArgs:=strDescrip

Any suggestions?

 

[pbaldy]

What does the string contain at that point?

 

[proballin]

I'm sorry, I'm not too sure what you are asking...what does which string contain at what point?

 

[pbaldy]

What would the contents of strWhere be at the time it was used as a wherecondition?

 

[proballin]

strWhere = strWhereMaterial & " And " & strWherePlant

When we arrive at the whereCondition, strWhere should hold the Materials selected in it's list box and the Plants selected in its list box. These are multi select list box so they can have more than one selection. If none are selected then all should appear. Once again, when I do the respective code seperate for Material and Plant it all works out. However I am having trouble combining both.

 

[MSAccessRookie]

strWhere = strWhereMaterial & " And " & strWherePlant

When we arrive at the whereCondition, strWhere should hold the Materials selected in it's list box and the Plants selected in its list box. These are multi select list box so they can have more than one selection. If none are selected then all should appear. Once again, when I do the respective code seperate for Material and Plant it all works out. However I am having trouble combining both.

As a Followup:

What are the values of strWhereMaterial And strWherePlant?

 

[proballin]

streWhereMaterial and strWherePlant are formatted the same:

strWherePlant = "[Plant Number] IN (" & Left$(strWherePlant, lngLen) & ")"
strWhereMaterial = "[Material Number] IN (" & Left$(strWhereMaterial, lngLen) & ")"

These show what numbers were selected in those respective list boxes, for example,

"[Plant Number] IN (13,27)"

So all plants that's 13 and 27 should display.

 

[MSAccessRookie]

streWhereMaterial and strWherePlant are formatted the same:

strWherePlant = "[Plant Number] IN (" & Left$(strWherePlant, lngLen) & ")"
strWhereMaterial = "[Material Number] IN (" & Left$(strWhereMaterial, lngLen) & ")"

These show what numbers were selected in those respective list boxes, for example,

"[Plant Number] IN (13,27)"

So all plants that's 13 and 27 should display.

In the example provided is there any chance that any part of the value is incorrect?

 

[proballin]

In the example provided is there any chance that any part of the value is incorrect?

No...Ive tested each list box out seperately and they are indeed correct. It is when combining both strWhere statements is where I have trouble.

 

[pbaldy]

Holy cow; put this right before the OpenReport line and post what shows up in the immediate window:

Debug.Print strWhere

 

[proballin]

When I wrote that line before the openreport nothing happened...I just got the report opened with the #Error again. However I went in and stepped through the code. When I reach the last strWhere this is what I get:

strWhere = "[Plant Number] IN (13,55) And [Material Number] IN (130150-000,130170-000)"

So this is what is passed to that whereCondition:

DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere, OpenArgs:=strDescrip

 

[pbaldy]

The dash in the second value implies it's a text value rather than numeric (the data type of the field). Presuming so, each value must be surrounded by single quotes.

 

[MSAccessRookie]

When I wrote that line before the openreport nothing happened...I just got the report opened with the #Error again. However I went in and stepped through the code. When I reach the last strWhere this is what I get:

strWhere = "[Plant Number] IN (13,55) And [Material Number] IN (130150-000,130170-000)"

So this is what is passed to that whereCondition:

DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere, OpenArgs:=strDescrip

It looks like those Material Numbers need to be strings.

Note: pbaldy said the same thing, only faster than me

 

[proballin]

Thanks to you both...got it!