Search Query with multiple criteria

[martin DH]

Ok, I was looking at it a little further. I changed this throughout the month strings:

[CODE=vb] 'number field example
If Me.CkJan = -1 And Not IsNull(Me.Month (1)) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 1) AND "
End If[/CODE]
TO
[CODE=vb] 'number field example
If Me.CkJan = -1 And Not IsNull(Me.CkJan ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 1) AND "
End If[/CODE]

and applied this concept to the Market checkboxes changing:

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

If Me.MMCkBox = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = True) OR "
ElseIf Me.MMCkBox = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = False) AND "
End If[/CODE]
TO
[CODE=vb] If Me.SMCkBox = -1 And Not IsNull(Me.SMCkB ox) Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = 1) AND "
End If
If Me.MMCkBox = -1 And Not IsNull(Me.MMCkB ox) Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = 2) AND "
End If[/CODE]

Now when I run I'm back to the 3075 error and the following string appears in the immediate window on debug:
[CODE=sql]([COMPILE_HIST.NA C] Like *A Inger*) AND ([COMPILE_HIST.AE] Like *A Gentry*) AND ([COMPILE_HIST.Sa lesPerson] Like *A Morris*) AND ([COMPILE_HIST.Sa lesManager] Like *A Frazier*) AND (([COMPILE_HIST.Ye ar] = 2005) AND ([COMPILE_HIST.Ye ar] = 2006) AND ([COMPILE_HIST.Ye ar] = 2007)) AND ([COMPILE_HIST.Mo nth] = 1) AND ([COMPILE_HIST.Mo nth] = 2) AND ([COMPILE_HIST.Mo nth] = 3) AND ([COMPILE_HIST.Mo nth] = 4) AND ([COMPILE_HIST.Mo nth] = 5) AND ([COMPILE_HIST.Mo nth] = 6) AND ([COMPILE_HIST.Mo nth] = 7) AND ([COMPILE_HIST.Mo nth] = 8) AND ([COMPILE_HIST.Mo nth] = 9) AND ([COMPILE_HIST.Mo nth] = 10) AND ([COMPILE_HIST.Mo nth] = 11) AND ([COMPILE_HIST.Mo nth] = 12) AND ([COMPILE_HIST.Ma rketID] = 1) AND ([COMPILE_HIST.Ma rketID] = 2)[/CODE]

Let me know what you think (if I'm thinking in the right direction).
martin

[puppydogbuddy]

Try it this way:
If Me.CkJan = -1 And Not IsNull(Me.Month ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = " & 1) & " AND "
End If

Questions:
tell me a little more how the month works. You place a checkmark in January...do you set some variable value = 1 or how does it work?

Did you change the data type on month to integer?

Let me know.

[martin DH]

Try it this way:
If Me.CkJan = -1 And Not IsNull(Me.Month ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = " & 1) & " AND "
End If

Questions:
tell me a little more how the month works. You place a checkmark in January...do you set some variable value = 1 or how does it work?

Did you change the data type on month to integer?

Let me know.

Yes, I did change the data type to integer for month, year, and market.

I have a checkbox for each month and the user could select any combination of months by checking the checkboxes. I want the result of these selected parameters to act a as filter pulling all matching data into a prebuilt report. I've attached, hopefully, a pdf of what the form currently looks like - this may help explain what I am trying to accomplish.

[Rabbit]

I took a cursory glance at the posts and am jumping into the fray without all the knowledge but...

tbl_Month
MonthID; Number (1-12); PK
MonthName; Text

tbl_Data
DataID; Number; PK
MonthID; Number (1-12); FK
Field1; Text

frm_Search
chk_Jan
chk_Feb
...
chk_Dec
but_Search

Now, if I wanted to return all records from tbl_Data that matches any of the months I checked, I would use:
[Code=vb]
Dim strCriteria As String
strCriteria = "("

If chk_Jan = True Then
strCriteria = strCriteria & "(MonthID = 1) OR "
End If

If chk_Feb = True Then
strCriteria = strCriteria & "(MonthID = 2) OR "
End If

...

If chk_Dec = True Then
strCriteria = strCriteria & "(MonthID = 12) OR "
End If

If Len(strCriteria ) = 1 Then
MsgBox "No Months Checked."
Exit Sub
End If

strCriteria = Left(strCriteri a, Len(strCriteria ) - 4) & ")"
[/Code]

[puppydogbuddy]

Martin,
Sorry I could not get back to you earlier, but I just got back on-line after my computer crashed on Friday afternoon. I will get back to you guys tomorrow after I have had a chance to look at everything.

[puppydogbuddy]

Ok, I was looking at it a little further. I changed this throughout the month strings:

[CODE=vb] 'number field example
If Me.CkJan = -1 And Not IsNull(Me.Month (1)) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 1) AND "
End If[/CODE]
TO
[CODE=vb] 'number field example
If Me.CkJan = -1 And Not IsNull(Me.CkJan ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 1) AND "
End If[/CODE]

and applied this concept to the Market checkboxes changing:

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

If Me.MMCkBox = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = True) OR "
ElseIf Me.MMCkBox = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = False) AND "
End If[/CODE]
TO
[CODE=vb] If Me.SMCkBox = -1 And Not IsNull(Me.SMCkB ox) Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = 1) AND "
End If
If Me.MMCkBox = -1 And Not IsNull(Me.MMCkB ox) Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = 2) AND "
End If[/CODE]

Now when I run I'm back to the 3075 error and the following string appears in the immediate window on debug:
[CODE=sql]([COMPILE_HIST.NA C] Like *A Inger*) AND ([COMPILE_HIST.AE] Like *A Gentry*) AND ([COMPILE_HIST.Sa lesPerson] Like *A Morris*) AND ([COMPILE_HIST.Sa lesManager] Like *A Frazier*) AND (([COMPILE_HIST.Ye ar] = 2005) AND ([COMPILE_HIST.Ye ar] = 2006) AND ([COMPILE_HIST.Ye ar] = 2007)) AND ([COMPILE_HIST.Mo nth] = 1) AND ([COMPILE_HIST.Mo nth] = 2) AND ([COMPILE_HIST.Mo nth] = 3) AND ([COMPILE_HIST.Mo nth] = 4) AND ([COMPILE_HIST.Mo nth] = 5) AND ([COMPILE_HIST.Mo nth] = 6) AND ([COMPILE_HIST.Mo nth] = 7) AND ([COMPILE_HIST.Mo nth] = 8) AND ([COMPILE_HIST.Mo nth] = 9) AND ([COMPILE_HIST.Mo nth] = 10) AND ([COMPILE_HIST.Mo nth] = 11) AND ([COMPILE_HIST.Mo nth] = 12) AND ([COMPILE_HIST.Ma rketID] = 1) AND ([COMPILE_HIST.Ma rketID] = 2)[/CODE]

Let me know what you think (if I'm thinking in the right direction).
martin

Martin,
We will know soon. I very quickly researched the 3075 error and found out it is because I changed allen browne's syntax for the wildcard on the like statement because it did not look right. It turns out I was wrong, because the extra quote that Allen had was designed to replace the apostrophe in names like O'Brien.

http://support.microsoft.com/default.aspx/kb/178070

To correct the wildcards so that they are like Allen Browne's example, change the syntax for all the wildcards.
From:
Like " & "*" & Me.NAC & "*" & ")
To:
Like ""*" & Me.NAC & "*"")

After you fix the above, recompile, test and post back any errors plus the final sql string like you have been doing. You should also run a test with Rabbits suggested syntax and post the results back as well.

[puppydogbuddy]

=puppydogbuddy]Martin, I just noticed that my previous post did not come out. Here is the text of that post in its entirety.

We will know soon. I very quickly researched the 3075 error and found out it is because I changed allen browne's syntax for the wildcard on the like statement because it did not look right. It turns out I was wrong, because the extra quote that Allen had was designed to replace the apostrophe in names like O'Brien.

http://support.microsoft.com/default.aspx/kb/178070

To correct the wildcards so that they are like Allen Browne's example, change the syntax for all the wildcards.
From:
Like " & "*" & Me.NAC & "*" & ")
To:
Like ""*" & Me.NAC & "*"")

After you fix the above, recompile, test and post back any errors plus the final sql string like you have been doing. You should also run a test with Rabbits suggested syntax and post the results back as well.

[kcddoorman]

Ok now how do you do it in a Data Access Page? Using multiple criteria to filter 1 record set.

[martin DH]

Okay, I'm afraid I'm really confused by the results of this so here goes.

I tried running with Rabbit's code and received another error. With Puppydogbuddy's suggestion, the form's code is below. When I attempt to run...
1) If all three Year fields contain criteria, the report opens but with no data and "#Error"

2) If only one or two of the three Year fields contain criteria, I receive the same error we have been getting (3075) with this SQL in the Immediate window:
[CODE=sql](([COMPILE_HIST.Ye ar] = 2006) AND ([COMPILE_HIST.Ye ar] = 2007)[/CODE] I had entered 2006, 2007 as the only criteria.

3) If any other field holds criteria (eg 1 month or 2 months or all 12 months or NAC), the report opens but with no data and "#Error" (as in point 1 above)

4) If no fields contain criteria EXCEPT for either one or both Market checkboxes (SMCkBox and MMCkBox), the report opens accurately with data matching which checkbox was checked.

I'm looking forward to some answers here! Thanks so much.

Form Code we're dealing with:
[CODE=vb]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


'number field example
Dim strCriteria As String
strCriteria = "("

If Me.CkJan = -1 And Not IsNull(Me.CkJan ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 1) AND "
End If

If Me.CkFeb = -1 And Not IsNull(Me.CkFeb ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 2) AND "
End If

If Me.CkMar = -1 And Not IsNull(Me.CkMar ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 3) AND "
End If

If Me.CkApr = -1 And Not IsNull(Me.CkApr ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 4) AND "
End If

If Me.CkMay = -1 And Not IsNull(Me.CkMay ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 5) AND "
End If

If Me.CkJun = -1 And Not IsNull(Me.CkJun ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 6) AND "
End If

If Me.CkJul = -1 And Not IsNull(Me.CkJul ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 7) AND "
End If

If Me.CkAug = -1 And Not IsNull(Me.CkAug ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 8) AND "
End If

If Me.CkSep = -1 And Not IsNull(Me.CkSep ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 9) AND "
End If

If Me.CkOct = -1 And Not IsNull(Me.CkOct ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 10) AND "
End If

If Me.CkNov = -1 And Not IsNull(Me.CkNov ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 11) AND "
End If

If Me.CkDec = -1 And Not IsNull(Me.CkDec ) Then
strWhere = strWhere & "([COMPILE_HIST.Mo nth] = 12) AND "
End If

If Me.SMCkBox = -1 And Not IsNull(Me.SMCkB ox) Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = 1) AND "
End If
If Me.MMCkBox = -1 And Not IsNull(Me.MMCkB ox) Then
strWhere = strWhere & "([COMPILE_HIST.Ma rketID] = 2) 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[/CODE]

[Rabbit]

Replace the appropriate block of code with the following:
[Code=vb]
If Not IsNull(Me.Year1 ) Or Not IsNull(Me.Year2 ) Or Not IsNull(Me.Year3 ) Then
strWhere = strWhere & "[COMPILE_HIST.Ye ar] IN ("

If Not IsNull(Me.Year1 ) Then
strWhere = strWhere & Me.Year1 & ","
End If

If Not IsNull(Me.Year2 ) Then
strWhere = strWhere & Me.Year2 & ","
End If

If Not IsNull(Me.Year3 ) Then
strWhere = strWhere & Me.Year3
Else
strWhere = Left(strWhere, Len(strWhere) - 1)
End If

strWhere = strWhere & ") AND "
End If

If Me.CkJan = -1 Or Me.CkFeb = -1 Or Me.CkMar = -1 Or Me.CkApr = -1 Or Me.CkMay = -1 Or Me.CkJun = -1 Or Me.CkJul = -1 Or Me.CkAug = -1 Or Me.CkSep = -1 Or Me.CkOct = -1 Or Me.CkNov = -1 Or Me.CkDec = -1 Then
strWhere = strWhere & "[COMPILE_HIST.Mo nth] IN ("

If Me.CkJan = -1 And Not IsNull(Me.CkJan ) Then
strWhere = strWhere & "1,"
End If

If Me.CkFeb = -1 And Not IsNull(Me.CkFeb ) Then
strWhere = strWhere & "2,"
End If

If Me.CkMar = -1 And Not IsNull(Me.CkMar ) Then
strWhere = strWhere & "3,"
End If

If Me.CkApr = -1 And Not IsNull(Me.CkApr ) Then
strWhere = strWhere & "4,"
End If

If Me.CkMay = -1 And Not IsNull(Me.CkMay ) Then
strWhere = strWhere & "5,"
End If

If Me.CkJun = -1 And Not IsNull(Me.CkJun ) Then
strWhere = strWhere & "6,"
End If

If Me.CkJul = -1 And Not IsNull(Me.CkJul ) Then
strWhere = strWhere & "7,"
End If

If Me.CkAug = -1 And Not IsNull(Me.CkAug ) Then
strWhere = strWhere & "8,"
End If

If Me.CkSep = -1 And Not IsNull(Me.CkSep ) Then
strWhere = strWhere & "9,"
End If

If Me.CkOct = -1 And Not IsNull(Me.CkOct ) Then
strWhere = strWhere & "10,"
End If

If Me.CkNov = -1 And Not IsNull(Me.CkNov ) Then
strWhere = strWhere & "11,"
End If

If Me.CkDec = -1 And Not IsNull(Me.CkDec ) Then
strWhere = strWhere & "12"
Else
strWhere = Left(strWhere, Len(strWhere) - 1)
End If

strWhere = strWhere & ") AND "
End If

If Me.SMCkBox = -1 Or Me.MMCkBox = -1 Then
strWhere = strWhere & "[COMPILE_HIST.Ma rketID] IN ("

If Me.SMCkBox = -1 And Not IsNull(Me.SMCkB ox) Then
strWhere = strWhere & "1,"
End If

If Me.MMCkBox = -1 And Not IsNull(Me.MMCkB ox) Then
strWhere = strWhere & "2"
Else
strWhere = Left(strWhere, Len(strWhere) - 1)
End If

strWhere = strWhere & ") AND "
End If
[/Code]

I did not test the code.

[puppydogbuddy]

Martin,
Thanks for your patience. Could you change the trailing And to a trailing Or just for the year criteria string, recompile your code, and run the test for the years 2006 and 2007 just like you did before. Let me know if you get year data after you made that change and just post back the final sql string, not the code .

Thanks.

[martin DH]

Puppydogbuddy and Rabbit:

Thank you very much for your help! I now have a working and incredibly useful search tool.
Per your last post, puppydogbuddy, changing the trailing AND to OR for the Year strings results in the same error as before with this sql string:
[CODE=sql](([COMPILE_HIST.Ye ar] = 2006) OR ([COMPILE_HIST.Ye ar] = 2007[/CODE]
WIth the addition of Rabbit's post (utilizing the IN syntax), the form (Survey Form) searches through the data table (COMPILE_HIST) for records matching any and all criteria entered, then presents that information on a report (Quick Report).
Thanks again for your help on this extensive post!

One last question: If I decide to add additional, slightly different reports (so I have NAC Report and AE Report), is it possible to change which report is opened by the form. I'm thinking that if the NAC field contains criteria, NAC Report opens, and if the AE field contains criteria, AE Report opens (each report would have some differences in the formating). Ideas?

martin

[puppydogbuddy]

Puppydogbuddy and Rabbit:

Thank you very much for your help! I now have a working and incredibly useful search tool.
Per your last post, puppydogbuddy, changing the trailing AND to OR for the Year strings results in the same error as before with this sql string:
[CODE=sql](([COMPILE_HIST.Ye ar] = 2006) OR ([COMPILE_HIST.Ye ar] = 2007[/CODE]
WIth the addition of Rabbit's post (utilizing the IN syntax), the form (Survey Form) searches through the data table (COMPILE_HIST) for records matching any and all criteria entered, then presents that information on a report (Quick Report).
Thanks again for your help on this extensive post!

One last question: If I decide to add additional, slightly different reports (so I have NAC Report and AE Report), is it possible to change which report is opened by the form. I'm thinking that if the NAC field contains criteria, NAC Report opens, and if the AE field contains criteria, AE Report opens (each report would have some differences in the formating). Ideas?

martin

Glad Rabbit's code worked. Thank you Rabbit!! I was just getting to the point where I was going to tell you to email a copy of your mdb to us if you wanted to get it working because we need in front of us to see what was happening. It would be nice if you posted the code for the final solution in its entirety.

As for your report, replace the open report statement with the following for a quick fix:

Dim strReportName As string

If Not IsNull(Me.NAC) Then[INDENT]strReportName = "NAC Report"[/INDENT]
ElseIf Not IsNull(Me.AE) Then [INDENT]strReportName = "AE Report"[/INDENT]
Else[INDENT]MsgBox "no NAC or AE criteria selected."[/INDENT]
End If

DoCmd.OpenReport strReportName, acViewPreview, , strWhere

[martin DH]

Yes, I'll gladly post the code in its entirety. Another question though, concerning the reports - as an alternative to printing to a report, I might need to simply display the results in a table (exactly as how the results of a simple SELECT query appear in Access). For this I would...? Please and thank you!

Also, if using the reports for the results, I'm not sure of how I would display a month's name (eg January) in place of the corresponsing number locating in the form code and the COMPILE_HIST table (eg 1). Ideas here? Thanks.

martin

[puppydogbuddy]

Yes, I'll gladly post the code in its entirety. Another question though, concerning the reports - as an alternative to printing to a report, I might need to simply display the results in a table (exactly as how the results of a simple SELECT query appear in Access). For this I would...? Please and thank you!

Also, if using the reports for the results, I'm not sure of how I would display a month's name (eg January) in place of the corresponsing number locating in the form code and the COMPILE_HIST table (eg 1). Ideas here? Thanks.

martin

To print the query output as is, just run your query and then do either one of the following:
1. click the printer icon to print as is
2. click the print preview icon to bring up the print dialog box and modify some of the print settings before it goes to the printer.


To get the month name for the month, you can any of the following:
1. Format(Date, "mmmm")
2. MonthName(month #[, abbreviate]) where month# is the number of the month (Month# for January = 1) abbreviate is optional....set to true if you want to abbreviate the month name.

example MonthName(12, True) >>>>>Dec

3. MonthName(month ("datestring ")) where datestring is a valid date string like "12/22/2007"