Search Form errors for date.

[oxicottin]

Hello, im having trouble with this search forms Where. Sometimes it gives me the results sometimes I get an error.. I want to call starttDate and give me results from that date and same with end date, I want end date to give me results to that date.

I call function using

Me.RecordSource = "SELECT * FROM qry_AdvancedSearch " & BuildFilter

Here is the code for the search form.

'*********************************FILTER SEARCH START*********************************
    
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    varWhere = Null  ' Main filter
    
'*************************************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'*************************************************************************************
'Employee
    If Not IsNull(Me.cboEmployees) Then
        varWhere = varWhere & "[EmployeeID] = " & Me.cboEmployees & " AND "
    End If
'Line
    If Not IsNull(Me.cboLine) Then
        varWhere = varWhere & "[MachineID] = " & Me.cboLine & " AND "
    End If
'Lengh
    If Not IsNull(Me.cboLength) Then
        varWhere = varWhere & "[Length] = '" & Replace(Me.cboLength, "'", "''") & "' AND "
    End If
    
'Check for LIKE in Keyword Search
    Me.txtProductionContains.SetFocus
    If Me.txtProductionContains > "" Then
        varWhere = varWhere & "[ProductionProblems] LIKE ""*" & Me.txtProductionContains & "*"" AND "
    End If
'Product
    If Not IsNull(Me.cboProduct) Then
        varWhere = varWhere & "[ProductID] = " & Me.cboProduct & " AND "
    End If
'Start date
'    If Not IsNull(Me.txtStartDate) Then
'        varWhere = varWhere & "([ShiftDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
'    End If

'End Date
'    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
'        varWhere = varWhere & "([ShiftDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
'    End If
    
' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
' msg if no data
'MsgBox "No criteria", vbInformation, "Nothing to do."
        Me.FilterOn = True
    Else
        varWhere = "WHERE " & varWhere
        
' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function
    
'
'*********************************FILTER SEARCH END***********************************

 

[plog]

Sometimes it gives me the results sometimes I get an error.

You make it sound completely random, I seriously doubt that. You need to find out what conditions are causing errors. What data are you putting into it that causes an error?

Then you need to find out what exactly your SQL looks like in those conditions. You're building an SQL string, correct? What exactly is in that string? Don't just look at how its built and assume, actually find out whats in it. Then, paste it into a new query object and see if it will run. If not, you've incorrectly built SQL and need to fix the part of your code that built it incorrectly.

Lastly, there's no need to format dates in the WHERE. When you want to build criteria using dates you should instead use the hash tag (#) around them:

Examples of using dates as criteria in Access queries - Microsoft Support

When entering dates as query criteria, enclose them in pound signs (#) to help Access understand what they are.

support.office.com

 

[oxicottin]

Most of the time It brings me to the "End Date" even if I don't use it as a search criteria.

 

[plog]

What is "it"? I gave you a few tips.

 

[Deleted member 147267]

When you post saying there is an error, provide that info - the number and message because it's a vital clue. Otherwise we're looking for any of a multitude of possibilities and there you are with pin point info that you're not sharing. Also, output the sql to the immediate window and if it's not obvious what the issue is, copy and paste into a new query sql view as suggested.
If you can do this
If Me.txtProductionContains > ""
instead of this
If Len(Me.txtProductionContains) > 0 or
Nz(Me.ProductContains,0) > 0
I was not aware - I expect the result of your expression to be Null if the control has no value, not True or False. Perhaps that is your problem. Regardless, the approach given is a good one to learn as it will probably result in you finding your own solutions much faster.

EDIT - I made post edits. Also, make sure you understand the difference between zls ("") and Null.

 

[Minty]

In addition to the above, check your combo box values.
Often if they are returning a number value (e.g. the Primary key of a lookup), then they won't be null they will be 0, so checking for null as a check for nothing selected won't work.

 

[Cronk]

I suggest you trap the error with
On error goto ...

In the error handling routine you can add the line
Resume

That will take you back to the line on which the error happened and you can examine values of controls and variables

 

[oxicottin]

I added error trap in my function and I get the error

Type Mismatch in BuildFilter

'*********************************FILTER SEARCH START*********************************
   
Private Function BuildFilter() As Variant
On Error GoTo BuildFilter_Err

    Dim varWhere As Variant
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"
   
    varWhere = Null  ' Main filter
   
'Employee
    If Not IsNull(Me.cboEmployees) Then
        varWhere = varWhere & "[EmployeeID] = " & Me.cboEmployees & " AND "
    End If
'Line
    If Not IsNull(Me.cboLine) Then
        varWhere = varWhere & "[MachineID] = " & Me.cboLine & " AND "
    End If
'Lengh
    If Not IsNull(Me.cboLength) Then
        varWhere = varWhere & "[Length] = '" & Replace(Me.cboLength, "'", "''") & "' AND "
    End If
   
'Check for LIKE in Keyword Search
    Me.txtProductionContains.SetFocus
    If Me.txtProductionContains > "" Then
        varWhere = varWhere & "[ProductionProblems] LIKE ""*" & Me.txtProductionContains & "*"" AND "
    End If
'Product
    If Not IsNull(Me.cboProduct) Then
        varWhere = varWhere & "[ProductID] = " & Me.cboProduct & " AND "
    End If
'Start date
    If Not IsNull(Me.txtStartDate) Then
        varWhere = varWhere & "([ShiftDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If

'End Date
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        varWhere = varWhere & "([ShiftDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If
   
' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
'Message if no data
'MsgBox "No criteria", vbInformation, "Nothing to do."
        Me.FilterOn = True
    Else
        varWhere = "WHERE " & varWhere
       
'Strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
   
    BuildFilter = varWhere
   
BuildFilter_Exit:
Exit Function
BuildFilter_Err:
MsgBox Err.Description & " in BuildFilter"
Resume BuildFilter_Exit

End Function
   
'
'*********************************FILTER SEARCH END***********************************

 

[Gasman]

So debug.printthe filter string at various sections in the code?
Even build each new portion to another string, debug.print that THEN append to filter string?

 

[oxicottin]

I added the debug and If I comment out the start and end date everything works fine but as soon as I uncomment one or the other I get the Type Mismatch in BuildFilter. I look at the immediate window and there is criteria and even if I don't use the date it still has AND ([ShiftDate] >= ) AND when it should read null right?

 

[Gasman]

You should not even have that clause if you are not using dates?
You need to build the string only with data you have.?

 

[Deleted member 147267]

Then your test for Null is failing because it's not Null? Is the date field(s) an actual date or is it text data? My guess is text. If it is, then create a new query with a few fields besides the date field(s) where you will put "" as criteria. Don't put criteria in any other field. Run it. If you get any records, your date fields have one or more records with zero length strings in them. In that case, you may want to run an update query to convert all "" to Null.

I never code to trust that a field will be only null or not. Something like
If Nz(theField,"") = "" Then

 

[smig]

I added the debug and If I comment out the start and end date everything works fine but as soon as I uncomment one or the other I get the Type Mismatch in BuildFilter. I look at the immediate window and there is criteria and even if I don't use the date it still has AND ([ShiftDate] >= ) AND when it should read null right?

A date can't be compared to Null value
try to replace the Null value with something like 01.01.1900

 

[Deleted member 147267]

Where is a date being compared to Null? I can see where a control that might or might not contain a date is being checked for Null but the check is on the control value?

 

[Cronk]

oxicottin, I suggest you add error handling to catch any error. In the error handling code, have a Stop and Resume line to take you back to the line causing the error. You can single line step to examine the value of the control or the filter string whenever an error occurs.

 

[oxicottin]

By getting rid of the Not IsNull and adding <> "" to the below it resolved the error... Thanks!

    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Me.txtStartDate <> "" Then
        strWhere = strWhere & "([ShiftDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Me.txtEndDate <> "" Then    'Less than the next day.
        strWhere = strWhere & "([ShiftDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If

 

[Deleted member 147267]

I don't know if you saw my edit in post 5, but there and in post 12 I suggested that you make sure you understood and checked for null vs empty string ("" , aka zls). Not sure if you saw those or just figured it out on your own...
I don't think you can go wrong with
If Nz(someField,"") = "" Then (or the opposite would be If Not...)