Search Query with multiple criteria

[martin DH]

Hello all,

I'm back with another SQL related problem. The details are below, but in short: I am using Access 2003. I have a table whose structure may include four different associate names per record (full structure below). I have a query that should return records that have one or more entered names (as criteria from a form) belonging to them.

TableName=COMPI LE_HIST

CompileID, autonumber, PK
ResultsID, number
Month, number, FK
Year, number
Market, number, FK
ClientID,number, FK
ClientName, text
NAC, text (potential search criteria name)
AE, text (potential search criteria name)
SalesPerson, text (potential search criteria name)
SalesManager, text (potential search criteria name)

QueryName=Expor t Historical Query
[CODE=sql]
SELECT COMPILE_HIST.Co mpileID, COMPILE_HIST.Re sultsID, MONTH.Month, COMPILE_HIST.Ye ar, Market.Market, COMPILE_HIST.Cl ientID, COMPILE_HIST.Cl ientName, COMPILE_HIST.AE , COMPILE_HIST.NA C, COMPILE_HIST.Sa lesPerson, COMPILE_HIST.Sa lesManager
FROM COMPILE_HIST, [MONTH], Market
WHERE (COMPILE_HIST.M onth=MONTH.Mont hID) And (COMPILE_HIST.M arketID=Market. MarketID) AND ((COMPILE_HIST. NAC) Like (Forms![Export Historical Form]!NAC) & "*") AND ((COMPILE_HIST. AE) Like (Forms![Export Historical Form]!AE) & "*") AND ((COMPILE_HIST. SalesPerson) Like (Forms![Export Historical Form]!SalesP) & "*") AND ((COMPILE_HIST. SalesManager) Like (Forms![Export Historical Form]!SalesM) & "*")
ORDER BY COMPILE_HIST.Ye ar, MONTH.MonthID, Market.MarketID ;[/CODE]

My trouble is that the query returns (A) no results when it should or (B) all results when it should be filtered. I'm just not sure of how to phrase the sql to look at any and all entered criteria else just return everything.

As always, any help is appreciated - thank you in advance!

martin

[Rabbit]

Have you tried Like "*" & Variable & "*"
Do any of them have null values? Wildcards don't work on nulls.

[puppydogbuddy]

Hello all,

I'm back with another SQL related problem. The details are below, but in short: I am using Access 2003. I have a table whose structure may include four different associate names per record (full structure below). I have a query that should return records that have one or more entered names (as criteria from a form) belonging to them.

TableName=COMPI LE_HIST

CompileID, autonumber, PK
ResultsID, number
Month, number, FK
Year, number
Market, number, FK
ClientID,number, FK
ClientName, text
NAC, text (potential search criteria name)
AE, text (potential search criteria name)
SalesPerson, text (potential search criteria name)
SalesManager, text (potential search criteria name)

QueryName=Expor t Historical Query
[CODE=sql]
SELECT COMPILE_HIST.Co mpileID, COMPILE_HIST.Re sultsID, MONTH.Month, COMPILE_HIST.Ye ar, Market.Market, COMPILE_HIST.Cl ientID, COMPILE_HIST.Cl ientName, COMPILE_HIST.AE , COMPILE_HIST.NA C, COMPILE_HIST.Sa lesPerson, COMPILE_HIST.Sa lesManager
FROM COMPILE_HIST, [MONTH], Market
WHERE (COMPILE_HIST.M onth=MONTH.Mont hID) And (COMPILE_HIST.M arketID=Market. MarketID) AND ((COMPILE_HIST. NAC) Like (Forms![Export Historical Form]!NAC) & "*") AND ((COMPILE_HIST. AE) Like (Forms![Export Historical Form]!AE) & "*") AND ((COMPILE_HIST. SalesPerson) Like (Forms![Export Historical Form]!SalesP) & "*") AND ((COMPILE_HIST. SalesManager) Like (Forms![Export Historical Form]!SalesM) & "*")
ORDER BY COMPILE_HIST.Ye ar, MONTH.MonthID, Market.MarketID ;[/CODE]

My trouble is that the query returns (A) no results when it should or (B) all results when it should be filtered. I'm just not sure of how to phrase the sql to look at any and all entered criteria else just return everything.

As always, any help is appreciated - thank you in advance!

martin

Martin,
The usual problems involve:
1. the improper handling of nulls when nothing is entered in one of the form controls that is used for part of the search criteria.
2. applying the applicable delimiter to each substring because different data types have different delimiters.

The link below uses a step by step methodology bind a button click to build the search string from data entered into unbound controls on a form, This link comes with a downloadable mdb file that will give you a working demo of the technique used to construct the search string, including the different delimiters that are applied to different data types.

http://allenbrowne.com/ser-62.html

More specifically, the Click event procedure for cmdFilter button builds a where clause for an sql select statement by looking at each unbound control on the form. If a control is not null, the data in the control is added as a substring to a string variable named strWhere. " AND " is added to the end of each substring, so that another substring can be added. When the final substring has been added, the trailing " AND " is removed before applying the entire constructed string to the Filter of the form. The tricky part of constructing the string is understanding that different field types require different delimiters, and applying the applicable delimiter to each substring.
.

[martin DH]

Thanks for the link. I can see how having the search code in the form could be useful. I copied my form and altered its code based on the demo. I added a few more fields as well. The user needs to be able to select any combination of the 12 month of the year (I made this option checkboxes), select up to 3 different years (textboxes) and select a market (checkboxes-only 1, 2, or both options). I also set the form to open a report instead of displaying the results in a subdatasheet.

Since I added the months, years, and market fields I am getting some VBA errors. Could you help me out? The error message and the form's code are below:

Run-time error '2465':
Microsoft Office can't find the field '|' referred to in your expression.

On debug it points to the first line of the month search code.

[CODE=vb]
Option Compare Database
Option Explicit

Private Sub OK_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** *************** ************
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** *************** ************
'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Forms![Survey Form]!NAC) Then
strWhere = strWhere & "([COMPILE_HIST.NA C] Like (Forms![Survey Form]!NAC)) AND "
End If

If Not IsNull(Forms![Survey Form]!AE) Then
strWhere = strWhere & "([COMPILE_HIST.AE] Like (Forms![Survey Form]!AE)) AND "
End If

If Not IsNull(Forms![Survey Form]!SalesP) Then
strWhere = strWhere & "([COMPILE_HIST.Sa lesPerson] Like (Forms![Survey Form]!SalesP)) AND "
End If

If Not IsNull(Forms![Survey Form]!SalesM) Then
strWhere = strWhere & "([COMPILE_HIST.Sa lesManager] Like (Forms![Survey Form]!SalesM)) AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Forms![Survey Form]!Year1) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = [Forms![Survey Form]!Year1]) AND "
End If

If Not IsNull(Forms![Survey Form]!Year2) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = [Forms![Survey Form]!Year2]) AND "
End If

If Not IsNull(Forms![Survey Form]!Year3) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = [Forms![Survey Form]!Year3]) AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
If [Forms![Survey Form]!CkJan] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkJan] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkFeb] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkFeb] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkMar] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkMar] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkApr] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkApr] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkMay] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkMay] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkJun] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkJun] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkJul] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkJul] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkAug] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkAug] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkSep] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkSep] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkOct] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkOct] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkNov] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkNov] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!CkDec] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!CkDec] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!SMCkBox] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!SMCkBox] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If [Forms![Survey Form]!MMCkBox] = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf [Forms![Survey Form]!MMCkBox] = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If
'************** *************** *************** *************** ************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** *************** ************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OpenRepor t "Rpt NAC Survey", acViewPreview, , strWhere
DoCmd.close acForm, "Survey Form"
End Sub

Private Sub Cancel_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHe ader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

Private Sub Form_BeforeInse rt(cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(cance l As Integer)
'Remove the single quote from these lines if you want to initially show no records.
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
[/CODE]

Thanks!
martin

[puppydogbuddy]

Martin,
All you have to do is follow the syntax for each data type as shown in the source code for the example (pay attention to the quotes and amperstands). I have redone two of your substrings below, so that you can see the difference.


*************** *******
'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Forms![Survey Form]!NAC) Then
strWhere = strWhere & "([COMPILE_HIST.NA C] Like ""*" & (Forms![Survey Form]!NAC & "*"")) AND "
End If

*************** *******
'Number field example. Do not add the extra quotes.
If Not IsNull(Forms![Survey Form]!Year1) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & [Forms![Survey Form]!Year1] & ") AND "
End If

*************** *******

[martin DH]

Have you tried Like "*" & Variable & "*"
Do any of them have null values? Wildcards don't work on nulls.

I'm trying to work this problem from both angles (VB on the form and sql on the query). Thanks for the note on the wildcards - that takes care of that problem.

Now, can you help me see why the query returns no records when I add in additional search criteria (where user selects any combination of the 12 months in a year via checkboxes and up to three different years via textboxes)? SQL is below - thanks!

[CODE=sql]
SELECT COMPILE_HIST.Co mpileID, COMPILE_HIST.Re sultsID, MONTH.Month, COMPILE_HIST.Ye ar, Market.Market, COMPILE_HIST.Cl ientID, COMPILE_HIST.Cl ientName, COMPILE_HIST.AE , COMPILE_HIST.NA C, COMPILE_HIST.Sa lesPerson, COMPILE_HIST.Sa lesManager
FROM COMPILE_HIST, [MONTH], Market
WHERE (COMPILE_HIST.M onth=MONTH.Mont hID)
AND (((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkJan=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkFeb=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkMar=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkApr=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkMay=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkJun=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkJul=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkAug=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkSep=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkOct=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkNov=-1,1,"")) OR ((MONTH.Month) Like IIf(Forms![Export Historical Form]!CkDec=-1,1,"")))
AND (((COMPILE_HIST .Year) Like (Forms![Export Historical Form]!Year1)) OR ((COMPILE_HIST. Year) Like (Forms![Export Historical Form]!Year2)) OR ((COMPILE_HIST. Year) Like (Forms![Export Historical Form]!Year3)))
AND ((COMPILE_HIST. NAC) Like (Forms![Export Historical Form]!NAC))
AND ((COMPILE_HIST. AE) Like (Forms![Export Historical Form]!AE))
AND ((COMPILE_HIST. SalesPerson) Like (Forms![Export Historical Form]!SalesP))
AND ((COMPILE_HIST. SalesManager) Like (Forms![Export Historical Form]!SalesM));
[/CODE]

[martin DH]

Martin,
All you have to do is follow the syntax for each data type as shown in the source code for the example (pay attention to the quotes and amperstands). I have redone two of your substrings below, so that you can see the difference.


*************** *******
'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Forms![Survey Form]!NAC) Then
strWhere = strWhere & "([COMPILE_HIST.NA C] Like ""*" & (Forms![Survey Form]!NAC & "*"")) AND "
End If

*************** *******
'Number field example. Do not add the extra quotes.
If Not IsNull(Forms![Survey Form]!Year1) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & [Forms![Survey Form]!Year1] & ") AND "
End If

*************** *******

puppydogbuddy,

I get a syntax error when I insert those substrings in place of mine on the form. The NAC substring works as it was actually - when I add the month and year is the problem. Advice? Thanks!
martin

[Rabbit]

I only took a quick glance but this:

[Forms![Survey Form]!CkJan]

Should be this:

[Forms]![Survey Form]![CkJan]

And this applies to every reference to the form you used. Not just this one example.

[Rabbit]

As for the query, instead of:
[Code=sql]
((COMPILE_HIST. AE) Like (Forms![Export Historical Form]!AE) & "*")
[/Code]
Try:
[Code=sql]
((Nz(COMPILE_HI ST.AE, "")) Like "*" & (Forms![Export Historical Form]!AE) & "*")
[/Code]

[puppydogbuddy]

puppydogbuddy,

I get a syntax error when I insert those substrings in place of mine on the form. The NAC substring works as it was actually - when I add the month and year is the problem. Advice? Thanks!
martin

Martin,
Keep in mind that each new substring is added to strWhere, so the syntax has to be consistently followed....you need to follow the sample syntax and let us help you debug. .....substituti ng your format in place of some of the example formats will cause additional problems and confusion. Just because your format does not generate errors at the substring level does not mean that you will get correct results.

In regards to the month and year, check their data type in the table. Are they defined as integer data types or ? The syntax is treating them as if they were integer data types.

[martin DH]

Martin,
In regards to the month and year, check their data type in the table. Are they defined as integer data types or ? The syntax is treating them as if they were integer data types.

Month and Year are both Number datatypes with a "Double" format. Should I go about it with the Number syntax or another syntax in the demo? Thanks for your help!

martin

[martin DH]

I edited my code back to its original form except with my field names, etc - you make a good point here. The form is set to open a report (Quick Report) as described in the demo. The code is below - it doesn't seem to recognize if I select a checkbox for a month or market or enter a year in the Year textbox (it returns all results as if no criteria were entered). So maybe a diiferent syntax is needed for these fields. Thanks and looking forward to your response!

[CODE=vb]
Option Compare Database
Option Explicit

Private Sub OK_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** *************** ************
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** *************** ************
'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.NAC) Then
strWhere = strWhere & "([COMPILE_HIST.NA C] Like ""*" & Me.NAC & "*"") AND "
End If

If Not IsNull(Me.AE) Then
strWhere = strWhere & "([COMPILE_HIST.AE] Like ""*" & Me.AE & "*"") AND "
End If

If Not IsNull(Me.Sales P) Then
strWhere = strWhere & "([COMPILE_HIST.Sa lesPerson] Like ""*" & Me.SalesP & "*"") AND "
End If

If Not IsNull(Me.Sales M) Then
strWhere = strWhere & "([COMPILE_HIST.Sa lesManager] Like ""*" & Me.SalesM & "*"") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Year1 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year1 & ") AND "
End If

If Not IsNull(Me.Year2 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year2 & ") AND "
End If

If Not IsNull(Me.Year3 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year3 & ") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
If Me.CkJan = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkJan = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkFeb = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkFeb = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkMar = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkMar = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkApr = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkApr = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkMay = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkMay = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkJun = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkJun = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkJul = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkJul = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkAug = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkAug = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkSep = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkSep = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkOct = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkOct = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkNov = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkNov = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.CkDec = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) AND "
ElseIf Me.CkDec = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) AND "
End If

If Me.SMCkBox = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = True) AND "
ElseIf Me.SMCkBox = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = False) AND "
End If

If Me.MMCkBox = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = True) AND "
ElseIf Me.MMCkBox = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = False) AND "
End If

'************** *************** *************** *************** ************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** *************** ************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
DoCmd.OpenRepor t "Quick Report", acViewPreview, , strWhere
DoCmd.Close acForm, "Survey Form"
End Sub

Private Sub Cancel_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHe ader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInse rt(cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(cance l As Integer)
'Remove the single quote from these lines if you want to initially show no records.
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
[/CODE]

[puppydogbuddy]

Martin,

Good, the coding looks like the Sample code. Now you have to change or modify the sample syntax for the things that are different than the sample. The first thing you need to do is look at the structure of the code :

The way the code structure is now with the trailing And on each substring: Criteria1 And Criteria2 And Year1 And Year2 And Year3 And Year4 And Year5 And Month1 And Month2.

Should be like this:
Criteria1 And Criteria2 And (Year1 Or Year2 Or Year3 Or Year4 Or Year5) And (Month1 Or Month2 Or Month3......... ......Or Month12)

The other problem is that the data type of year in the table is a double precision number (fractional number to n decimal places) when the year is really an integer (whole number). I assume that the Month is the same.

_______________ ___

So: try and go back to the syntax change the trailing And to a trailing Or for each of the years and do the same with the months. You also need to go back to your table and change the data type for year to integer, ditto for the month,

Then test and post back and I will help you make the necessary corrections. If you have any questions or problems just let me know.

[martin DH]

I really appreciate your help. I'm wondering about the trailing AND/OR syntax. Which option is correct below? Thank you!

Option 1:
[CODE=vb]
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Year1 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year1 & ") OR "
End If

If Not IsNull(Me.Year2 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year2 & ") OR "
End If

If Not IsNull(Me.Year3 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year3 & ") OR "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
If Me.CkJan = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) OR "
ElseIf Me.CkJan = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) OR "
End If

If Me.CkFeb = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) OR "
ElseIf Me.CkFeb = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) OR "
End If [/CODE]

Option 2:
[CODE=vb]
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Year1 ) Then
strWhere = strWhere & "(([COMPILE_HIST.Ye ar] = " & Me.Year1 & ") OR "
End If

If Not IsNull(Me.Year2 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year2 & ") OR "
End If

If Not IsNull(Me.Year3 ) Then
strWhere = strWhere & "([COMPILE_HIST.Ye ar] = " & Me.Year3 & ")) AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
If Me.CkJan = -1 Then
strWhere = strWhere & "(([COMPILE_HIST.Mo nth] = True) OR "
ElseIf Me.CkJan = 0 Then
strWhere = strWhere & "(([COMPILE_HIST.Mo nth] = False) OR "
End If

If Me.CkFeb = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = True) OR "
ElseIf Me.CkFeb = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = False) OR "
End If [/CODE]

[puppydogbuddy]

Martin,
Option 2 is the way....you use "and" between different criteria, such as Year And Month......and you use "Or" within a single criteria that has different variations, such (Year1 Or Year2 Or Year3) And (Month1 Or Month2 Or Month3).

Also, keep in mind that the following line of code would change if the last trailing " And " were a trailing " Or ". The trailing " And " has 5 characters long as opposed to 4 if the last trailer line were to end in an " Or "

'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** ****
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) – 5


Let me know what happens when you run your code.