By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Search Query with multiple criteria

100+
P: 114
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
Expand|Select|Wrap|Line Numbers
  1. CompileID, autonumber, PK
  2. ResultsID, number
  3. Month, number, FK
  4. Year, number
  5. Market, number, FK
  6. ClientID,number, FK
  7. ClientName, text
  8. NAC, text (potential search criteria name)
  9. AE, text (potential search criteria name)
  10. SalesPerson, text (potential search criteria name)
  11. SalesManager, text (potential search criteria name)
QueryName=Export Historical Query
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM COMPILE_HIST, [MONTH], Market
  3. 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) & "*")
  4. 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
Oct 15 '07 #1
Share this Question
Share on Google+
49 Replies


Rabbit
Expert Mod 10K+
P: 12,334
Have you tried Like "*" & Variable & "*"
Do any of them have null values? Wildcards don't work on nulls.
Oct 15 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
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
Expand|Select|Wrap|Line Numbers
  1. CompileID, autonumber, PK
  2. ResultsID, number
  3. Month, number, FK
  4. Year, number
  5. Market, number, FK
  6. ClientID,number, FK
  7. ClientName, text
  8. NAC, text (potential search criteria name)
  9. AE, text (potential search criteria name)
  10. SalesPerson, text (potential search criteria name)
  11. SalesManager, text (potential search criteria name)
QueryName=Export Historical Query
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM COMPILE_HIST, [MONTH], Market
  3. 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) & "*")
  4. 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.
.
Oct 15 '07 #3

100+
P: 114
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:

Expand|Select|Wrap|Line Numbers
  1. Run-time error '2465':
  2. Microsoft Office can't find the field '|' referred to in your expression.
  3.  
On debug it points to the first line of the month search code.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub OK_Click()
  5.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  6.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  7.                         we remove the trailing " AND " at the end.
  8.     '           2. The date range works like this: _
  9.                         Both dates      = only dates between (both inclusive. _
  10.                         Start date only = all dates from this one onwards; _
  11.                         End date only   = all dates up to (and including this one).
  12.     Dim strWhere As String                  'The criteria string.
  13.     Dim lngLen As Long                      'Length of the criteria string to append to.
  14.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  15.  
  16.     '***********************************************************************
  17.     'Look at each search box, and build up the criteria string from the non-blank ones.
  18.     '***********************************************************************
  19.     'Another text field example. Use Like to find anywhere in the field.
  20.     If Not IsNull(Forms![Survey Form]!NAC) Then
  21.         strWhere = strWhere & "([COMPILE_HIST.NAC] Like (Forms![Survey Form]!NAC)) AND "
  22.     End If
  23.  
  24.      If Not IsNull(Forms![Survey Form]!AE) Then
  25.         strWhere = strWhere & "([COMPILE_HIST.AE] Like (Forms![Survey Form]!AE)) AND "
  26.     End If
  27.  
  28.      If Not IsNull(Forms![Survey Form]!SalesP) Then
  29.         strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like (Forms![Survey Form]!SalesP)) AND "
  30.     End If
  31.  
  32.      If Not IsNull(Forms![Survey Form]!SalesM) Then
  33.         strWhere = strWhere & "([COMPILE_HIST.SalesManager] Like (Forms![Survey Form]!SalesM)) AND "
  34.     End If
  35.  
  36.         'Number field example. Do not add the extra quotes.
  37.     If Not IsNull(Forms![Survey Form]!Year1) Then
  38.         strWhere = strWhere & "([COMPILE_HIST.Year] = [Forms![Survey Form]!Year1]) AND "
  39.     End If
  40.  
  41.      If Not IsNull(Forms![Survey Form]!Year2) Then
  42.         strWhere = strWhere & "([COMPILE_HIST.Year] = [Forms![Survey Form]!Year2]) AND "
  43.     End If
  44.  
  45.      If Not IsNull(Forms![Survey Form]!Year3) Then
  46.         strWhere = strWhere & "([COMPILE_HIST.Year] = [Forms![Survey Form]!Year3]) AND "
  47.     End If
  48.  
  49.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  50.     If [Forms![Survey Form]!CkJan] = -1 Then
  51.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  52.     ElseIf [Forms![Survey Form]!CkJan] = 0 Then
  53.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  54.     End If
  55.  
  56.         If [Forms![Survey Form]!CkFeb] = -1 Then
  57.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  58.     ElseIf [Forms![Survey Form]!CkFeb] = 0 Then
  59.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  60.     End If
  61.  
  62.         If [Forms![Survey Form]!CkMar] = -1 Then
  63.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  64.     ElseIf [Forms![Survey Form]!CkMar] = 0 Then
  65.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  66.     End If
  67.  
  68.         If [Forms![Survey Form]!CkApr] = -1 Then
  69.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  70.     ElseIf [Forms![Survey Form]!CkApr] = 0 Then
  71.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  72.     End If
  73.  
  74.         If [Forms![Survey Form]!CkMay] = -1 Then
  75.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  76.     ElseIf [Forms![Survey Form]!CkMay] = 0 Then
  77.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  78.     End If
  79.  
  80.         If [Forms![Survey Form]!CkJun] = -1 Then
  81.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  82.     ElseIf [Forms![Survey Form]!CkJun] = 0 Then
  83.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  84.     End If
  85.  
  86.         If [Forms![Survey Form]!CkJul] = -1 Then
  87.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  88.     ElseIf [Forms![Survey Form]!CkJul] = 0 Then
  89.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  90.     End If
  91.  
  92.         If [Forms![Survey Form]!CkAug] = -1 Then
  93.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  94.     ElseIf [Forms![Survey Form]!CkAug] = 0 Then
  95.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  96.     End If
  97.  
  98.         If [Forms![Survey Form]!CkSep] = -1 Then
  99.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  100.     ElseIf [Forms![Survey Form]!CkSep] = 0 Then
  101.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  102.     End If
  103.  
  104.         If [Forms![Survey Form]!CkOct] = -1 Then
  105.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  106.     ElseIf [Forms![Survey Form]!CkOct] = 0 Then
  107.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  108.     End If
  109.  
  110.         If [Forms![Survey Form]!CkNov] = -1 Then
  111.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  112.     ElseIf [Forms![Survey Form]!CkNov] = 0 Then
  113.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  114.     End If
  115.  
  116.         If [Forms![Survey Form]!CkDec] = -1 Then
  117.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  118.     ElseIf [Forms![Survey Form]!CkDec] = 0 Then
  119.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  120.     End If
  121.  
  122.             If [Forms![Survey Form]!SMCkBox] = -1 Then
  123.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  124.     ElseIf [Forms![Survey Form]!SMCkBox] = 0 Then
  125.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  126.     End If
  127.  
  128.             If [Forms![Survey Form]!MMCkBox] = -1 Then
  129.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  130.     ElseIf [Forms![Survey Form]!MMCkBox] = 0 Then
  131.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  132.     End If
  133.     '***********************************************************************
  134.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  135.     '***********************************************************************
  136.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  137.     lngLen = Len(strWhere) - 5
  138.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  139.         MsgBox "No criteria", vbInformation, "Nothing to do."
  140.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  141.         strWhere = Left$(strWhere, lngLen)
  142.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  143.         'Debug.Print strWhere
  144.  
  145.         'Finally, apply the string as the form's Filter.
  146.         Me.Filter = strWhere
  147.         Me.FilterOn = True
  148.     End If
  149.     DoCmd.OpenReport "Rpt NAC Survey", acViewPreview, , strWhere
  150.     DoCmd.close acForm, "Survey Form"
  151. End Sub
  152.  
  153. Private Sub Cancel_Click()
  154.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  155.     Dim ctl As Control
  156.  
  157.     'Clear all the controls in the Form Header section.
  158.     For Each ctl In Me.Section(acHeader).Controls
  159.         Select Case ctl.ControlType
  160.         Case acTextBox, acComboBox
  161.             ctl.Value = Null
  162.         Case acCheckBox
  163.             ctl.Value = False
  164.         End Select
  165.     Next
  166.  
  167.     'Remove the form's filter.
  168.     Me.Filter = "(False)"
  169.     Me.FilterOn = True
  170. End Sub
  171.  
  172. Private Sub Form_BeforeInsert(cancel As Integer)
  173.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  174.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  175.     'The problems are explained at http://allenbrowne.com/bug-06.html
  176.     cancel = True
  177.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  178. End Sub
  179.  
  180. Private Sub Form_Open(cancel As Integer)
  181.     'Remove the single quote from these lines if you want to initially show no records.
  182.     Me.Filter = "(False)"
  183.     Me.FilterOn = True
  184. End Sub
  185.  
Thanks!
martin
Oct 16 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
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

**********************
Oct 16 '07 #5

100+
P: 114
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!

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM COMPILE_HIST, [MONTH], Market
  3. WHERE (COMPILE_HIST.Month=MONTH.MonthID)
  4. 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,"")))
  5. 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)))
  6. AND ((COMPILE_HIST.NAC) Like (Forms![Export Historical Form]!NAC))
  7. AND ((COMPILE_HIST.AE) Like (Forms![Export Historical Form]!AE))
  8. AND ((COMPILE_HIST.SalesPerson) Like (Forms![Export Historical Form]!SalesP))
  9. AND ((COMPILE_HIST.SalesManager) Like (Forms![Export Historical Form]!SalesM));
  10.  
Oct 16 '07 #6

100+
P: 114
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
Oct 16 '07 #7

Rabbit
Expert Mod 10K+
P: 12,334
I only took a quick glance but this:
Expand|Select|Wrap|Line Numbers
  1. [Forms![Survey Form]!CkJan]
  2.  
Should be this:
Expand|Select|Wrap|Line Numbers
  1. [Forms]![Survey Form]![CkJan]
  2.  
And this applies to every reference to the form you used. Not just this one example.
Oct 16 '07 #8

Rabbit
Expert Mod 10K+
P: 12,334
As for the query, instead of:
Expand|Select|Wrap|Line Numbers
  1. ((COMPILE_HIST.AE) Like (Forms![Export Historical Form]!AE) & "*")
  2.  
Try:
Expand|Select|Wrap|Line Numbers
  1. ((Nz(COMPILE_HIST.AE, "")) Like "*" & (Forms![Export Historical Form]!AE) & "*")
  2.  
Oct 16 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 16 '07 #10

100+
P: 114
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
Oct 16 '07 #11

100+
P: 114
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!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub OK_Click()
  5.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  6.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  7.                         we remove the trailing " AND " at the end.
  8.     '           2. The date range works like this: _
  9.                         Both dates      = only dates between (both inclusive. _
  10.                         Start date only = all dates from this one onwards; _
  11.                         End date only   = all dates up to (and including this one).
  12.     Dim strWhere As String                  'The criteria string.
  13.     Dim lngLen As Long                      'Length of the criteria string to append to.
  14.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  15.  
  16.     '***********************************************************************
  17.     'Look at each search box, and build up the criteria string from the non-blank ones.
  18.     '***********************************************************************
  19.     'Another text field example. Use Like to find anywhere in the field.
  20.     If Not IsNull(Me.NAC) Then
  21.         strWhere = strWhere & "([COMPILE_HIST.NAC] Like ""*" & Me.NAC & "*"") AND "
  22.     End If
  23.  
  24.         If Not IsNull(Me.AE) Then
  25.         strWhere = strWhere & "([COMPILE_HIST.AE] Like ""*" & Me.AE & "*"") AND "
  26.     End If
  27.  
  28.         If Not IsNull(Me.SalesP) Then
  29.         strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like ""*" & Me.SalesP & "*"") AND "
  30.     End If
  31.  
  32.         If Not IsNull(Me.SalesM) Then
  33.         strWhere = strWhere & "([COMPILE_HIST.SalesManager] Like ""*" & Me.SalesM & "*"") AND "
  34.     End If
  35.  
  36.         'Number field example. Do not add the extra quotes.
  37.     If Not IsNull(Me.Year1) Then
  38.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year1 & ") AND "
  39.     End If
  40.  
  41.     If Not IsNull(Me.Year2) Then
  42.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") AND "
  43.     End If
  44.  
  45.     If Not IsNull(Me.Year3) Then
  46.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ") AND "
  47.     End If
  48.  
  49.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  50.     If Me.CkJan = -1 Then
  51.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  52.     ElseIf Me.CkJan = 0 Then
  53.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  54.     End If
  55.  
  56.     If Me.CkFeb = -1 Then
  57.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  58.     ElseIf Me.CkFeb = 0 Then
  59.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  60.     End If
  61.  
  62.     If Me.CkMar = -1 Then
  63.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  64.     ElseIf Me.CkMar = 0 Then
  65.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  66.     End If
  67.  
  68.     If Me.CkApr = -1 Then
  69.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  70.     ElseIf Me.CkApr = 0 Then
  71.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  72.     End If
  73.  
  74.     If Me.CkMay = -1 Then
  75.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  76.     ElseIf Me.CkMay = 0 Then
  77.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  78.     End If
  79.  
  80.     If Me.CkJun = -1 Then
  81.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  82.     ElseIf Me.CkJun = 0 Then
  83.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  84.     End If
  85.  
  86.     If Me.CkJul = -1 Then
  87.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  88.     ElseIf Me.CkJul = 0 Then
  89.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  90.     End If
  91.  
  92.     If Me.CkAug = -1 Then
  93.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  94.     ElseIf Me.CkAug = 0 Then
  95.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  96.     End If
  97.  
  98.     If Me.CkSep = -1 Then
  99.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  100.     ElseIf Me.CkSep = 0 Then
  101.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  102.     End If
  103.  
  104.     If Me.CkOct = -1 Then
  105.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  106.     ElseIf Me.CkOct = 0 Then
  107.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  108.     End If
  109.  
  110.     If Me.CkNov = -1 Then
  111.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  112.     ElseIf Me.CkNov = 0 Then
  113.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  114.     End If
  115.  
  116.     If Me.CkDec = -1 Then
  117.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) AND "
  118.     ElseIf Me.CkDec = 0 Then
  119.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  120.     End If
  121.  
  122.     If Me.SMCkBox = -1 Then
  123.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) AND "
  124.     ElseIf Me.SMCkBox = 0 Then
  125.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
  126.     End If
  127.  
  128.     If Me.MMCkBox = -1 Then
  129.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) AND "
  130.     ElseIf Me.MMCkBox = 0 Then
  131.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
  132.     End If
  133.  
  134.     '***********************************************************************
  135.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  136.     '***********************************************************************
  137.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  138.     lngLen = Len(strWhere) - 5
  139.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  140.         MsgBox "No criteria", vbInformation, "Nothing to do."
  141.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  142.         strWhere = Left$(strWhere, lngLen)
  143.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  144.         'Debug.Print strWhere
  145.  
  146.         'Finally, apply the string as the form's Filter.
  147.         Me.Filter = strWhere
  148.         Me.FilterOn = True
  149.     End If
  150.     DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
  151.     DoCmd.Close acForm, "Survey Form"
  152. End Sub
  153.  
  154. Private Sub Cancel_Click()
  155.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  156.     Dim ctl As Control
  157.  
  158.     'Clear all the controls in the Form Header section.
  159.     For Each ctl In Me.Section(acHeader).Controls
  160.         Select Case ctl.ControlType
  161.         Case acTextBox, acComboBox
  162.             ctl.Value = Null
  163.         Case acCheckBox
  164.             ctl.Value = False
  165.         End Select
  166.     Next
  167.  
  168.     'Remove the form's filter.
  169.     Me.FilterOn = False
  170. End Sub
  171.  
  172. Private Sub Form_BeforeInsert(cancel As Integer)
  173.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  174.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  175.     'The problems are explained at http://allenbrowne.com/bug-06.html
  176.     cancel = True
  177.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  178. End Sub
  179.  
  180. Private Sub Form_Open(cancel As Integer)
  181.     'Remove the single quote from these lines if you want to initially show no records.
  182.     Me.Filter = "(False)"
  183.     Me.FilterOn = True
  184. End Sub
  185.  
Oct 16 '07 #12

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 16 '07 #13

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

Option 1:
Expand|Select|Wrap|Line Numbers
  1.         'Number field example. Do not add the extra quotes.
  2.     If Not IsNull(Me.Year1) Then
  3.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
  4.     End If
  5.  
  6.     If Not IsNull(Me.Year2) Then
  7.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
  8.     End If
  9.  
  10.     If Not IsNull(Me.Year3) Then
  11.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ") OR "
  12.     End If
  13.  
  14.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  15.     If Me.CkJan = -1 Then
  16.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  17.     ElseIf Me.CkJan = 0 Then
  18.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  19.     End If
  20.  
  21.     If Me.CkFeb = -1 Then
  22.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  23.     ElseIf Me.CkFeb = 0 Then
  24.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  25.     End If 
Option 2:
Expand|Select|Wrap|Line Numbers
  1.         'Number field example. Do not add the extra quotes.
  2.     If Not IsNull(Me.Year1) Then
  3.         strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
  4.     End If
  5.  
  6.     If Not IsNull(Me.Year2) Then
  7.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
  8.     End If
  9.  
  10.     If Not IsNull(Me.Year3) Then
  11.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
  12.     End If
  13.  
  14.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  15.     If Me.CkJan = -1 Then
  16.         strWhere = strWhere & "(([COMPILE_HIST.Month] = True) OR "
  17.     ElseIf Me.CkJan = 0 Then
  18.         strWhere = strWhere & "(([COMPILE_HIST.Month] = False) OR "
  19.     End If
  20.  
  21.     If Me.CkFeb = -1 Then
  22.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  23.     ElseIf Me.CkFeb = 0 Then
  24.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  25.     End If 
Oct 17 '07 #14

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 17 '07 #15

100+
P: 114
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!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub OK_Click()
  5.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  6.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  7.                         we remove the trailing " AND " at the end.
  8.     '           2. The date range works like this: _
  9.                         Both dates      = only dates between (both inclusive. _
  10.                         Start date only = all dates from this one onwards; _
  11.                         End date only   = all dates up to (and including this one).
  12.     Dim strWhere As String                  'The criteria string.
  13.     Dim lngLen As Long                      'Length of the criteria string to append to.
  14.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  15.  
  16.     '***********************************************************************
  17.     'Look at each search box, and build up the criteria string from the non-blank ones.
  18.     '***********************************************************************
  19.     'Another text field example. Use Like to find anywhere in the field.
  20.     If Not IsNull(Me.NAC) Then
  21.         strWhere = strWhere & "([COMPILE_HIST.NAC] Like ""*" & Me.NAC & "*"") AND "
  22.     End If
  23.  
  24.         If Not IsNull(Me.AE) Then
  25.         strWhere = strWhere & "([COMPILE_HIST.AE] Like ""*" & Me.AE & "*"") AND "
  26.     End If
  27.  
  28.         If Not IsNull(Me.SalesP) Then
  29.         strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like ""*" & Me.SalesP & "*"") AND "
  30.     End If
  31.  
  32.         If Not IsNull(Me.SalesM) Then
  33.         strWhere = strWhere & "([COMPILE_HIST.SalesManager] Like ""*" & Me.SalesM & "*"") AND "
  34.     End If
  35.  
  36.         'Number field example. Do not add the extra quotes.
  37.     If Not IsNull(Me.Year1) Then
  38.         strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
  39.     End If
  40.  
  41.     If Not IsNull(Me.Year2) Then
  42.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
  43.     End If
  44.  
  45.     If Not IsNull(Me.Year3) Then
  46.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
  47.     End If
  48.  
  49.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  50.     If Me.CkJan = -1 Then
  51.         strWhere = strWhere & "(([COMPILE_HIST.Month] = True) OR "
  52.     ElseIf Me.CkJan = 0 Then
  53.         strWhere = strWhere & "(([COMPILE_HIST.Month] = False) OR "
  54.     End If
  55.  
  56.     If Me.CkFeb = -1 Then
  57.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  58.     ElseIf Me.CkFeb = 0 Then
  59.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  60.     End If
  61.  
  62.     If Me.CkMar = -1 Then
  63.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  64.     ElseIf Me.CkMar = 0 Then
  65.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  66.     End If
  67.  
  68.     If Me.CkApr = -1 Then
  69.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  70.     ElseIf Me.CkApr = 0 Then
  71.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  72.     End If
  73.  
  74.     If Me.CkMay = -1 Then
  75.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  76.     ElseIf Me.CkMay = 0 Then
  77.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  78.     End If
  79.  
  80.     If Me.CkJun = -1 Then
  81.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  82.     ElseIf Me.CkJun = 0 Then
  83.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  84.     End If
  85.  
  86.     If Me.CkJul = -1 Then
  87.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  88.     ElseIf Me.CkJul = 0 Then
  89.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  90.     End If
  91.  
  92.     If Me.CkAug = -1 Then
  93.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  94.     ElseIf Me.CkAug = 0 Then
  95.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  96.     End If
  97.  
  98.     If Me.CkSep = -1 Then
  99.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  100.     ElseIf Me.CkSep = 0 Then
  101.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  102.     End If
  103.  
  104.     If Me.CkOct = -1 Then
  105.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  106.     ElseIf Me.CkOct = 0 Then
  107.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  108.     End If
  109.  
  110.     If Me.CkNov = -1 Then
  111.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  112.     ElseIf Me.CkNov = 0 Then
  113.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) OR "
  114.     End If
  115.  
  116.     If Me.CkDec = -1 Then
  117.         strWhere = strWhere & "([COMPILE_HIST.Month] = True)) AND "
  118.     ElseIf Me.CkDec = 0 Then
  119.         strWhere = strWhere & "([COMPILE_HIST.Month] = False)) AND "
  120.     End If
  121.  
  122.     If Me.SMCkBox = -1 Then
  123.         strWhere = strWhere & "(([COMPILE_HIST.MarketID] = True) OR "
  124.     ElseIf Me.SMCkBox = 0 Then
  125.         strWhere = strWhere & "(([COMPILE_HIST.MarketID] = False) OR "
  126.     End If
  127.  
  128.     If Me.MMCkBox = -1 Then
  129.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True)) AND "
  130.     ElseIf Me.MMCkBox = 0 Then
  131.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False)) AND "
  132.     End If
  133.  
  134.     '***********************************************************************
  135.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  136.     '***********************************************************************
  137.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  138.     lngLen = Len(strWhere) - 5
  139.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  140.         MsgBox "No criteria", vbInformation, "Nothing to do."
  141.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  142.         strWhere = Left$(strWhere, lngLen)
  143.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  144.         'Debug.Print strWhere
  145.  
  146.         'Finally, apply the string as the form's Filter.
  147.         Me.Filter = strWhere
  148.         Me.FilterOn = True
  149.     End If
  150.     DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
  151.     DoCmd.Close acForm, "Survey Form"
  152. End Sub
  153.  
  154. Private Sub Cancel_Click()
  155.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  156.     Dim ctl As Control
  157.  
  158.     'Clear all the controls in the Form Header section.
  159.     For Each ctl In Me.Section(acHeader).Controls
  160.         Select Case ctl.ControlType
  161.         Case acTextBox, acComboBox
  162.             ctl.Value = Null
  163.         Case acCheckBox
  164.             ctl.Value = False
  165.         End Select
  166.     Next
  167.  
  168.     'Remove the form's filter.
  169.     Me.FilterOn = False
  170. End Sub
  171.  
  172. Private Sub Form_BeforeInsert(cancel As Integer)
  173.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  174.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  175.     'The problems are explained at http://allenbrowne.com/bug-06.html
  176.     cancel = True
  177.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  178. End Sub
  179.  
  180. Private Sub Form_Open(cancel As Integer)
  181.     'Remove the single quote from these lines if you want to initially show no records.
  182.     Me.Filter = "(False)"
  183.     Me.FilterOn = True
  184. End Sub
Oct 17 '07 #16

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 17 '07 #17

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 18 '07 #18

100+
P: 114
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:
Expand|Select|Wrap|Line Numbers
  1.     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
Oct 18 '07 #19

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1.     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.
Oct 18 '07 #20

Jim Doherty
Expert 100+
P: 897
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:
Expand|Select|Wrap|Line Numbers
  1.  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 :)
Oct 18 '07 #21

100+
P: 114
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:
Expand|Select|Wrap|Line Numbers
  1. ([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
Oct 18 '07 #22

puppydogbuddy
Expert 100+
P: 1,923
Here is the sql string that returns:
Expand|Select|Wrap|Line Numbers
  1. ([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
Expand|Select|Wrap|Line Numbers
  1. ([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
Expand|Select|Wrap|Line Numbers
  1. ([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)
Oct 18 '07 #23

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1.      'Number field example. Do not add the extra quotes.
  2.     If Not IsNull(Me.Year1) Then
  3.         strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
  4.     ElseIf Not IsNull(Me.Year2) Then
  5.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
  6.     ElseIf Not IsNull(Me.Year3) Then
  7.        strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
  8.     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.
Oct 18 '07 #24

100+
P: 114
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:
Expand|Select|Wrap|Line Numbers
  1. ([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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  3.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  4.                         we remove the trailing " AND " at the end.
  5.     '           2. The date range works like this: _
  6.                         Both dates      = only dates between (both inclusive. _
  7.                         Start date only = all dates from this one onwards; _
  8.                         End date only   = all dates up to (and including this one).
  9.     Dim strWhere As String                  'The criteria string.
  10.     Dim lngLen As Long                      'Length of the criteria string to append to.
  11.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  12.  
  13.     '***********************************************************************
  14.     'Look at each search box, and build up the criteria string from the non-blank ones.
  15.     '***********************************************************************
  16.     'Another text field example. Use Like to find anywhere in the field.
  17.     If Not IsNull(Me.NAC) Then
  18.         strWhere = strWhere & "([COMPILE_HIST.NAC] Like " & "*" & Me.NAC & "*" & ") AND "
  19.     End If
  20.  
  21.         If Not IsNull(Me.AE) Then
  22.         strWhere = strWhere & "([COMPILE_HIST.AE] Like " & "*" & Me.AE & "*" & ") AND "
  23.     End If
  24.  
  25.         If Not IsNull(Me.SalesP) Then
  26.         strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like " & "*" & Me.SalesP & "*" & ") AND "
  27.     End If
  28.  
  29.         If Not IsNull(Me.SalesM) Then
  30.         strWhere = strWhere & "([COMPILE_HIST.SalesManager] Like " & "*" & Me.SalesM & "*" & ") AND "
  31.     End If
  32.  
  33.         'Number field example. Do not add the extra quotes.
  34.     If Not IsNull(Me.Year1) Then
  35.         strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") OR "
  36.     ElseIf Not IsNull(Me.Year2) Then
  37.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") OR "
  38.     ElseIf Not IsNull(Me.Year3) Then
  39.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
  40.     End If
  41.  
  42.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  43.     If Me.CkJan = -1 Then
  44.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  45.     ElseIf Me.CkJan = 0 Then
  46.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  47.     End If
  48.  
  49.     If Me.CkFeb = -1 Then
  50.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  51.     ElseIf Me.CkFeb = 0 Then
  52.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  53.     End If
  54.  
  55.     If Me.CkMar = -1 Then
  56.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  57.     ElseIf Me.CkMar = 0 Then
  58.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  59.     End If
  60.  
  61.     If Me.CkApr = -1 Then
  62.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  63.     ElseIf Me.CkApr = 0 Then
  64.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  65.     End If
  66.  
  67.     If Me.CkMay = -1 Then
  68.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  69.     ElseIf Me.CkMay = 0 Then
  70.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  71.     End If
  72.  
  73.     If Me.CkJun = -1 Then
  74.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  75.     ElseIf Me.CkJun = 0 Then
  76.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  77.     End If
  78.  
  79.     If Me.CkJul = -1 Then
  80.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  81.     ElseIf Me.CkJul = 0 Then
  82.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  83.     End If
  84.  
  85.     If Me.CkAug = -1 Then
  86.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  87.     ElseIf Me.CkAug = 0 Then
  88.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  89.     End If
  90.  
  91.     If Me.CkSep = -1 Then
  92.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  93.     ElseIf Me.CkSep = 0 Then
  94.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  95.     End If
  96.  
  97.     If Me.CkOct = -1 Then
  98.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  99.     ElseIf Me.CkOct = 0 Then
  100.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  101.     End If
  102.  
  103.     If Me.CkNov = -1 Then
  104.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  105.     ElseIf Me.CkNov = 0 Then
  106.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  107.     End If
  108.  
  109.     If Me.CkDec = -1 Then
  110.         strWhere = strWhere & "([COMPILE_HIST.Month] = True) OR "
  111.     ElseIf Me.CkDec = 0 Then
  112.         strWhere = strWhere & "([COMPILE_HIST.Month] = False) AND "
  113.     End If
  114.  
  115.     If Me.SMCkBox = -1 Then
  116.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  117.     ElseIf Me.SMCkBox = 0 Then
  118.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
  119.     End If
  120.  
  121.     If Me.MMCkBox = -1 Then
  122.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  123.     ElseIf Me.MMCkBox = 0 Then
  124.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
  125.     End If
  126.  
  127.     '***********************************************************************
  128.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  129.     '***********************************************************************
  130.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  131.     lngLen = Len(strWhere) - 5
  132.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  133.         MsgBox "No criteria", vbInformation, "Nothing to do."
  134.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  135.         strWhere = Left$(strWhere, lngLen)
  136.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  137.         Debug.Print strWhere
  138.  
  139.         'Finally, apply the string as the form's Filter.
  140.         Me.Filter = strWhere
  141.         Me.FilterOn = True
  142.     End If
  143.     DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
  144.     DoCmd.Close acForm, "Survey Form"
  145. End Sub
Thank you!
Oct 18 '07 #25

puppydogbuddy
Expert 100+
P: 1,923
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
Oct 18 '07 #26

100+
P: 114
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.
Oct 18 '07 #27

puppydogbuddy
Expert 100+
P: 1,923
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
Oct 18 '07 #28

puppydogbuddy
Expert 100+
P: 1,923
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  3.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  4.                         we remove the trailing " AND " at the end.
  5.     '           2. The date range works like this: _
  6.                         Both dates      = only dates between (both inclusive. _
  7.                         Start date only = all dates from this one onwards; _
  8.                         End date only   = all dates up to (and including this one).
  9.     Dim strWhere As String                  'The criteria string.
  10.     Dim lngLen As Long                      'Length of the criteria string to append to.
  11.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  12.  
  13.     '*************************************************  **********************
  14.     'Look at each search box, and build up the criteria string from the non-blank ones.
  15.     '*************************************************  **********************
  16.     'Another text field example. Use Like to find anywhere in the field.
  17.     If Not IsNull(Me.NAC) Then
  18.         strWhere = strWhere & "([COMPILE_HIST.NAC] Like " & "*" & Me.NAC & "*" & ") AND "
  19.     End If
  20.  
  21.         If Not IsNull(Me.AE) Then
  22.         strWhere = strWhere & "([COMPILE_HIST.AE] Like " & "*" & Me.AE & "*" & ") AND "
  23.     End If
  24.  
  25.         If Not IsNull(Me.SalesP) Then
  26.         strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like " & "*" & Me.SalesP & "*" & ") AND "
  27.     End If
  28.  
  29.         If Not IsNull(Me.SalesM) Then
  30.         strWhere = strWhere & "([COMPILE_HIST.SalesManager] Like " & "*" & Me.SalesM & "*" & ") AND "
  31.     End If
  32.  
  33.         'Number field example. Do not add the extra quotes.
  34.     If Not IsNull(Me.Year1) Then
  35.         strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") AND "
  36.     End If
  37.     If Not IsNull(Me.Year2) Then
  38.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") AND "
  39.    End If
  40.    If Not IsNull(Me.Year3) Then
  41.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
  42.    End If
  43.  
  44.  
  45.     'number field example
  46.     If Me.CkJan = -1 And Not IsNull(Me.Month(1)) Then
  47.         strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
  48.     End If
  49.  
  50.     If Me.CkFeb = -1 And Not IsNull(Me.Month(2)) Then
  51.         strWhere = strWhere & "([COMPILE_HIST.Month] = 2) AND "
  52.     End If
  53.  
  54.     If Me.CkMar = -1 And Not IsNull(Me.Month(3)) Then
  55.         strWhere = strWhere & "([COMPILE_HIST.Month] = 3) AND "
  56.     End If
  57.  
  58.     If Me.CkApr = -1 And Not IsNull(Me.Month(4)) Then
  59.         strWhere = strWhere & "([COMPILE_HIST.Month] = 4) AND "
  60.     End If
  61.  
  62.     If Me.CkMay = -1 And Not IsNull(Me.Month(5)) Then
  63.         strWhere = strWhere & "([COMPILE_HIST.Month] = 5) AND "
  64.     End If
  65.  
  66.     If Me.CkJun = -1 And Not IsNull(Me.Month(6)) Then
  67.         strWhere = strWhere & "([COMPILE_HIST.Month] = 6) AND "
  68.     End If
  69.  
  70.     If Me.CkJul = -1 And Not IsNull(Me.Month(7)) Then
  71.         strWhere = strWhere & "([COMPILE_HIST.Month] = 7) AND "
  72.     End If
  73.  
  74.     If Me.CkAug = -1 And Not IsNull(Me.Month(8)) Then
  75.         strWhere = strWhere & "([COMPILE_HIST.Month] = 8) AND "
  76.     End If
  77.  
  78.     If Me.CkSep = -1 And Not IsNull(Me.Month(9)) Then
  79.         strWhere = strWhere & "([COMPILE_HIST.Month] = 9) AND "
  80.     End If
  81.  
  82.     If Me.CkOct = -1 And Not IsNull(Me.Month(10)) Then
  83.         strWhere = strWhere & "([COMPILE_HIST.Month] = 10) AND "
  84.     End If
  85.  
  86.     If Me.CkNov = -1 And Not IsNull(Me.Month(11)) Then
  87.         strWhere = strWhere & "([COMPILE_HIST.Month] = 11) AND "
  88.     End If
  89.  
  90.     If Me.CkDec = -1 And Not IsNull(Me.Month(12)) Then
  91.         strWhere = strWhere & "([COMPILE_HIST.Month] = 12) AND "
  92.     End If
  93.  
  94.     If Me.SMCkBox = -1 Then
  95.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  96.     ElseIf Me.SMCkBox = 0 Then
  97.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
  98.     End If
  99.  
  100.     If Me.MMCkBox = -1 Then
  101.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  102.     ElseIf Me.MMCkBox = 0 Then
  103.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
  104.     End If
  105.  
  106.     '*************************************************  **********************
  107.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  108.     '*************************************************  **********************
  109.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  110.     lngLen = Len(strWhere) - 5
  111.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  112.         MsgBox "No criteria", vbInformation, "Nothing to do."
  113.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  114.         strWhere = Left$(strWhere, lngLen)
  115.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  116.         Debug.Print strWhere
  117.  
  118.         'Finally, apply the string as the form's Filter.
  119.         Me.Filter = strWhere
  120.         Me.FilterOn = True
  121.     End If
  122.     DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
  123.     DoCmd.Close acForm, "Survey Form"
  124. End Sub
Oct 19 '07 #29

100+
P: 114
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!
Oct 19 '07 #30

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

Expand|Select|Wrap|Line Numbers
  1.     'number field example
  2.     If Me.CkJan = -1 And Not IsNull(Me.Month(1)) Then
  3.         strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
  4. End If
TO
Expand|Select|Wrap|Line Numbers
  1.     'number field example
  2.     If Me.CkJan = -1 And Not IsNull(Me.CkJan) Then
  3.         strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
  4.     End If
and applied this concept to the Market checkboxes changing:

Expand|Select|Wrap|Line Numbers
  1.     If Me.SMCkBox = -1 Then
  2.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  3.     ElseIf Me.SMCkBox = 0 Then
  4.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
  5.     End If
  6.  
  7.     If Me.MMCkBox = -1 Then
  8.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  9.     ElseIf Me.MMCkBox = 0 Then
  10.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
  11.     End If
TO
Expand|Select|Wrap|Line Numbers
  1.     If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
  2.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = 1) AND "
  3.     End If
  4.     If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
  5.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = 2) AND "
  6.     End If
Now when I run I'm back to the 3075 error and the following string appears in the immediate window on debug:
Expand|Select|Wrap|Line Numbers
  1. ([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
Oct 19 '07 #31

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 19 '07 #32

100+
P: 114
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.
Attached Files
File Type: pdf Form Image.pdf (8.3 KB, 232 views)
Oct 19 '07 #33

Rabbit
Expert Mod 10K+
P: 12,334
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2. strCriteria = "("
  3.  
  4. If chk_Jan = True Then
  5.    strCriteria = strCriteria & "(MonthID = 1) OR "
  6. End If
  7.  
  8. If chk_Feb = True Then
  9.    strCriteria = strCriteria & "(MonthID = 2) OR "
  10. End If
  11.  
  12. ...
  13.  
  14. If chk_Dec = True Then
  15.    strCriteria = strCriteria & "(MonthID = 12) OR "
  16. End If
  17.  
  18. If Len(strCriteria) = 1 Then
  19.    MsgBox "No Months Checked."
  20.    Exit Sub
  21. End If
  22.  
  23. strCriteria = Left(strCriteria, Len(strCriteria) - 4) & ")"
  24.  
Oct 19 '07 #34

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 22 '07 #35

puppydogbuddy
Expert 100+
P: 1,923
Ok, I was looking at it a little further. I changed this throughout the month strings:

Expand|Select|Wrap|Line Numbers
  1.     'number field example
  2.     If Me.CkJan = -1 And Not IsNull(Me.Month(1)) Then
  3.         strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
  4. End If
TO
Expand|Select|Wrap|Line Numbers
  1.     'number field example
  2.     If Me.CkJan = -1 And Not IsNull(Me.CkJan) Then
  3.         strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
  4.     End If
and applied this concept to the Market checkboxes changing:

Expand|Select|Wrap|Line Numbers
  1.     If Me.SMCkBox = -1 Then
  2.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  3.     ElseIf Me.SMCkBox = 0 Then
  4.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) OR "
  5.     End If
  6.  
  7.     If Me.MMCkBox = -1 Then
  8.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = True) OR "
  9.     ElseIf Me.MMCkBox = 0 Then
  10.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = False) AND "
  11.     End If
TO
Expand|Select|Wrap|Line Numbers
  1.     If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
  2.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = 1) AND "
  3.     End If
  4.     If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
  5.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = 2) AND "
  6.     End If
Now when I run I'm back to the 3075 error and the following string appears in the immediate window on debug:
Expand|Select|Wrap|Line Numbers
  1. ([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.
Oct 22 '07 #36

puppydogbuddy
Expert 100+
P: 1,923
=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.
Oct 22 '07 #37

P: 76
Ok now how do you do it in a Data Access Page? Using multiple criteria to filter 1 record set.
Oct 22 '07 #38

100+
P: 114
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:
Expand|Select|Wrap|Line Numbers
  1. (([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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  3.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  4.                         we remove the trailing " AND " at the end.
  5.     '           2. The date range works like this: _
  6.                         Both dates      = only dates between (both inclusive. _
  7.                         Start date only = all dates from this one onwards; _
  8.                         End date only   = all dates up to (and including this one).
  9.     Dim strWhere As String                  'The criteria string.
  10.     Dim lngLen As Long                      'Length of the criteria string to append to.
  11.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  12.  
  13.     '*************************************************    **********************
  14.     'Look at each search box, and build up the criteria string from the non-blank ones.
  15.     '*************************************************    **********************
  16.     'Another text field example. Use Like to find anywhere in the field.
  17.     If Not IsNull(Me.NAC) Then
  18.         strWhere = strWhere & "([COMPILE_HIST.NAC] Like ""*" & Me.NAC & "*"") AND "
  19.     End If
  20.  
  21.         If Not IsNull(Me.AE) Then
  22.         strWhere = strWhere & "([COMPILE_HIST.AE] Like ""*" & Me.AE & "*"") AND "
  23.     End If
  24.  
  25.         If Not IsNull(Me.SalesP) Then
  26.         strWhere = strWhere & "([COMPILE_HIST.SalesPerson] Like ""*" & Me.SalesP & "*"") AND "
  27.     End If
  28.  
  29.         If Not IsNull(Me.SalesM) Then
  30.         strWhere = strWhere & "([COMPILE_HIST.SalesManager] Like ""*" & Me.SalesM & "*"") AND "
  31.     End If
  32.  
  33.         'Number field example. Do not add the extra quotes.
  34.     If Not IsNull(Me.Year1) Then
  35.         strWhere = strWhere & "(([COMPILE_HIST.Year] = " & Me.Year1 & ") AND "
  36.     End If
  37.     If Not IsNull(Me.Year2) Then
  38.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year2 & ") AND "
  39.    End If
  40.    If Not IsNull(Me.Year3) Then
  41.         strWhere = strWhere & "([COMPILE_HIST.Year] = " & Me.Year3 & ")) AND "
  42.    End If
  43.  
  44.  
  45.     'number field example
  46. Dim strCriteria As String
  47. strCriteria = "("
  48.  
  49.     If Me.CkJan = -1 And Not IsNull(Me.CkJan) Then
  50.         strWhere = strWhere & "([COMPILE_HIST.Month] = 1) AND "
  51.     End If
  52.  
  53.     If Me.CkFeb = -1 And Not IsNull(Me.CkFeb) Then
  54.         strWhere = strWhere & "([COMPILE_HIST.Month] = 2) AND "
  55.     End If
  56.  
  57.     If Me.CkMar = -1 And Not IsNull(Me.CkMar) Then
  58.         strWhere = strWhere & "([COMPILE_HIST.Month] = 3) AND "
  59.     End If
  60.  
  61.     If Me.CkApr = -1 And Not IsNull(Me.CkApr) Then
  62.         strWhere = strWhere & "([COMPILE_HIST.Month] = 4) AND "
  63.     End If
  64.  
  65.     If Me.CkMay = -1 And Not IsNull(Me.CkMay) Then
  66.         strWhere = strWhere & "([COMPILE_HIST.Month] = 5) AND "
  67.     End If
  68.  
  69.     If Me.CkJun = -1 And Not IsNull(Me.CkJun) Then
  70.         strWhere = strWhere & "([COMPILE_HIST.Month] = 6) AND "
  71.     End If
  72.  
  73.     If Me.CkJul = -1 And Not IsNull(Me.CkJul) Then
  74.         strWhere = strWhere & "([COMPILE_HIST.Month] = 7) AND "
  75.     End If
  76.  
  77.     If Me.CkAug = -1 And Not IsNull(Me.CkAug) Then
  78.         strWhere = strWhere & "([COMPILE_HIST.Month] = 8) AND "
  79.     End If
  80.  
  81.     If Me.CkSep = -1 And Not IsNull(Me.CkSep) Then
  82.         strWhere = strWhere & "([COMPILE_HIST.Month] = 9) AND "
  83.     End If
  84.  
  85.     If Me.CkOct = -1 And Not IsNull(Me.CkOct) Then
  86.         strWhere = strWhere & "([COMPILE_HIST.Month] = 10) AND "
  87.     End If
  88.  
  89.     If Me.CkNov = -1 And Not IsNull(Me.CkNov) Then
  90.         strWhere = strWhere & "([COMPILE_HIST.Month] = 11) AND "
  91.     End If
  92.  
  93.     If Me.CkDec = -1 And Not IsNull(Me.CkDec) Then
  94.         strWhere = strWhere & "([COMPILE_HIST.Month] = 12) AND "
  95.     End If
  96.  
  97.     If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
  98.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = 1) AND "
  99.     End If
  100.     If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
  101.         strWhere = strWhere & "([COMPILE_HIST.MarketID] = 2) AND "
  102.     End If
  103.  
  104.     '*************************************************    **********************
  105.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  106.     '*************************************************    **********************
  107.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  108.     lngLen = Len(strWhere) - 5
  109.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  110.         MsgBox "No criteria", vbInformation, "Nothing to do."
  111.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  112.         strWhere = Left$(strWhere, lngLen)
  113.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  114.         Debug.Print strWhere
  115.  
  116.         'Finally, apply the string as the form's Filter.
  117.         Me.Filter = strWhere
  118.         Me.FilterOn = True
  119.     End If
  120.     DoCmd.OpenReport "Quick Report", acViewPreview, , strWhere
  121.     DoCmd.Close acForm, "Survey Form"
  122. End Sub
Oct 22 '07 #39

Rabbit
Expert Mod 10K+
P: 12,334
Replace the appropriate block of code with the following:
Expand|Select|Wrap|Line Numbers
  1.     If Not IsNull(Me.Year1) Or Not IsNull(Me.Year2) Or Not IsNull(Me.Year3) Then
  2.         strWhere = strWhere & "[COMPILE_HIST.Year] IN ("
  3.  
  4.         If Not IsNull(Me.Year1) Then
  5.             strWhere = strWhere & Me.Year1 & ","
  6.         End If
  7.  
  8.         If Not IsNull(Me.Year2) Then
  9.             strWhere = strWhere & Me.Year2 & ","
  10.         End If
  11.  
  12.         If Not IsNull(Me.Year3) Then
  13.             strWhere = strWhere & Me.Year3
  14.         Else
  15.             strWhere = Left(strWhere, Len(strWhere) - 1)
  16.         End If
  17.  
  18.         strWhere = strWhere & ") AND "
  19.     End If
  20.  
  21.      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
  22.         strWhere = strWhere & "[COMPILE_HIST.Month] IN ("
  23.  
  24.         If Me.CkJan = -1 And Not IsNull(Me.CkJan) Then
  25.             strWhere = strWhere & "1,"
  26.         End If
  27.  
  28.         If Me.CkFeb = -1 And Not IsNull(Me.CkFeb) Then
  29.             strWhere = strWhere & "2,"
  30.         End If
  31.  
  32.         If Me.CkMar = -1 And Not IsNull(Me.CkMar) Then
  33.             strWhere = strWhere & "3,"
  34.         End If
  35.  
  36.         If Me.CkApr = -1 And Not IsNull(Me.CkApr) Then
  37.             strWhere = strWhere & "4,"
  38.         End If
  39.  
  40.         If Me.CkMay = -1 And Not IsNull(Me.CkMay) Then
  41.             strWhere = strWhere & "5,"
  42.         End If
  43.  
  44.         If Me.CkJun = -1 And Not IsNull(Me.CkJun) Then
  45.             strWhere = strWhere & "6,"
  46.         End If
  47.  
  48.         If Me.CkJul = -1 And Not IsNull(Me.CkJul) Then
  49.             strWhere = strWhere & "7,"
  50.         End If
  51.  
  52.         If Me.CkAug = -1 And Not IsNull(Me.CkAug) Then
  53.             strWhere = strWhere & "8,"
  54.         End If
  55.  
  56.         If Me.CkSep = -1 And Not IsNull(Me.CkSep) Then
  57.             strWhere = strWhere & "9,"
  58.         End If
  59.  
  60.         If Me.CkOct = -1 And Not IsNull(Me.CkOct) Then
  61.             strWhere = strWhere & "10,"
  62.         End If
  63.  
  64.         If Me.CkNov = -1 And Not IsNull(Me.CkNov) Then
  65.             strWhere = strWhere & "11,"
  66.         End If
  67.  
  68.         If Me.CkDec = -1 And Not IsNull(Me.CkDec) Then
  69.             strWhere = strWhere & "12"
  70.         Else
  71.             strWhere = Left(strWhere, Len(strWhere) - 1)
  72.         End If
  73.  
  74.         strWhere = strWhere & ") AND "
  75.     End If
  76.  
  77.     If Me.SMCkBox = -1 Or Me.MMCkBox = -1 Then
  78.         strWhere = strWhere & "[COMPILE_HIST.MarketID] IN ("
  79.  
  80.         If Me.SMCkBox = -1 And Not IsNull(Me.SMCkBox) Then
  81.             strWhere = strWhere & "1,"
  82.         End If
  83.  
  84.         If Me.MMCkBox = -1 And Not IsNull(Me.MMCkBox) Then
  85.             strWhere = strWhere & "2"
  86.         Else
  87.             strWhere = Left(strWhere, Len(strWhere) - 1)
  88.         End If
  89.  
  90.         strWhere = strWhere & ") AND "
  91.     End If
  92.  
I did not test the code.
Oct 22 '07 #40

puppydogbuddy
Expert 100+
P: 1,923
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.
Oct 22 '07 #41

100+
P: 114
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:
Expand|Select|Wrap|Line Numbers
  1. (([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
Oct 23 '07 #42

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. (([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:
Expand|Select|Wrap|Line Numbers
  1. Dim strReportName As string
  2.  
  3. If Not IsNull(Me.NAC) Then
    strReportName = "NAC Report"
    ElseIf Not IsNull(Me.AE) Then 
    strReportName = "AE Report"
    Else
    MsgBox "no NAC or AE criteria selected."
    End If
  4.  
  5. DoCmd.OpenReport strReportName, acViewPreview, , strWhere
Oct 23 '07 #43

100+
P: 114
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
Oct 23 '07 #44

puppydogbuddy
Expert 100+
P: 1,923
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"
Oct 23 '07 #45

100+
P: 114
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):
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 23 '07 #46

Rabbit
Expert Mod 10K+
P: 12,334
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:
Expand|Select|Wrap|Line Numbers
  1. (([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.
Oct 23 '07 #47

puppydogbuddy
Expert 100+
P: 1,923
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):
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 23 '07 #48

100+
P: 114
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.
Oct 23 '07 #49

puppydogbuddy
Expert 100+
P: 1,923
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.
Martin,
Sorry about that. you can use the sql string to open a recordset which will display your results in table format, which you can then print using the printer icon. see this link for sample code on how to do that:

http://www.fmsinc.com/tpapers/querie...html#SQLstring


you can also save the sql string as a querydef (query definition) using Access VBA. you can use the querydef just like a regular query. See this link:
http://www.blueclaw-db.com/access_createquerydef.htm


Also, read the entire link for one of the best tutorials on queries.

http://www.fmsinc.com/tpapers/queries/index.html
Oct 23 '07 #50

Post your reply

Sign in to post your reply or Sign up for a free account.