Open Reports with multiple Where Clause

[Snowman88]

Hi again!

I can't figure this out myself.....so if anyone likes to help, I would be really glad!

Ok, here is the problem:

I got a form with 7 listboxes and a Command Button.

Box1 chooses the report
box2 the clientname
box3 to 7 choose different variables depending on the choosen report.

So i want to do something like
DoCmd.OpenReport "rpDetails", acPreview, , "[clientname] " & strclientnames & "" And Box3-7

I already have done this for every box:

If IsNull(Me.cboGAclientnames.Value) Then
strclientnames = "Like '*'"
Else
strclientnames = "='" & Me.cboGAclientnames.Value & "'"
End If

I tried to build the OpenReport String with one more box, but i get a mismatch error and don't know why

 

[The_Doc_Man]

Usually, this is caused by building the string incorrectly. In all probability, your problem is incorrect quoting.

The last time I did something like this, I built the contents of the WHERE clause separately as a string, then filtered the report using the one string.

Also, if you have no value selected in the box, you don't want to add .."[fieldname] LIKE ""*"" * - you want to add NOTHING. I.e. if you don't qualify it, you get the same effect as "LIKE *" - but without the headaches.

 

[boblarson]

and one more point about dynamically building the where clause: you have to remember to have a space in between the parts you build so that they don't run together and become meaningless gibberish to Access.

 

[Snowman88]

I thought about the string thing, but I am not sure how to do it. When i build that string, how do I structure the where clause? Because I don't have a field then, if you know what I mean.

And I just tested using a clause like
"[clientname] ='" & Me.cboGAclientnames.Value & "'"

and I get no results if I leave the combo box empty.


Edit:

Full Code:



strclientnames = "='" & Me.cboGAclientnames.Value & "'"
strSYSmember = "='" & Me.cboSYSmember.Value & "'"

DoCmd.OpenReport "rpSystem", acPreview, , "[clientname] " & strclientnames And "[membership] " & strSYSmember & ""

 

[boblarson]

Use If statements to build it:

If Not IsNull(Me.cboGAclientnames) Then
strWhere = "[clientname]='" & Me.cboGAclientnames & "' And "
End If

If Not IsNull(Me.cboAnotherOne) Then
strWhere = strWhere & "[myotherfield]='" & Me.cboAnotherOne & "' And "
End If

strWhere = Left(strWhere, Len(strWhere) - 5) ' to get rid of the last And and space as you don't know where the thing will end if they only select less than the maximum.

At least that's how I've done it before in the past.

 

[Snowman88]

And how do I build the statement?

DoCmd.OpenReport "rpSystem", acPreview, , ??

 

[boblarson]

After you build strWhere then it would just be:

DoCmd.OpenReport "rpSystem", acPreview, , strWhere

 

[Snowman88]

Excellent, works like a charm! You are the man, Bob! Thanks a lot!

Doc, too, of course