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

WHERE clause error message prevents all records from printing

P: 39
Hello All,

I am unable to get my VBA code to show all the records and allow me to

view the filtered responses from user input, once my cmd_click() event

fires.

The form displays the 19 fields from database in an AD HOC form, using a

continuous form format so it allows the user to select what fields they want

to filter.

To aid the user, I have two unbound combo boxes and four unbound text

boxes for user input:

Program name (Combo)
Source (Text)
Liaison (Text)
Status (Combo)
Due Date - From (Text)
Due Date - To (Text)

The filter works great. However, it only displays the FIRST record on the
report when I fire the command button to print preview as follows.

I realized that I needed to tell my report what to look for in the record source but I am getting sooo many errors on my SQL WHERE clause of my report table. What am I missing?
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   tblOpenActionItems
  3. WHERE  [Liaison_Contact_Information] = '"& Me.txtFilterLiaison &"'
  4.   AND  [Source] Like  '"*& Me.txtFilterSource &“’
  5.   AND  [Program] Like '"* & Me.cboFilterProgram & “’ 
  6.   AND  [Status] Like ‘”* & Me.cboFilterStatus & “’
  7.   AND  [Due_Date]>='"&Format(Me.txtStartFilterDate, conJetDate )& “’
  8.   AND  [Due_Date] <  '"& Format(Me.txtEndFilterDate + 1, conJetDate) “’
  9.   AND  [Program] Like '"* & Me.cboFilterProgram & “’
May 9 '10 #1
Share this Question
Share on Google+
10 Replies


patjones
Expert 100+
P: 931
Hi,

I think your placement of the asterisks in the Like comparisons is a bit off, so if you could try this, for starters, I'd be interested to see what you get. You also had a couple ' missing in some places.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblOpenActionItems.* 
  2. FROM tblOpenActionItems 
  3. WHERE tblOpenActionItems.[Liaison_Contact_Information] = '" & Me.txtFilterLiaison & "' AND             
  4.       tblOpenActionItems.[Source] Like '*" & Me.txtFilterSource & "' AND  
  5.       tblOpenActionItems.[Program] Like '*" & Me.cboFilterProgram & "' AND 
  6.       tblOpenActionItems.[Status] Like '*" & Me.cboFilterStatus & "' AND 
  7.       tblOpenActionItems.[Due_Date] >= '" & Format(Me.txtStartFilterDate, conJetDate ) & "' AND               
  8.       tblOpenActionItems.[Due_Date] <  '" & Format(Me.txtEndFilterDate + 1, conJetDate)& "' AND             
  9.       tblOpenActionItems.[Program] Like '*" & Me.cboFilterProgram & ""

This may not quite work though. I'm also looking at your date comparisons. These comparisons may not function correctly, because if your [Due_Date] field is formatted as a date in the table (which it should be), you won't get the right result trying to compare them to strings. I also don't think Me.txtEndFilterDate + 1 is going to work right. You might want to look into using the DateAdd function if you are trying to add one day to the date in question.

Pat
May 9 '10 #2

P: 39
Hey there Zepphead80,

I wonder how to decipher this error message:

The recourd source ‘tblOpenActionItems.[Liaison_Contact_Information] = '" & Me.txtFilterLiaison & "' AND tblOpenActionItems.[Source] Like '*" & …’specified on this form or report does not exist.

I truly do not understand as these are the fields from the form. I also do not see anything in the error that points directly to an error in the date

I must also mention that my report's fields point back to the fields from the form where the user makes direct input. For example, here is the Date Complete field source in the report:

Expand|Select|Wrap|Line Numbers
  1. =[Forms]![ASaP_AI_Form_Template_5]![Date Complete] 

Thanks!
May 9 '10 #3

patjones
Expert 100+
P: 931
There is a problem with the way you are putting the string together to form the whole SQL query. Can you post more of the code so that I can see the actually assignment of the SQL string?

Pat
May 9 '10 #4

NeoPa
Expert Mod 15k+
P: 31,494
QTip,

What you're missing here is that a number of the characters in your SQL, though they look similar to the correct ones, are not. Specifically single- and double-qoutes. It looks like you may have done work on this in a word processor. This is never a good idea and should be avoided like the plague.

Another good tip is to formulate your code so that it can be seen as easily as possible. The SQL interpreter itself may not care how well it is laid out, but other readers, as well as yourself, will find it a lot easier to comprehend, and more specifically proof, if it is all clearly visible.

I'll do this for you now and you will be able to see by looking at the code where your problems are.

Welcome to Bytes!
May 10 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
I tidied up your original code, without losing any more of the errors than was consistent with the reformat (I couldn't leave the missing spaces AND tidy it up). Doing this I noticed even more errors.

If, when you've cleared up as many as you can see, you find it still doesn't work then post back with your latest version and we'll see what else we can see for you.
May 10 '10 #6

patjones
Expert 100+
P: 931
@NeoPa
I caught those as well and changed them in the revised SQL that I posted, but maybe Qtip did not see it. At any rate, the whole string is just not concatenating properly for some reason, which is why I asked for Qtip to post more of what he/she is trying to code.

I think those date comparisons are not going to work quite right, in addition.

Pat
May 10 '10 #7

P: 39
@NeoPa & @zepphead80: Before I post the code, I definitely will have to look at how the code is placed.

I think my biggest problem is that while I understand the clean lines that must be used for entering SQL expressions in coding. I am actually attempting to place the SQL expression as a record source within the report property of the record source.

Could that be my major issue?

I appreciate your helpful responses and will attempt to make changes now and let you know what is the outcome.

Thanks is advance!
May 10 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
In case it helps :
One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.
May 10 '10 #9

P: 39
@NeoPa thanks for the tidbit of information regarding troubleshooting. I have a Debug.Print line of code already in there but I will continue to look and test out other the above changes.

@zepphead80 I have embedded code below for your review:

Expand|Select|Wrap|Line Numbers
  1. 'Source: http://allenbrowne.com
  2. 'Adapted from: http://allenbrowne.com
  3.  
  4. Option Compare Database
  5. Option Explicit
  6.  
  7. Private Sub cmdFilter_Click()
  8. On Error GoTo Err_cmdFilter_Click
  9. 'Purpose:   This module illustrates how to create a search form, _
  10.             where the user can enter as many or few criteria as they wish, _
  11.             and results are shown one per line.
  12. 'Note:      Only records matching ALL of the criteria are returned.
  13. 'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
  14. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  15.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  16.                         we remove the trailing " AND " at the end.
  17.     '           2. The date range works like this: _
  18.                         Both dates      = only dates between (both inclusive. _
  19.                         Start date only = all dates from this one onwards; _
  20.                         End date only   = all dates up to (and including this one).
  21.     Dim strWhere As String                  'The criteria string.
  22.     Dim lngLen As Long                      'Length of the criteria string to append to.
  23.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  24.  
  25.     '***********************************************************************
  26.     'Look at each search box, and build up the criteria string from the non-blank ones.
  27.     '***********************************************************************
  28.       'Liaison's Name Text field. Use quotes around the value in the string.
  29.      If Not IsNull(Me.txtFilterLiaison) Then
  30.         'strWhere = "Like '*'"
  31.     'Else
  32.         strWhere = strWhere & "([Liaison_Contact_Information] = """ & Me.txtFilterLiaison & """) AND "
  33.     End If
  34.  
  35.     'Source Text field. Use Like to find anywhere in the field.
  36.     If Not IsNull(Me.txtFilterSource) Then
  37.       '  strWhere = "Like '*'"
  38.     'Else
  39.         strWhere = strWhere & "([Source] Like ""*" & Me.txtFilterSource & "*"") AND "
  40.     End If
  41.  
  42.  
  43.     'Program Field. Do not add the extra quotes.
  44.      If Not IsNull(Me.cboFilterProgram) Then
  45.       '  strWhere = "Like '*'"
  46.     'Else
  47.         strWhere = strWhere & "([Program] Like ""*" & Me.cboFilterProgram & "*"") AND "
  48.     End If
  49.  
  50.      'Status Field. Do not add the extra quotes.
  51.     If Not IsNull(Me.cboFilterStatus) Then
  52.      '   strWhere = "Like '*'"
  53.     'Else
  54.         strWhere = strWhere & "([Status] Like ""*" & Me.cboFilterStatus & "*"") AND "
  55.     End If
  56.  
  57.  
  58.     'Due Date - Start Date field. Use the format string to add the # delimiters and get the right international format.
  59.     If Not IsNull(Me.txtStartFilterDate) Then
  60.      '   strWhere = "Like '*'"
  61.     'Else
  62.         strWhere = strWhere & "([Due_Date] >= " & Format(Me.txtStartFilterDate, conJetDate) & ") AND "
  63.     End If
  64.  
  65.     'Due Date - End Date field. Use "less than the next day" since this field has times as well as dates.
  66.      If Not IsNull(Me.txtEndFilterDate) Then
  67.      '   strWhere = "Like '*'"
  68.     'Else
  69.         strWhere = strWhere & "([Due_Date] < " & Format(Me.txtEndFilterDate + 1, conJetDate) & ") AND "
  70.     End If
  71.  
  72.     '***********************************************************************
  73.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  74.     '***********************************************************************
  75.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  76.     lngLen = Len(strWhere) - 5
  77.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  78.         MsgBox "No criteria", vbInformation, "Nothing to do."
  79.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  80.         strWhere = Left$(strWhere, lngLen)
  81.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  82.         Debug.Print strWhere
  83.  
  84.         'Finally, apply the string as the form's Filter.
  85.         Me.Filter = strWhere
  86.         Me.FilterOn = True
  87.     End If
  88.  
  89.  
  90. Exit_cmdFilter_Click:
  91.     Exit Sub
  92.  
  93. Err_cmdFilter_Click:
  94.     MsgBox Err.Description
  95.     Resume Exit_cmdFilter_Click
  96.  
  97.  
  98.  
  99. End Sub
  100.  
  101.  
  102.  
  103. Private Sub cmdReset_Click()
  104. On Error GoTo Err_cmdReset_Click
  105.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  106.     Dim ctl As Control
  107.  
  108.     'Clear all the controls in the Form Header section.
  109.     For Each ctl In Me.Section(acHeader).Controls
  110.         Select Case ctl.ControlType
  111.         Case acTextBox, acComboBox
  112.             ctl.Value = Null
  113.         Case acCheckBox
  114.             ctl.Value = False
  115.         End Select
  116.     Next
  117.  
  118.     'Remove the form's filter.
  119.     Me.FilterOn = False
  120.  
  121. Exit_cmdReset_Click:
  122.     Exit Sub
  123.  
  124. Err_cmdReset_Click:
  125.     MsgBox Err.Description
  126.     Resume Exit_cmdReset_Click
  127.  
  128.  
  129.  
  130.  
  131. End Sub
  132.  
  133. Private Sub Command142_Click()
  134. On Error GoTo Err_Command142_Click
  135.  
  136.     Dim stDocName As String
  137.     Dim strWhere As String                  'The criteria string.
  138.  
  139.  
  140.     stDocName = "rptAll_CBP_Action_Items"
  141.     DoCmd.OpenReport stDocName, acViewPreview
  142. Exit_Command142_Click:
  143.     Exit Sub
  144.  
  145. Err_Command142_Click:
  146.     MsgBox Err.Description
  147.     Resume Exit_Command142_Click
  148. End Sub
  149.  
  150. Private Sub Command44_Click()
  151. On Error GoTo Err_Command44_Click
  152.  
  153.  
  154.     DoCmd.Close
  155.  
  156. Exit_Command44_Click:
  157.     Exit Sub
  158.  
  159. Err_Command44_Click:
  160.     MsgBox Err.Description
  161.     Resume Exit_Command44_Click
  162.  
  163. End Sub
  164.  
  165. Private Sub Form_BeforeInsert(Cancel As Integer)
  166. On Error GoTo Err_Form_BeforeInsert
  167.  
  168.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  169.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  170.     'The problems are explained at http://allenbrowne.com/bug-06.html
  171.     Cancel = True
  172.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  173.  
  174.  
  175.  
  176. Exit_Form_BeforeInsert:
  177.     Exit Sub
  178.  
  179. Err_Form_BeforeInsert:
  180.     MsgBox Err.Description
  181.     Resume Exit_Form_BeforeInsert
  182.  
  183.  
  184.  
  185. End Sub
  186.  
  187. Private Sub Command139_Click()
  188. On Error GoTo Err_Command139_Click
  189.  
  190.     Dim stDocName As String
  191.     Dim strWhere As String                  'The criteria string.
  192.  
  193.     stDocName = "rptActionItems_Filtered_TurnBack on"
  194.       ''  If SysCmd(acSysCmdGetObjectState, acReport, stDocName) <> acObjStateOpen Then
  195.       ''  MsgBox "You must open the report first."
  196.  
  197.     DoCmd.OpenReport stDocName, acViewReport, strWhere
  198.     'DoCmd.OpenReport stDocName, acViewReport, , strWhere
  199.     'DoCmd.OpenReport stDocName, acViewPreview, strWhere
  200.     'DoCmd.OpenReport stDocName, acViewPreview, strWhere
  201.  
  202.  
  203.     '' End If
  204. Exit_Command139_Click:
  205.     Exit Sub
  206.  
  207. Err_Command139_Click:
  208.     MsgBox Err.Description
  209.     Resume Exit_Command139_Click
  210.  
  211. End Sub
  212.  
I am really learning alot and appreciate all the great feedback from a novice like myself!
May 10 '10 #10

P: 39
Well it took some time, testing, rearranging of code, etc. I was able to print out all the records based on the filtered response from the user. What I did was placed the same code for the cmdFilter_Click() button event as the Command139_Click() event (to preview the report), using WITH statements to turn the filter on. Then I used the acViewPreview option with the OPENREPORT to get the results in the report I saved and designed per my customer's request.

In addition, I want to know does the acViewReport parameter really work for reports?

So now I am on to a count down box that displays the number of characters a user places in a box, so when they exceed 255; they are unable to do so.

Thanks again everyone!
May 11 '10 #11

Post your reply

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