Help on trouble handling date.

[Mihail]

I try to develop a simple filter for a form bound to a table using _DblClick event for form's controls.

Except I don't know how to determine the field data type, everything is fine... :) until I try to handle the date data type.

Again and again the same error in line 52:
Run-time error in date in query expression.
The (string) value for MyFilter is ([RecData] = #11.17.2011#) (when the error raise)

What is wrong here ?!?!?

Option Compare Database
Option Explicit
 
Dim MyFilter As String
 
Private Sub ID_Nod_DblClick(Cancel As Integer)
    Call UpdateFilter("ID_Nod", Me.ID_Nod, "Number")
End Sub
 
Private Sub Nod_DblClick(Cancel As Integer)
    Call UpdateFilter("Nod", Me.Nod, "Text")
End Sub
 
Private Sub AreContract_DblClick(Cancel As Integer)
    Me.AreContract = Not (Me.AreContract) 'Restore value after first click
    Call UpdateFilter("AreContract", Me.AreContract, "Yes/No")
End Sub
 
Private Sub RecData_DblClick(Cancel As Integer)
    Call UpdateFilter("RecData", Me.RecData, "Date")
End Sub
 
 
 
Private Sub UpdateFilter(MyField As String, WithThisValue, FieldType As String)
Dim dtData As Date
Dim FixFilter As String
 
    Select Case FieldType
        Case "Number"
            FixFilter = "([" & MyField & "] = " & WithThisValue & ")"
        Case "Text"
            FixFilter = "([" & MyField & "] = """ & WithThisValue & """)"
        Case "Yes/No"
            ' Same as case "Number"
            FixFilter = "([" & MyField & "] = " & WithThisValue & ")"
        Case "Date"
            FixFilter = "([" & MyField & "] = " & WithThisValue & ")"
            FixFilter = "([" & MyField & "] = #" & WithThisValue & "#)"
            FixFilter = "([" & MyField & "] = " & "#" & Format(CDate(WithThisValue), "mm/dd/yyyy") & "#)"
         Case Else
            Stop
    End Select
 
    If MyFilter = "" Then
        MyFilter = FixFilter
    Else
        MyFilter = MyFilter & " AND " & FixFilter
    End If
 
    Me.Filter = MyFilter
    Me.FilterOn = True
End Sub
 
Private Sub cmdResetFilter_Click()
    MyFilter = ""
    Me.FilterOn = False
End Sub

[TheSmileyCoder]

Im quite confused with the code you have posted. You write the date 3 times in your filter, the last 2 times simply overwriting the first. Im going to presume thats because you have tried several times.

Also the code you posted:

FixFilter = "([" & MyField & "] = " & "#" & Format(CDate(WithThisValue), "mm/dd/yyyy") & "#)"

I see that the example you posted there is . between the month/date/year values. Is that a regional setting way of expressing dates?


My initial guess would be that you have either spelled RecData wrong in either your code or your table, or that your RecData field is not a date type field.

[TheSmileyCoder]

I wrote some example code for you, on another way to handle your general issue, albeit a bit simpler to reuse

Public Sub UpdateFilter()
    'Other variables
    Dim strFilter As String
 
    'Get current control
        Dim ctrl As Control
        Set ctrl = Screen.ActiveControl
 
    'Check that control has a controlsource (is bound)
        If ctrl.ControlSource & "" = "" Then
            Exit Sub
        End If
 
    'Get current form
        Dim myFrm As Form
        Set myFrm = ctrl.Parent
 
    'Get the underlying field for that control
        Dim myField As DAo.Field
        Set myField = myFrm.Recordset.Fields(ctrl.ControlSource)
 
    'Check that control has a value to sort on
        If IsNull(ctrl.Value) Then
            strFilter = "[" & myField.Name & "] is Null"
        Else
            'Determine field type
            Select Case myField.Type
                Case dbLong, dbint, dbByte, dbBoolean, dbDouble, dbDecimal, dbFloat, dbSingle
                    strFilter = "[" & myField.Name & "]=" & ctrl.Value
 
                Case dbDate, dbTime
                    strFilter = "[" & myField.Name & "]=#" & ctrl.Value & "#"
 
                Case dbText, dbMemo, dbChar
                    strFilter = "[" & myField.Name & "]='" & ctrl.Value & "'"
 
                Case Else
                    'There are a few more datatypes out there.
 
            End Select
 
        End If
 
    If myFrm.Filter & "" <> "" And myFrm.FilterOn = True Then
        myFrm.Filter = myFrm.Filter & " AND (" & strFilter & ")"
    Else
        myFrm.Filter = "(" & strFilter & ")"
    End If
'cleanup
    Set ctrl = Nothing
    Set myFrm = Nothing
    Set myField = Nothing
End Sub

If you look at your controls event, then instead of writing [Event Procedure] under Doubleclick, you can simply write =UpdateFilter.

Notes:
This of course only works for bound fields.

Note that if the user is editing the field before doubleclicking it, you may need to use ctrl.text instead of ctrl.value.

[Mihail]

Thank you Smiley for code.
I copy it and paste in my own computer in order to use it.

No. Field name [RecData] is Ok. (Copy-Paste)

Yes. I try 3 different lines of code. With the same result: the error I describe.

I read somewhere (maybe even in NeoPa's article) that Access work better with dates in American format. So just I try this way.

The field in the table is formated as DATE.

The form is created, fully automatic, by Access.
My changes are ONLY in form layout.

I don't know how to use =UpdateFilter. I'll try to teach myself about.

I show you the value assigned to the Form.Filter : ([RecData] = #11.17.2011#)
I can't see anything wrong here (except the error, of course :)) )
For me the filter string looks like in books.

I use a query to filter by date.
I type 17.11.2011, Access automatic transform that in #17.11.2011# and the query work fine.

Note, please, that I can't add the same filter to the form in design mode.



P.S.
No problem if you decide that my post must be the start for a new thread. I think you think better.

[TheSmileyCoder]

I have had a similar problem at times, when using doubles. In my regional setting a double is written as 76,1231 whereas access want it to be 76.1231. In the end my solution was to simply use the Replace function for example: Replace("76,1231",",",".")
or in your example:

Case "Date" 
            FixFilter = "([" & MyField & "] = " & "#" & replace(Format(CDate(WithThisValue), "mm/dd/yyyy"),".","/") & "#)"

Now as to why this is required I haven't really done enough research to say anything with certainty.
It seems your regional settings is set up to write dates with a period seperating the day/month/values, for me its a dash -. While access can interpret the dashes without problems, Im gussing the periods is what is causing it grief.

I was able to reproduce your error if I forced the date to be written with periods instead of dashes.

[NeoPa]

I will point you in what I think is the right direction and Smiley can add a comment if he feels it might be helpful to show a different perspective (He prefers the idea of a function call to handle the date formatting, which I don't, but I wouldn't argue against it as there is some merit to it).

Anyway, the issue in your code is introduced in lines #38 to #40 (even though it only actually falls over on line #52). It should go away if you change it to the following though :

FixFilter = "([" & MyField & "] = #" & Format(CDate(WithThisValue), "m\/d\/yyyy") & "#)"

You were suffering from a problem where Format(), uses your Regional Settings to change the format string you actually entered ("mm/dd/yyyy") into a local version of the same string ("mm.dd.yyyy"), which is exactly the opposite of what you needed when working with SQL strings.

Does that sort you out?

[Mihail]

Thank you !

[NeoPa]

No problem Mihail :-)

I have now removed that other discussion, which was sort of relevant really, but is more use in a separate thread I think, to Discussion: SQL Date Literals and Regional Settings. That way people can still see it, but it doesn't confuse this thread so much.

[Mihail]

First solution (TheSmileyCoder)

FixFilter = "([" & MyField & "] = " & formatSQLDate(dtInput) & ")"
 
Public Function formatSQLDate(dtInput As Date) As String
    formatSQLDate = Format(dtInput, "\#m\/d\/yyyy\#")
End Function

Second solution (NeoPa)

FixFilter = "([" & MyField & "] = #" & Format(CDate(dtInput), "m\/d\/yyyy") & "#)"

Thank you !