Multiple Combo Boxes Limiting a Query

[Coral G via AccessMonster.com]

Hello! I tried searching for an answer to this question but didn't come up
with anything, so forgive me if this is a repeat. A while ago I asked a
question about combo boxes limiting a query with one of the options being not
to pick something out of the box, the answer being a simple Or IsNull
statement (
http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/32275/Form-Combo-Box-for-Query-Criteria
is the original question).

This has worked great up until this point, but now I'm working on a different
database and there is going to be a similar situation but instead there are
five combo boxes limiting one query. I'm having a hard time finding a way to
come up with all of the Or statements that I'm going to need, is there an
"easy" way to do this?

Thanks!

 

[Guest]

If you are using the combo boxes to filter the records when opening a form, I
would attempt to remove the criteria from your form's record source
query/SQL. Try write some code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboNumericOne) Then
strWhere = strWhere & " [MyNumericField] =" & Me.cboNumericOne
End If
If Not IsNull(Me.cboTextTwo) Then
strWhere = strWhere & " [MyTextField]=""" & Me.cboTextTwo & """ "
End If
If Not IsNull(Me.cboDateThree) Then
strWhere = strWhere & " [MyDateField]=#" & Me.cboDateThree & "# "
End If
' add more combo box sections here...
DoCmd.OpenForm "frmYourForm", , , strWhere

 

[John Spencer]

Duane,

I think you missed the conjuction - AND - in your example code.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboNumericOne) Then
strWhere = strWhere & " AND [MyNumericField] =" & Me.cboNumericOne
End If
If Not IsNull(Me.cboTextTwo) Then
strWhere = strWhere & " AND [MyTextField]=""" & Me.cboTextTwo & """ "
End If
If Not IsNull(Me.cboDateThree) Then
strWhere = strWhere & " AND [MyDateField]=#" & Me.cboDateThree & "# "
End If

' add more combo box sections here...
DoCmd.OpenForm "frmYourForm", , , strWhere

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

If you are using the combo boxes to filter the records when opening a
form, I
would attempt to remove the criteria from your form's record source
query/SQL. Try write some code like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboNumericOne) Then
strWhere = strWhere & " [MyNumericField] =" & Me.cboNumericOne
End If
If Not IsNull(Me.cboTextTwo) Then
strWhere = strWhere & " [MyTextField]=""" & Me.cboTextTwo & """ "
End If
If Not IsNull(Me.cboDateThree) Then
strWhere = strWhere & " [MyDateField]=#" & Me.cboDateThree & "# "
End If
' add more combo box sections here...
DoCmd.OpenForm "frmYourForm", , , strWhere

--
Duane Hookom
Microsoft Access MVP

Hello! I tried searching for an answer to this question but didn't come
up
with anything, so forgive me if this is a repeat. A while ago I asked a
question about combo boxes limiting a query with one of the options being
not
to pick something out of the box, the answer being a simple Or IsNull
statement (
http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/32275/Form-Combo-Box-for-Query-Criteria
is the original question).

This has worked great up until this point, but now I'm working on a
different
database and there is going to be a similar situation but instead there
are
five combo boxes limiting one query. I'm having a hard time finding a
way to
come up with all of the Or statements that I'm going to need, is there an
"easy" way to do this?

Thanks!

 

[Coral G via AccessMonster.com]

Ok, now I'm in unfamiliar territory and going to sound like the complete
novice I am, I'm afraid. Where does this code go? I've only ever written
statements in the SQL view of the query and this doesn't seem like it belongs
there.

Duane,

I think you missed the conjuction - AND - in your example code.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboNumericOne) Then
strWhere = strWhere & " AND [MyNumericField] =" & Me.cboNumericOne
End If
If Not IsNull(Me.cboTextTwo) Then
strWhere = strWhere & " AND [MyTextField]=""" & Me.cboTextTwo & """ "
End If
If Not IsNull(Me.cboDateThree) Then
strWhere = strWhere & " AND [MyDateField]=#" & Me.cboDateThree & "# "
End If

' add more combo box sections here...
DoCmd.OpenForm "frmYourForm", , , strWhere

If you are using the combo boxes to filter the records when opening a
form, I

[quoted text clipped - 34 lines]

 

[Guest]

Thanks for the correction John.

The easiest way to implement this solution is to use the command button
wizard to create a button that opens your form. Once the button has been
created, right click it in design view and choose "Build Event...". This will
open the code window where you can modify your code include the code to build
a where condition.

--
Duane Hookom
Microsoft Access MVP

Ok, now I'm in unfamiliar territory and going to sound like the complete
novice I am, I'm afraid. Where does this code go? I've only ever written
statements in the SQL view of the query and this doesn't seem like it belongs
there.

Duane,

I think you missed the conjuction - AND - in your example code.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboNumericOne) Then
strWhere = strWhere & " AND [MyNumericField] =" & Me.cboNumericOne
End If
If Not IsNull(Me.cboTextTwo) Then
strWhere = strWhere & " AND [MyTextField]=""" & Me.cboTextTwo & """ "
End If
If Not IsNull(Me.cboDateThree) Then
strWhere = strWhere & " AND [MyDateField]=#" & Me.cboDateThree & "# "
End If

' add more combo box sections here...
DoCmd.OpenForm "frmYourForm", , , strWhere

If you are using the combo boxes to filter the records when opening a
form, I

[quoted text clipped - 34 lines]

Thanks!

 

[Coral G via AccessMonster.com]

Thanks for the help John and Duane!

I took out all the criteria in my query and then made a new button to open
the query and used your code as a guideline but the query doesn't seem to be
working now. Right now it's not limiting anything, just dumping the entire
table in. I'm sure I probably did something wrong but I have no idea what it
is since I'm not sure what a lot of the code you posted does. Here's what
the code looks like now, hopefully one of you can see what the problem is.

Dim stDocName As String
strWhere = "1=1 "
If Not IsNull(Me.Combo2) Then
strWhere = strWhere & " AND [All Orders Table Cached!SO Type]=""" & Me.
Combo2 & """ "
End If
If Not IsNull(Me.Combo4) Then
strWhere = strWhere & " AND [All Orders Table Cached!Branch]=""" & Me.
Combo4 & """ "
End If
If Not IsNull(Me.Combo6) Then
strWhere = strWhere & " AND [All Orders Table Cached!Sold to]=""" & Me.
Combo6 & """ "
End If
If Not IsNull(Me.Combo10) Then
strWhere = strWhere & " AND [All Orders Table Cached!Yr Inv]=""" & Me.
Combo10 & """ "
End If
If Not IsNull(Me.Combo12) Then
strWhere = strWhere & " AND [All Orders Table Cached!GLCC]=""" & Me.Combo12
& """ "
End If
stDocName = "All Orders Table Cached Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Thanks for the correction John.

The easiest way to implement this solution is to use the command button
wizard to create a button that opens your form. Once the button has been
created, right click it in design view and choose "Build Event...". This will
open the code window where you can modify your code include the code to build
a where condition.

Ok, now I'm in unfamiliar territory and going to sound like the complete
novice I am, I'm afraid. Where does this code go? I've only ever written

[quoted text clipped - 25 lines]