DoCmd.OpenReport trouble

[moishy101]

I'm trying to open a report using the following code:

Private Sub cmdPrintSample_Click()
    Dim strWhere As String
    strWhere = Nz([Forms]![frmReports]![cboCity], "'" & "*" & "'") & _
               " AND " _
               & Nz([Forms]![frmReports]![cboNeighborhood], "'" & "*" & "'") & _
               " AND " _
               & Nz([Forms]![frmReports]![cboRooms], "'" & "*" & "'")
    DoCmd.OpenReport "rptSampler", acViewPreview, , strWhere
End Sub

I get prompted for a parameter (the name of the city) even when info is entered on the form, if I re-enter the parameter it opens the report ignoring the strWhere, and if I don't re-enter the parameter it opens the report without records.

Any idea what's wrong?

[Rabbit]

1) If it's just the cboCity that's popping up the parameter box, then check the name of the control.

2) If it's all of them, check the name of the controls and the form.

3) If it's not actually cboCity and some other city parameter, check the parameters in the query.

4) The reason your strWhere is being ignored is because it's coded incorrectly, you're not telling it which field to filter on.

For example, if your city is Los Angeles:

What you have:
strWhere = "Los Angeles"
 
What you need:
strWhere = "cityField = 'Los Angeles'"

[dsatino]

First, see Rabbits response...

Your NZ() function is also not going to get you what you want if your fields are null. It looks like you are trying to get any city if the city is blank, but you under your current syntax you will return no results because you are telling it to look for cities names "*".

[moishy101]

@Rabbit
Would this do the job?

"City = '[Forms]![frmReports]![cboCity]'"

@dsatino
What is the proper syntax?

[Rabbit]

It will not. The engine won't have access to the form values. You need to use your current approach, you just need to tell it what field to filter on.

[moishy101]

How would I do that?

[Rabbit]

strWhere = "cityField Like '" & Nz([Forms]![frmReports]![cboCity], "*") & "'"

[moishy101]

I tried your suggestion

strWhere = " A.City Like '" & Nz([Forms]![frmReports]![cboCity], "*") & "'"

, it ended in a type mismatch error.

Giving Los Angeles instead of 'Los Angeles'.

[Rabbit]

That's an easy error message to troubleshoot. If there's a type mismatch, then you need to check the data types to see which one isn't matching up.

[moishy101]

Ok I think I got it, I had added another filter using the AND keyword Should have been & " AND " &.