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=COMPILE_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=Export Historical Query -
SELECT COMPILE_HIST.CompileID, COMPILE_HIST.ResultsID, MONTH.Month, COMPILE_HIST.Year, Market.Market, COMPILE_HIST.ClientID, COMPILE_HIST.ClientName, COMPILE_HIST.AE, COMPILE_HIST.NAC, COMPILE_HIST.SalesPerson, COMPILE_HIST.SalesManager
-
FROM COMPILE_HIST, [MONTH], Market
-
WHERE (COMPILE_HIST.Month=MONTH.MonthID) And (COMPILE_HIST.MarketID=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.Year, MONTH.MonthID, Market.MarketID;
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
49  5928 
Have you tried Like "*" & Variable & "*"
Do any of them have null values? Wildcards don't work on nulls.
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=COMPILE_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=Export Historical Query
-
SELECT COMPILE_HIST.CompileID, COMPILE_HIST.ResultsID, MONTH.Month, COMPILE_HIST.Year, Market.Market, COMPILE_HIST.ClientID, COMPILE_HIST.ClientName, COMPILE_HIST.AE, COMPILE_HIST.NAC, COMPILE_HIST.SalesPerson, COMPILE_HIST.SalesManager
-
FROM COMPILE_HIST, [MONTH], Market
-
WHERE (COMPILE_HIST.Month=MONTH.MonthID) And (COMPILE_HIST.MarketID=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.Year, MONTH.MonthID, Market.MarketID;
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.
.
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. -
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.NAC] 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.SalesPerson] Like (Forms![Survey Form]!SalesP)) AND "
-
End If
-
-
If Not IsNull(Forms![Survey Form]!SalesM) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesManager] 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.Year] = [Forms![Survey Form]!Year1]) AND "
-
End If
-
-
If Not IsNull(Forms![Survey Form]!Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = [Forms![Survey Form]!Year2]) AND "
-
End If
-
-
If Not IsNull(Forms![Survey Form]!Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = [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.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkJan] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkFeb] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkFeb] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkMar] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkMar] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkApr] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkApr] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkMay] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkMay] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkJun] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkJun] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkJul] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkJul] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkAug] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkAug] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkSep] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkSep] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkOct] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkOct] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkNov] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkNov] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!CkDec] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!CkDec] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!SMCkBox] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!SMCkBox] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If [Forms![Survey Form]!MMCkBox] = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf [Forms![Survey Form]!MMCkBox] = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 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.OpenReport "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(acHeader).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_BeforeInsert(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(cancel 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
-
Thanks!
martin
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.NAC] 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.Year] = " & [Forms![Survey Form]!Year1] & ") AND "
End If
**********************
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! -
SELECT COMPILE_HIST.CompileID, COMPILE_HIST.ResultsID, MONTH.Month, COMPILE_HIST.Year, Market.Market, COMPILE_HIST.ClientID, COMPILE_HIST.ClientName, COMPILE_HIST.AE, COMPILE_HIST.NAC, COMPILE_HIST.SalesPerson, COMPILE_HIST.SalesManager
-
FROM COMPILE_HIST, [MONTH], Market
-
WHERE (COMPILE_HIST.Month=MONTH.MonthID)
-
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));
-
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.NAC] 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.Year] = " & [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
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.
As for the query, instead of: -
((COMPILE_HIST.AE) Like (Forms![Export Historical Form]!AE) & "*")
-
Try: -
((Nz(COMPILE_HIST.AE, "")) Like "*" & (Forms![Export Historical Form]!AE) & "*")
-
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. .....substituting 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,
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
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! -
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.NAC] 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.SalesP) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like ""*" & Me.SalesP & "*"") AND "
-
End If
-
-
If Not IsNull(Me.SalesM) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesManager] 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.Year] = " & Me.Year1 & ") AND "
-
End If
-
-
If Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") AND "
-
End If
-
-
If Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & 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.Month] = True) AND "
-
ElseIf Me.CkJan = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkFeb = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkFeb = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkMar = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkMar = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkApr = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkApr = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkMay = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkMay = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkJun = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkJun = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkJul = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkJul = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkAug = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkAug = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkSep = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkSep = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkOct = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkOct = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkNov = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkNov = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkDec = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
-
ElseIf Me.CkDec = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.SMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) AND "
-
ElseIf Me.SMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
-
End If
-
-
If Me.MMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) AND "
-
ElseIf Me.MMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 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.OpenReport "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(acHeader).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_BeforeInsert(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(cancel 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
-
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.
I really appreciate your help. I'm wondering about the trailing AND/OR syntax. Which option is correct below? Thank you! Option 1: -
'Number field example. Do not add the extra quotes.
-
If Not IsNull(Me.Year1) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
-
End If
-
-
If Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
-
End If
-
-
If Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & 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.Month] = True) OR "
-
ElseIf Me.CkJan = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkFeb = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkFeb = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
Option 2: -
'Number field example. Do not add the extra quotes.
-
If Not IsNull(Me.Year1) Then
-
strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
-
End If
-
-
If Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
-
End If
-
-
If Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & 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.Month] = True) OR "
-
ElseIf Me.CkJan = 0 Then
-
strWhere = strWhere & "(([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkFeb = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkFeb = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
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.
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).
Thanks, that's what I thought. I have the code set up and when I run I get the following: Run-time error '3075': Missing ), ], or Item in query expression...
I don't see anything missing...perhaps I'm still not setting up the () correctly; hopefully you can see the mistake. Thanks! -
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.NAC] 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.SalesP) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like ""*" & Me.SalesP & "*"") AND "
-
End If
-
-
If Not IsNull(Me.SalesM) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesManager] 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.Year] = " & Me.Year1 & ") OR "
-
End If
-
-
If Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
-
End If
-
-
If Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & 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.Month] = True) OR "
-
ElseIf Me.CkJan = 0 Then
-
strWhere = strWhere & "(([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkFeb = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkFeb = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkMar = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkMar = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkApr = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkApr = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkMay = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkMay = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkJun = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkJun = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkJul = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkJul = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkAug = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkAug = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkSep = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkSep = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkOct = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkOct = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkNov = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkNov = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
-
End If
-
-
If Me.CkDec = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True)) AND "
-
ElseIf Me.CkDec = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False)) AND "
-
End If
-
-
If Me.SMCkBox = -1 Then
-
strWhere = strWhere & "(([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.SMCkBox = 0 Then
-
strWhere = strWhere & "(([COMPILE_HIST.MarketID] = False) OR "
-
End If
-
-
If Me.MMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True)) AND "
-
ElseIf Me.MMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 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.OpenReport "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(acHeader).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_BeforeInsert(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(cancel 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
Martin,
In order to pinpoint the error we need to narrow down the scope to the line generating the error by doing some debugging. One way you can pinpoint the error line is to go to the code window, select Tools>Options>General Tab> Change error trapping to "Break on All Errors" , click ok and rerun your code. Now when the error occurs, it will take you to the line that has the error. Tell me what line or lines have the error.
Martin,
I took a look at your code and spotted the following mistakes:
1. take out extra left parenthesis ( on these code lines:
38,51,53,123,125
2. take out the extra right parenthesis ) on these lines:
46,117,119,129,131
3. Change the trailing " And " to a trailing " Or " on these lines:
117, 129
I understand you were trying to use the parenthesis to group the Or's into the logical construct that I presented to you for visualization purposes, but we can't do it because the code is structured such that the extra left and right parens are in different code blocks, which creates the error message you were receiving. In order to add the extra left and right parentheses, the two code blocks would have to be consolidated into one code block that has both the left and the right parentheses. At this point, that is not necessary.....as the compiler will execute the code in order from top to bottom.
After making the above changes, try recompiling the code using the "break on all errors" debugging method I discussed in the previous post. Let me know what happened.
Thanks for explaining the parantheses logic - I was wondering if they were needed or not - that's why I love these forums - you can learn so much!
Anyway, I corrected the code exactly as you stated and changed the settings to break on all errors. When I attempt to run the form I receive the same error (3075) and, when clicking "debug," it highlights the following line of code: - DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
I inserted this code (line 150 in the post above) based on the instructions in the demo to return the form's results in a report as opposed to listed on a continuous form. Ideas?
martin
Thanks for explaining the parantheses logic - I was wondering if they were needed or not - that's why I love these forums - you can learn so much!
Anyway, I corrected the code exactly as you stated and changed the settings to break on all errors. When I attempt to run the form I receive the same error (3075) and, when clicking "debug," it highlights the following line of code:
- DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
I inserted this code (line 150 in the post above) based on the instructions in the demo to return the form's results in a report as opposed to listed on a continuous form. Ideas?
martin
Hi Martin,
That line is ok. Set the error trapping back to "Break on Unhandled Errors", then
1. 'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
2. Rerun the code
3. If nothing happens, Hit Ctrl + G
4. copy the entire final sql string that appears at the bottom of your screen and paste it here so I can look at it.
Thanks.
Thanks for explaining the parantheses logic - I was wondering if they were needed or not - that's why I love these forums - you can learn so much!
Anyway, I corrected the code exactly as you stated and changed the settings to break on all errors. When I attempt to run the form I receive the same error (3075) and, when clicking "debug," it highlights the following line of code:
- DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
I inserted this code (line 150 in the post above) based on the instructions in the demo to return the form's results in a report as opposed to listed on a continuous form. Ideas?
martin
Martin,
PM me with your email I can see this thread is well extensive and as such no need to poke my nose in on any intricacies so to speak or put anyone of track, I have a sample demo database here where you might benefit by at least looking at how it handles where clause 'criteria building' on the fly functionally using minimal code. I can send it you via email if you would like as I can see you are flogging your guts out coping with AND this and AND that and AND the other but only if its not null etc etc etc.. You might want to keep it as part of your armoury
Regards
Jim :)
Hi Martin,
That line is ok. Set the error trapping back to "Break on Unhandled Errors", then
1. 'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
2. Rerun the code
3. If nothing happens, Hit Ctrl + G
4. copy the entire final sql string that appears at the bottom of your screen and paste it here so I can look at it.
Thanks.
Here is the sql string that returns: - ([COMPILE_HIST.NAC] Like "*Gylvia Hensler*") AND ([COMPILE_HIST.Year] = 2007) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.MarketID] = True) OR ([COMPILE_HIST.MarketID] = True
Here is the sql string that returns:
- ([COMPILE_HIST.NAC] Like "*Gylvia Hensler*") AND ([COMPILE_HIST.Year] = 2007) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.MarketID] = True) OR ([COMPILE_HIST.MarketID] = True
Martin,
Very interesting….comparing the sql from test to what the final sql needs to look like, we will have to consolidate code blocks after all in order to get the and & ors right. There also appears to be an error in the sample code for the wildcard, and the right parentheses is missing after True in the last line. I will get back to you on this.
Test sql - ([COMPILE_HIST.NAC] LIKE "*Gylvia Hensler*") AND ([COMPILE_HIST.Year] = 2007) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.MarketID] = True) OR ([COMPILE_HIST.MarketID] = True
what Test Sql should have looked like - ([COMPILE_HIST.NAC] LIKE *Gylvia Hensler*) AND ([COMPILE_HIST.Year] = 2007) And ([COMPILE_HIST.Month] = True) And ([COMPILE_HIST.MarketID] = True) OR ([COMPILE_HIST.MarketID] = True)
Martin,
1. To correct the wildcards so that they appear correct in the final sql string, change the syntax for all the wildcards.
From:
Like ""*" & Me.NAC & "*"")
To:
Like " & "*" & Me.NAC & "*" & ")
2. Consolidate the code blocks for the years so that the and/or construct is implemented correctly: -
'Number field example. Do not add the extra quotes.
-
If Not IsNull(Me.Year1) Then
-
strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
-
ElseIf Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
-
ElseIf Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
-
End If
3.Change " Or " to " And " for all code lines containing Month = False
Example:
Change this:
strWhere = strWhere & "(([COMPILE_HIST.Month] = False) OR "
To:
strWhere = strWhere & "(([COMPILE_HIST.Month] = False) And "
4. I think we are getting there, but this is complex so it may take a few more times to get it right.
Like before, compile the code, run it, and dump out the sql string. Except this time, put something in for each criteria so that we can look at the entire string.
Then post the string back here.
Thanks.
You're right - we're getting closer, I can feel it! Made the suggested changes to the code, filled every criteria box, ran the form, same error (3075), here's the sql string: - ([COMPILE_HIST.NAC] Like *A Gentry*) AND ([COMPILE_HIST.AE] Like *A Inger*) AND ([COMPILE_HIST.SalesPerson] Like *A Morris*) AND ([COMPILE_HIST.SalesManager] Like *A Frazier*) AND (([COMPILE_HIST.Year] = 2005) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.Month] = True) OR ([COMPILE_HIST.MarketID] = True) OR ([COMPILE_HIST.MarketID] = True
I noticed - I entered 2005, 2006, and 2007into the three "Year" textboxes but only 2005 (in "Year1") showing up in string. Here's the code we're dealing with again: - 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.NAC] 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.SalesP) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like " & "*" & Me.SalesP & "*" & ") AND "
-
End If
-
-
If Not IsNull(Me.SalesM) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesManager] 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.Year] = " & Me.Year1 & ") OR "
-
ElseIf Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
-
ElseIf Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & 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.Month] = True) OR "
-
ElseIf Me.CkJan = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkFeb = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkFeb = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkMar = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkMar = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkApr = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkApr = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkMay = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkMay = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkJun = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkJun = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkJul = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkJul = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkAug = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkAug = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkSep = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkSep = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkOct = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkOct = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkNov = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkNov = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.CkDec = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
-
ElseIf Me.CkDec = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
-
End If
-
-
If Me.SMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.SMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
-
End If
-
-
If Me.MMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.MMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 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.OpenReport "Quick Report", acViewPreview, , strWhere
-
DoCmd.Close acForm, "Survey Form"
-
End Sub
Thank you!
Martin,
Question on the month syntax:
If Me.CkJan = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
ElseIf Me.CkJan = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
End If
there is nothing in the final string that indicates which month; is the syntax correct? or should it be Month1, Month2, etc as ilustrated below?
If Me.CkJan = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Month1] = True) OR "
ElseIf Me.CkJan = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Month1] = False) AND "
End If
Martin,
Question on the month syntax:
If Me.CkJan = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
ElseIf Me.CkJan = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
End If
there is nothing in the final string that indicates which month; is the syntax correct? or should it be Month1, Month2, etc as ilustrated below?
If Me.CkJan = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Month1] = True) OR "
ElseIf Me.CkJan = 0 Then
strWhere = strWhere & "([COMPILE_HIST.Month1] = False) AND "
End If
The table COMPILE_HIST has a field MONTH filled with a number (1-12) that acts as a foreign key to the table MONTH with PK Month_ID (1-12). Assigned to each id is the name of the month (January = 1, February = 2, etc). Here is where your question comes into play and where I am a little confused myself. I created 12 checkboxes (CkJan, CkFeb, etc) that are unbound on this form. The user could select a box (or multiple boxes) and the code would return any results matching the selected checkboxes - but what value do the checkboxes have to compare with the COMPILE_HIST table? As I said here is where I am now confused as well.
Martin,
How about this?
If Me.CkJan = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Month] = 1) OR "
End If
If Me.CkFeb = -1 Then
strWhere = strWhere & "([COMPILE_HIST.Month] = 2) OR "
End If
and so forth
Martin,
I am not at your computer, so you will have to test. This version reflects:
1. changing of format for month criteria
2. change of Or back to And where>>> Not IsNull check takes care of OR -
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.NAC] 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.SalesP) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like " & "*" & Me.SalesP & "*" & ") AND "
-
End If
-
-
If Not IsNull(Me.SalesM) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesManager] 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.Year] = " & Me.Year1 & ") AND "
-
End If
-
If Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") AND "
-
End If
-
If Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
-
End If
-
-
-
'number field example
-
If Me.CkJan = -1 And Not IsNull(Me.Month(1)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
-
End If
-
-
If Me.CkFeb = -1 And Not IsNull(Me.Month(2)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 2) AND "
-
End If
-
-
If Me.CkMar = -1 And Not IsNull(Me.Month(3)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 3) AND "
-
End If
-
-
If Me.CkApr = -1 And Not IsNull(Me.Month(4)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 4) AND "
-
End If
-
-
If Me.CkMay = -1 And Not IsNull(Me.Month(5)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 5) AND "
-
End If
-
-
If Me.CkJun = -1 And Not IsNull(Me.Month(6)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 6) AND "
-
End If
-
-
If Me.CkJul = -1 And Not IsNull(Me.Month(7)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 7) AND "
-
End If
-
-
If Me.CkAug = -1 And Not IsNull(Me.Month(8)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 8) AND "
-
End If
-
-
If Me.CkSep = -1 And Not IsNull(Me.Month(9)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 9) AND "
-
End If
-
-
If Me.CkOct = -1 And Not IsNull(Me.Month(10)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 10) AND "
-
End If
-
-
If Me.CkNov = -1 And Not IsNull(Me.Month(11)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 11) AND "
-
End If
-
-
If Me.CkDec = -1 And Not IsNull(Me.Month(12)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 12) AND "
-
End If
-
-
If Me.SMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.SMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
-
End If
-
-
If Me.MMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.MMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 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.OpenReport "Quick Report", acViewPreview, , strWhere
-
DoCmd.Close acForm, "Survey Form"
-
End Sub
Thanks. I pasted the revised code into the form and ran with all fields full of data. The result is:
Compile error:
Method or data member not found
-highlighting this part CkJan of the first month string:
'number field example
If Me.CkJan = -1 And Not IsNull(Me.Month(1)) Then
strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
End If
No string appears in the Immediate box when I ctrl+G whether the setting is Break on all errors or Break on unhandled errors. Advice? Thanks!
Ok, I was looking at it a little further. I changed this throughout the month strings: - 'number field example
-
If Me.CkJan = -1 And Not IsNull(Me.Month(1)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
-
End If
TO - 'number field example
-
If Me.CkJan = -1 And Not IsNull(Me.CkJan) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
-
End If
and applied this concept to the Market checkboxes changing: - If Me.SMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.SMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
-
End If
-
-
If Me.MMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.MMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
-
End If
TO - If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 1) AND "
-
End If
-
If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 2) AND "
-
End If
Now when I run I'm back to the 3075 error and the following string appears in the immediate window on debug: - ([COMPILE_HIST.NAC] Like *A Inger*) AND ([COMPILE_HIST.AE] Like *A Gentry*) AND ([COMPILE_HIST.SalesPerson] Like *A Morris*) AND ([COMPILE_HIST.SalesManager] Like *A Frazier*) AND (([COMPILE_HIST.Year] = 2005) AND ([COMPILE_HIST.Year] = 2006) AND ([COMPILE_HIST.Year] = 2007)) AND ([COMPILE_HIST.Month] = 1) AND ([COMPILE_HIST.Month] = 2) AND ([COMPILE_HIST.Month] = 3) AND ([COMPILE_HIST.Month] = 4) AND ([COMPILE_HIST.Month] = 5) AND ([COMPILE_HIST.Month] = 6) AND ([COMPILE_HIST.Month] = 7) AND ([COMPILE_HIST.Month] = 8) AND ([COMPILE_HIST.Month] = 9) AND ([COMPILE_HIST.Month] = 10) AND ([COMPILE_HIST.Month] = 11) AND ([COMPILE_HIST.Month] = 12) AND ([COMPILE_HIST.MarketID] = 1) AND ([COMPILE_HIST.MarketID] = 2)
Let me know what you think (if I'm thinking in the right direction).
martin
Try it this way:
If Me.CkJan = -1 And Not IsNull(Me.Month) Then
strWhere = strWhere & "([COMPILE_HIST.Month] = " & 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.
Try it this way:
If Me.CkJan = -1 And Not IsNull(Me.Month) Then
strWhere = strWhere & "([COMPILE_HIST.Month] = " & 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.
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: -
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(strCriteria, Len(strCriteria) - 4) & ")"
-
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.
Ok, I was looking at it a little further. I changed this throughout the month strings:
- 'number field example
-
If Me.CkJan = -1 And Not IsNull(Me.Month(1)) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
-
End If
TO
- 'number field example
-
If Me.CkJan = -1 And Not IsNull(Me.CkJan) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
-
End If
and applied this concept to the Market checkboxes changing:
- If Me.SMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.SMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
-
End If
-
-
If Me.MMCkBox = -1 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
-
ElseIf Me.MMCkBox = 0 Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
-
End If
TO
- If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 1) AND "
-
End If
-
If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 2) AND "
-
End If
Now when I run I'm back to the 3075 error and the following string appears in the immediate window on debug:
- ([COMPILE_HIST.NAC] Like *A Inger*) AND ([COMPILE_HIST.AE] Like *A Gentry*) AND ([COMPILE_HIST.SalesPerson] Like *A Morris*) AND ([COMPILE_HIST.SalesManager] Like *A Frazier*) AND (([COMPILE_HIST.Year] = 2005) AND ([COMPILE_HIST.Year] = 2006) AND ([COMPILE_HIST.Year] = 2007)) AND ([COMPILE_HIST.Month] = 1) AND ([COMPILE_HIST.Month] = 2) AND ([COMPILE_HIST.Month] = 3) AND ([COMPILE_HIST.Month] = 4) AND ([COMPILE_HIST.Month] = 5) AND ([COMPILE_HIST.Month] = 6) AND ([COMPILE_HIST.Month] = 7) AND ([COMPILE_HIST.Month] = 8) AND ([COMPILE_HIST.Month] = 9) AND ([COMPILE_HIST.Month] = 10) AND ([COMPILE_HIST.Month] = 11) AND ([COMPILE_HIST.Month] = 12) AND ([COMPILE_HIST.MarketID] = 1) AND ([COMPILE_HIST.MarketID] = 2)
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]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.
Ok now how do you do it in a Data Access Page? Using multiple criteria to filter 1 record set.
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: - (([COMPILE_HIST.Year] = 2006) AND ([COMPILE_HIST.Year] = 2007)
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: - 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.NAC] 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.SalesP) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like ""*" & Me.SalesP & "*"") AND "
-
End If
-
-
If Not IsNull(Me.SalesM) Then
-
strWhere = strWhere & "([COMPILE_HIST.SalesManager] 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.Year] = " & Me.Year1 & ") AND "
-
End If
-
If Not IsNull(Me.Year2) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") AND "
-
End If
-
If Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "([COMPILE_HIST.Year] = " & 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.Month] = 1) AND "
-
End If
-
-
If Me.CkFeb = -1 And Not IsNull(Me.CkFeb) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 2) AND "
-
End If
-
-
If Me.CkMar = -1 And Not IsNull(Me.CkMar) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 3) AND "
-
End If
-
-
If Me.CkApr = -1 And Not IsNull(Me.CkApr) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 4) AND "
-
End If
-
-
If Me.CkMay = -1 And Not IsNull(Me.CkMay) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 5) AND "
-
End If
-
-
If Me.CkJun = -1 And Not IsNull(Me.CkJun) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 6) AND "
-
End If
-
-
If Me.CkJul = -1 And Not IsNull(Me.CkJul) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 7) AND "
-
End If
-
-
If Me.CkAug = -1 And Not IsNull(Me.CkAug) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 8) AND "
-
End If
-
-
If Me.CkSep = -1 And Not IsNull(Me.CkSep) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 9) AND "
-
End If
-
-
If Me.CkOct = -1 And Not IsNull(Me.CkOct) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 10) AND "
-
End If
-
-
If Me.CkNov = -1 And Not IsNull(Me.CkNov) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 11) AND "
-
End If
-
-
If Me.CkDec = -1 And Not IsNull(Me.CkDec) Then
-
strWhere = strWhere & "([COMPILE_HIST.Month] = 12) AND "
-
End If
-
-
If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 1) AND "
-
End If
-
If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
-
strWhere = strWhere & "([COMPILE_HIST.MarketID] = 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.OpenReport "Quick Report", acViewPreview, , strWhere
-
DoCmd.Close acForm, "Survey Form"
-
End Sub
Replace the appropriate block of code with the following: -
If Not IsNull(Me.Year1) Or Not IsNull(Me.Year2) Or Not IsNull(Me.Year3) Then
-
strWhere = strWhere & "[COMPILE_HIST.Year] 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.Month] 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.MarketID] IN ("
-
-
If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
-
strWhere = strWhere & "1,"
-
End If
-
-
If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
-
strWhere = strWhere & "2"
-
Else
-
strWhere = Left(strWhere, Len(strWhere) - 1)
-
End If
-
-
strWhere = strWhere & ") AND "
-
End If
-
I did not test the code.
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.
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: - (([COMPILE_HIST.Year] = 2006) OR ([COMPILE_HIST.Year] = 2007
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 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:
- (([COMPILE_HIST.Year] = 2006) OR ([COMPILE_HIST.Year] = 2007
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
strReportName = "NAC Report" ElseIf Not IsNull(Me.AE) Then strReportName = "AE Report" ElseMsgBox "no NAC or AE criteria selected." End If -
-
DoCmd.OpenReport strReportName, acViewPreview, , strWhere
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
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"
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.
I'm sorry, I wasn't too clear. In the code for my form, I have it set to open the results of the search in a report (see code below): - DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
My question is: in place of this code, is there some other code that will simply display the results in a table - so that I could export these results to Excel, for example.
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"
Where do I apply these changes? To the code on my form? To the textbox on the report? Thanks for your help.
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:
- (([COMPILE_HIST.Year] = 2006) OR ([COMPILE_HIST.Year] = 2007
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
Not a problem, good luck.
I'm sorry, I wasn't too clear. In the code for my form, I have it set to open the results of the search in a report (see code below):
- DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
My question is: in place of this code, is there some other code that will simply display the results in a table - so that I could export these results to Excel, for example.
Where do I apply these changes? To the code on my form? To the textbox on the report? Thanks for your help.
Martin.
1. Re your question to display the output in table format, just open the query that is the source for the report, instead of opening the report itself.
DoCmd.OpenQuery "YourQueryName" , acViewNormal
2. On a second thought, if you want to change the date format everywhere, I would just go to your table, place it in design view. Go to the format property for the datefields in your table type mmmm/dd/yyyy, which would result in 06/19/2007 looking like his>>June 19, 2007. If you want the month abbreviated set the format in the table as mmm/dd/yyyy.. If it is in table, I believe it will display that way on the reports and forms, unless a format property is specified in the property sheet for the form.
Martin.
1. Re your question to display the output in table format, just open the query that is the source for the report, instead of opening the report itself.
DoCmd.OpenQuery "YourQueryName" , acViewNormal
There is no actual query for the report. It's control source is the table COMPILE_HIST and the it's "query" is the WHERE string created by the form above.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: TH |
last post by:
I am (still :) working on a recipe database. Now I am trying to figure out
how to set it up for an ingredient search. What I want it to be able to do
is three things:
1. Search based on just...
|
by: TH |
last post by:
I am (still :) working on a recipe database. Now I am trying to figure out
how to set it up for an ingredient search. What I want it to be able to do
is to search by one ingredient, sometimes by...
|
by: N. Graves |
last post by:
Hi,
I want to have a Search Dialog box that has several text box and
fields to build a search and display the results in a form. I can do
everything that I need to if I us a report but I would...
|
by: Gobi |
last post by:
Hello, I have a Database with lists of Clients in each.
Every year a new tables is created with the naming convention
"CloseYear"
ie close1999, close2000
There are tables from this year back to...
|
by: Don Pagel |
last post by:
I have a number of memo fields in my table that I want to search for
words in. How do I right a routine to search for all records with the
search variable word(s) in multiple fields?
|
by: angie |
last post by:
I need to figure out how to create a user interface to search a query,
but here's the bad part...I need to account for criteria on at least 7
of the fields. Here's what I'm thinking I need to do:...
|
by: shantanu |
last post by:
Hi All,
I have a requirement to develop a search engine based on
some search criteria that will search for the string or statement in
all the documents uploaded in the website. The search result...
|
by: kaosx5 |
last post by:
I am running Access 2003. I would like to be able to search the criteria section in an Access query to see if it populated with anything. I have a report database that has a list box and some...
|
by: Robertf987 |
last post by:
Hi,
Yet another thing I need help with I'm affraid. I'll first explain what I want, then I'll try to explain what I have. I'm using Microsoft Access 2000.
What I want is to be able to do a...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
