WHERE clause error message prevents all records from printing

[Qtip23]

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?

SELECT *
FROM   tblOpenActionItems
WHERE  [Liaison_Contact_Information] = '"& Me.txtFilterLiaison &"'
  AND  [Source] Like  '"*& Me.txtFilterSource &“’
  AND  [Program] Like '"* & Me.cboFilterProgram & “’ 
  AND  [Status] Like ‘”* & Me.cboFilterStatus & “’
  AND  [Due_Date]>='"&Format(Me.txtStartFilterDate, conJetDate )& “’
  AND  [Due_Date] <  '"& Format(Me.txtEndFilterDate + 1, conJetDate) “’
  AND  [Program] Like '"* & Me.cboFilterProgram & “’

[patjones]

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.

SELECT tblOpenActionItems.* 
FROM tblOpenActionItems 
WHERE tblOpenActionItems.[Liaison_Contact_Information] = '" & Me.txtFilterLiaison & "' AND             
      tblOpenActionItems.[Source] Like '*" & Me.txtFilterSource & "' AND  
      tblOpenActionItems.[Program] Like '*" & Me.cboFilterProgram & "' AND 
      tblOpenActionItems.[Status] Like '*" & Me.cboFilterStatus & "' AND 
      tblOpenActionItems.[Due_Date] >= '" & Format(Me.txtStartFilterDate, conJetDate ) & "' AND               
      tblOpenActionItems.[Due_Date] <  '" & Format(Me.txtEndFilterDate + 1, conJetDate)& "' AND             
      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

[Qtip23]

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:

=[Forms]![ASaP_AI_Form_Template_5]![Date Complete] 

Thanks!

[patjones]

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

[NeoPa]

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!

[NeoPa]

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.

[patjones]

@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

[Qtip23]

@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!

[NeoPa]

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.

[Qtip23]

@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:

'Source: http://allenbrowne.com
'Adapted from: http://allenbrowne.com
 
Option Compare Database
Option Explicit
 
Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click
'Purpose:   This module illustrates how to create a search form, _
            where the user can enter as many or few criteria as they wish, _
            and results are shown one per line.
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne ([e-mail]), June 2006.
'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
      'Liaison's Name Text field. Use quotes around the value in the string.
     If Not IsNull(Me.txtFilterLiaison) Then
        'strWhere = "Like '*'"
    'Else
        strWhere = strWhere & "([Liaison_Contact_Information] = """ & Me.txtFilterLiaison & """) AND "
    End If
 
    'Source Text field. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterSource) Then
      '  strWhere = "Like '*'"
    'Else
        strWhere = strWhere & "([Source] Like ""*" & Me.txtFilterSource & "*"") AND "
    End If
 
 
    'Program Field. Do not add the extra quotes.
     If Not IsNull(Me.cboFilterProgram) Then
      '  strWhere = "Like '*'"
    'Else
        strWhere = strWhere & "([Program] Like ""*" & Me.cboFilterProgram & "*"") AND "
    End If
 
     'Status Field. Do not add the extra quotes.
    If Not IsNull(Me.cboFilterStatus) Then
     '   strWhere = "Like '*'"
    'Else
        strWhere = strWhere & "([Status] Like ""*" & Me.cboFilterStatus & "*"") AND "
    End If
 
 
    'Due Date - Start Date field. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtStartFilterDate) Then
     '   strWhere = "Like '*'"
    'Else
        strWhere = strWhere & "([Due_Date] >= " & Format(Me.txtStartFilterDate, conJetDate) & ") AND "
    End If
 
    'Due Date - End Date field. Use "less than the next day" since this field has times as well as dates.
     If Not IsNull(Me.txtEndFilterDate) Then
     '   strWhere = "Like '*'"
    'Else
        strWhere = strWhere & "([Due_Date] < " & Format(Me.txtEndFilterDate + 1, conJetDate) & ") AND "
    End If
 
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
 
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
 
 
Exit_cmdFilter_Click:
    Exit Sub
 
Err_cmdFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdFilter_Click
 
 
 
End Sub
 
 
 
Private Sub cmdReset_Click()
On Error GoTo Err_cmdReset_Click
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
 
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
 
    'Remove the form's filter.
    Me.FilterOn = False
 
Exit_cmdReset_Click:
    Exit Sub
 
Err_cmdReset_Click:
    MsgBox Err.Description
    Resume Exit_cmdReset_Click
 
 
 
 
End Sub
 
Private Sub Command142_Click()
On Error GoTo Err_Command142_Click
 
    Dim stDocName As String
    Dim strWhere As String                  'The criteria string.
 
 
    stDocName = "rptAll_CBP_Action_Items"
    DoCmd.OpenReport stDocName, acViewPreview
Exit_Command142_Click:
    Exit Sub
 
Err_Command142_Click:
    MsgBox Err.Description
    Resume Exit_Command142_Click
End Sub
 
Private Sub Command44_Click()
On Error GoTo Err_Command44_Click
 
 
    DoCmd.Close
 
Exit_Command44_Click:
    Exit Sub
 
Err_Command44_Click:
    MsgBox Err.Description
    Resume Exit_Command44_Click
 
End Sub
 
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_BeforeInsert
 
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
 
 
 
Exit_Form_BeforeInsert:
    Exit Sub
 
Err_Form_BeforeInsert:
    MsgBox Err.Description
    Resume Exit_Form_BeforeInsert
 
 
 
End Sub
 
Private Sub Command139_Click()
On Error GoTo Err_Command139_Click
 
    Dim stDocName As String
    Dim strWhere As String                  'The criteria string.
 
    stDocName = "rptActionItems_Filtered_TurnBack on"
      ''  If SysCmd(acSysCmdGetObjectState, acReport, stDocName) <> acObjStateOpen Then
      ''  MsgBox "You must open the report first."
 
    DoCmd.OpenReport stDocName, acViewReport, strWhere
    'DoCmd.OpenReport stDocName, acViewReport, , strWhere
    'DoCmd.OpenReport stDocName, acViewPreview, strWhere
    'DoCmd.OpenReport stDocName, acViewPreview, strWhere
 
 
    '' End If
Exit_Command139_Click:
    Exit Sub
 
Err_Command139_Click:
    MsgBox Err.Description
    Resume Exit_Command139_Click
 
End Sub
 

I am really learning alot and appreciate all the great feedback from a novice like myself!

[Qtip23]

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!