By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

DoCmd.OpenReport trouble

P: 46
I'm trying to open a report using the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintSample_Click()
  2.     Dim strWhere As String
  3.     strWhere = Nz([Forms]![frmReports]![cboCity], "'" & "*" & "'") & _
  4.                " AND " _
  5.                & Nz([Forms]![frmReports]![cboNeighborhood], "'" & "*" & "'") & _
  6.                " AND " _
  7.                & Nz([Forms]![frmReports]![cboRooms], "'" & "*" & "'")
  8.     DoCmd.OpenReport "rptSampler", acViewPreview, , strWhere
  9. 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?
Jul 23 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,315
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:
Expand|Select|Wrap|Line Numbers
  1. What you have:
  2. strWhere = "Los Angeles"
  3.  
  4. What you need:
  5. strWhere = "cityField = 'Los Angeles'"
Jul 23 '12 #2

dsatino
100+
P: 393
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 "*".
Jul 23 '12 #3

P: 46
@Rabbit
Would this do the job?
Expand|Select|Wrap|Line Numbers
  1. "City = '[Forms]![frmReports]![cboCity]'"
@dsatino
What is the proper syntax?
Jul 23 '12 #4

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jul 23 '12 #5

P: 46
How would I do that?
Jul 23 '12 #6

Rabbit
Expert Mod 10K+
P: 12,315
Expand|Select|Wrap|Line Numbers
  1. strWhere = "cityField Like '" & Nz([Forms]![frmReports]![cboCity], "*") & "'"
Jul 23 '12 #7

P: 46
I tried your suggestion
Expand|Select|Wrap|Line Numbers
  1. strWhere = " A.City Like '" & Nz([Forms]![frmReports]![cboCity], "*") & "'"
, it ended in a type mismatch error.

Giving Los Angeles instead of 'Los Angeles'.
Jul 26 '12 #8

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jul 26 '12 #9

P: 46
Ok I think I got it, I had added another filter using the AND keyword Should have been & " AND " &.
Jul 26 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.