Question Help with running a search

[josephbupe]

Please, open the form "F_Training_Main"

I am having some problem when, for instance, i query for "South Africa" as a Venue, initially that will return three records, click "Search" button again will return nothing, click "Search" again will return several records.

The same thing happens when I query for "France" as a venue.

And again, my date queries aren't accurate. I tried start date: "01/01/2011" end date: "20/01/2011" but ended up with wrong records being returned.

I am utilizing the following code to run queries:

Private Sub cmdGo_Click()
    Dim strField As String 'Name of your date field.
    Dim strMessage As String 'Message on no data for selection.
    Dim varWhere As String 'Where condition for OpenReport.
On Error GoTo Err_cmdGo_Click

    ' Const conDateFormat = "\#mm\/dd\/yyyy\#"
    Const conDateFormat = "\#dd\/mm\/yyyy\#"

     strReport = "rpt"
    strField = "[Start Date]"
    varWhere = ""
    ' ================================================================================
    ' CHECK FOR DATE RANGE
    ' ================================================================================
    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then  'End date, but no start.
            varWhere = "(" & strField & " <= " & Format(Me.txtEndDate, conDateFormat) & ") "
        Else
            'neither start nor end dates, do nothing to varWhere
        End If
    Else
        If IsNull(Me.txtEndDate) Then  'Start date, but no End.
            varWhere = "(" & strField & " >= " & Format(Me.txtStartDate, conDateFormat) & ") "
   '     Else 'Both start and end dates present.
   ElseIf IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then 'Both start and end dates present.
            varWhere = "(" & strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
            & " AND " & Format(Me.txtEndDate, conDateFormat) & ")"
        End If
    End If
    ' =================================================================================
      
      Dim strSQL As String
           
    If Me.Programme <> "" Then
        strSQL = strSQL & "[Programme Name] Like '*" & Me.Programme & "*' AND "
    End If
           
    If Me.txtVenue <> "" Then
        strSQL = strSQL & "[Country] Like '*" & Me.txtVenue & "*'"     ' AND "
    End If
    
    If Trim(strSQL) = "" Then
        strSQL = "False"
    End If
    
    'Drop the last " AND " from the string
    If Right(strSQL, 5) = " AND " Then
        strSQL = Left(strSQL, Len(strSQL) - 5)
    End If
    
    'next add the where to the front of the string if the user has entered something in at least one field
    If strSQL <> "False" Then
        If varWhere = "" Then
            varWhere = strSQL
        Else
            varWhere = varWhere & " AND " & strSQL
        End If
    End If

 '================================================================================
    
    Debug.Print varWhere
    
    If varWhere <> "" Then
        'Update the record source
       Me.RecordSource = "SELECT * FROM Q_Training_Programmes_CrossTab WHERE " & varWhere
    Else
        'Update the record source
        Me.RecordSource = "SELECT * FROM Q_Training_Programmes_CrossTab"
       
    End If
    
     Me.Requery
   
Exit_cmdGo_Click:
   Exit Sub

Err_cmdGo_Click:
    MsgBox "You did not fill in any data", vbCritical, "No data"
    Resume Exit_cmdGo_Click
End Sub

I have attached my database for easy reference.

I will appreciate your help.

Regards.

Joseph

 

[spikepl]

Any reason for changing the correct value to yours?

' Const conDateFormat = "\#mm\/dd\/yyyy\#"     
Const conDateFormat = "\#dd\/mm\/yyyy\#"

It messes up the date searches

Did you look at the output of your
Debug.Print varWhere
after each search?