Search Form errors for date. (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 16:34
Joined
Jun 26, 2007
Messages
851
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.

Code:
'*********************************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

Banishment Pending
Local time
Today, 15:34
Joined
May 11, 2011
Messages
11,611
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:

 

oxicottin

Learning by pecking away....
Local time
Today, 16:34
Joined
Jun 26, 2007
Messages
851
Most of the time It brings me to the "End Date" even if I don't use it as a search criteria.
 

plog

Banishment Pending
Local time
Today, 15:34
Joined
May 11, 2011
Messages
11,611
What is "it"? I gave you a few tips.
 

Micron

AWF VIP
Local time
Today, 16:34
Joined
Oct 20, 2018
Messages
3,476
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

AWF VIP
Local time
Today, 20:34
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Tomorrow, 07:34
Joined
Jul 4, 2013
Messages
2,770
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

Learning by pecking away....
Local time
Today, 16:34
Joined
Jun 26, 2007
Messages
851
I added error trap in my function and I get the error

Type Mismatch in BuildFilter

Code:
'*********************************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

Enthusiastic Amateur
Local time
Today, 20:34
Joined
Sep 21, 2011
Messages
14,047
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

Learning by pecking away....
Local time
Today, 16:34
Joined
Jun 26, 2007
Messages
851
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

Enthusiastic Amateur
Local time
Today, 20:34
Joined
Sep 21, 2011
Messages
14,047
You should not even have that clause if you are not using dates?
You need to build the string only with data you have.?
 

Micron

AWF VIP
Local time
Today, 16:34
Joined
Oct 20, 2018
Messages
3,476
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

Registered User.
Local time
Today, 22:34
Joined
Nov 25, 2009
Messages
2,209
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
 

Micron

AWF VIP
Local time
Today, 16:34
Joined
Oct 20, 2018
Messages
3,476
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

Registered User.
Local time
Tomorrow, 07:34
Joined
Jul 4, 2013
Messages
2,770
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

Learning by pecking away....
Local time
Today, 16:34
Joined
Jun 26, 2007
Messages
851
By getting rid of the Not IsNull and adding <> "" to the below it resolved the error... Thanks!

Code:
    '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
 

Micron

AWF VIP
Local time
Today, 16:34
Joined
Oct 20, 2018
Messages
3,476
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...)
 

Users who are viewing this thread

Top Bottom