Using combo box selection to supply criteria for query

[Douglas J. Steele]

Right after your code

If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 5)
End If

put the statement

Debug.Print strWhere

Once the code runs, go to the Immediate Window (Ctrl-G) and see what's
written there.

It's possible your combo boxes aren't bound to the field to which you think
they're bound.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug -
Sorry, I was responding in the middle of the conversation that
everyone was having, I did not see the new code.

OK, the report now opens correctly if I have no combo box selections.

If I select something in the combo boxes for SBT Account (text
format), ADM (text format), or Month (number format) I receive a Run-
time error 3075: Syntax error (missing operator) in query expression
'[ARCHIVE_ADM_Summary].ADM = 'AE' (error is the same for SBT account
selection, just replace 'AE' with 'Meijer')
Or I get a Run-time error 3075: Syntax error (missing operator) in
query expression '[ARCHIVE_ADM_Summary].[Month] = '.

If I select something in the Region combo box I get an 'Enter
Parameter Value' box that opens up.

If I select something in the Year combo box (right now only 2009 is
populated in my tables) I get a blank report, no error messages.

Here is the updated code that I have right now, I believe that I
captured the most recent recommendations:

Dim strWhere As String
Dim strReportName As String

strWhere = ""
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "'
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
"' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If


If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 5)
End If


DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere

What's the actual code you're using (Steve's code does not have WHERE 1 =
1
anywhere in it).

Incidentally, Steve's code does have an error. You should be adding "' AND
", not "' AND" (the difference is the space after the word AND)

and the line of code

strWhere = Left$(strWhere, Len(strWhere) - 4)

should be

strWhere = Left$(strWhere, Len(strWhere) - 5)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


OK, made the changes. It runs for 2 seconds and then pops up with an
error 3075: Syntax error (missing operator) in query expression 'WHERE
1 = 1 AND'.
The debugger highlights the DoCmd line:
DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere

Is it possibly an error caused by how I built the report?

Many thanks John..... I knew that. <headsmack>
I've been creating too many recordsets in code lately...

Now the code should look like:

'-------------------------------------
Dim strWhere As String
' Dim strReportName As String ' REM'ed - not used

strWhere = ""
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'-------------------------------------

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Steve Sanford]

I always add the space to the front of the string - that way I can see if I
miss a space.

strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

What's the actual code you're using (Steve's code does not have WHERE 1 = 1
anywhere in it).

Incidentally, Steve's code does have an error. You should be adding "' AND
", not "' AND" (the difference is the space after the word AND)

and the line of code

strWhere = Left$(strWhere, Len(strWhere) - 4)

should be

strWhere = Left$(strWhere, Len(strWhere) - 5)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OK, made the changes. It runs for 2 seconds and then pops up with an
error 3075: Syntax error (missing operator) in query expression 'WHERE
1 = 1 AND'.
The debugger highlights the DoCmd line:
DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere

Is it possibly an error caused by how I built the report?

Many thanks John..... I knew that. <headsmack>
I've been creating too many recordsets in code lately...

Erick,

Now the code should look like:

'-------------------------------------
Dim strWhere As String
' Dim strReportName As String ' REM'ed - not used

strWhere = ""
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'-------------------------------------

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

On Mon, 21 Sep 2009 13:15:01 -0700, Steve Sanford <limbim53 at yahoo dot
com>
wrote:

Just in case, here is the code that I currently have, I may have
missed something that you had in your update.

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"

Lose the word WHERE.

The WhereCondition argument of an OpenReport or OpenForm method needs to
be a
valid SQL WHERE clause *WITHOUT* the word WHERE included.
--

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

 

[Steve Sanford]

Change the code to look like this:

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

Docmd.Print strWhere

' DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere



Run the code, then open the immediate window (press contrl-G), copy the line
and paste in in a post so I can see what the string looks like.

 

[Erick C]

OK everybody, new results.
Steve -
I made the changes to the If Len section.
Doug -
I added the debug line.

I tried to run the report again and it ran with no combo box
selections. I then tried some of the combo boxes, and almost
everything is working. The only combo box that is not working still
is the Region selection. It looks like I am maybe missing a '
Here is the results from the immediate window, as you can see I tried
a few different combo boxes:

[ARCHIVE_ADM_Summary].ADM = 'AH'
[ARCHIVE_ADM_Summary].ADM = 'CF'
[ARCHIVE_ADM_Summary].Region = C'
[ARCHIVE_ADM_Summary].[Month] = 7
[ARCHIVE_ADM_Summary].[Month] = 7 And [ARCHIVE_ADM_Summary].[Year] =
2009
[ARCHIVE_ADM_Summary].[Year] = 2009
[ARCHIVE_ADM_Summary].[SBT Account] = 'Fresh & Easy'
[ARCHIVE_ADM_Summary].[SBT Account] = 'Winn-Dixie'
[ARCHIVE_ADM_Summary].Region = C'

 

[John W. Vinson]

I tried to run the report again and it ran with no combo box
selections. I then tried some of the combo boxes, and almost
everything is working. The only combo box that is not working still
is the Region selection. It looks like I am maybe missing a '
Here is the results from the immediate window, as you can see I tried
a few different combo boxes:

So put in the quote:

If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = '"
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "'
AND"

Note the singlequote after Region =

 

[Erick C]

John -
Thank you for the update.
SUCCESS! The report is updating correctly!
Thank you all for all of the time and effort that you put in, it is
greatly appreciated.
I do have one more quick question. Is there an easy way for me to
modify this code so it creates a query of the results rather than a
report? I ask because I know that some people may want to take the
results and export them to excel. I would rather not automatically
export to excel, rather I would want to set up another button that
would show the results in datasheet view.
Thank you all again!

I tried to run the report again and it ran with no combo box
selections.  I then tried some of the combo boxes, and almost
everything is working.  The only combo box that is not working still
is the Region selection.  It looks like I am maybe missing a '
Here is the results from the immediate window, as you can see I tried
a few different combo boxes:

So put in the quote:

   If Not IsNull([Forms]![Main_Form]![Region Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = '"
      strWhere = strWhere & [Forms]![Main_Form]![Region Select] &"'
AND"

Note the singlequote after Region =