By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Help on trouble handling date.

100+
P: 759
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 ?!?!?


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim MyFilter As String
  5.  
  6. Private Sub ID_Nod_DblClick(Cancel As Integer)
  7.     Call UpdateFilter("ID_Nod", Me.ID_Nod, "Number")
  8. End Sub
  9.  
  10. Private Sub Nod_DblClick(Cancel As Integer)
  11.     Call UpdateFilter("Nod", Me.Nod, "Text")
  12. End Sub
  13.  
  14. Private Sub AreContract_DblClick(Cancel As Integer)
  15.     Me.AreContract = Not (Me.AreContract) 'Restore value after first click
  16.     Call UpdateFilter("AreContract", Me.AreContract, "Yes/No")
  17. End Sub
  18.  
  19. Private Sub RecData_DblClick(Cancel As Integer)
  20.     Call UpdateFilter("RecData", Me.RecData, "Date")
  21. End Sub
  22.  
  23.  
  24.  
  25. Private Sub UpdateFilter(MyField As String, WithThisValue, FieldType As String)
  26. Dim dtData As Date
  27. Dim FixFilter As String
  28.  
  29.     Select Case FieldType
  30.         Case "Number"
  31.             FixFilter = "([" & MyField & "] = " & WithThisValue & ")"
  32.         Case "Text"
  33.             FixFilter = "([" & MyField & "] = """ & WithThisValue & """)"
  34.         Case "Yes/No"
  35.             ' Same as case "Number"
  36.             FixFilter = "([" & MyField & "] = " & WithThisValue & ")"
  37.         Case "Date"
  38.             FixFilter = "([" & MyField & "] = " & WithThisValue & ")"
  39.             FixFilter = "([" & MyField & "] = #" & WithThisValue & "#)"
  40.             FixFilter = "([" & MyField & "] = " & "#" & Format(CDate(WithThisValue), "mm/dd/yyyy") & "#)"
  41.          Case Else
  42.             Stop
  43.     End Select
  44.  
  45.     If MyFilter = "" Then
  46.         MyFilter = FixFilter
  47.     Else
  48.         MyFilter = MyFilter & " AND " & FixFilter
  49.     End If
  50.  
  51.     Me.Filter = MyFilter
  52.     Me.FilterOn = True
  53. End Sub
  54.  
  55. Private Sub cmdResetFilter_Click()
  56.     MyFilter = ""
  57.     Me.FilterOn = False
  58. End Sub
Nov 29 '11 #1

✓ answered by Mihail

First solution (TheSmileyCoder)
Expand|Select|Wrap|Line Numbers
  1. FixFilter = "([" & MyField & "] = " & formatSQLDate(dtInput) & ")"
  2.  
  3. Public Function formatSQLDate(dtInput As Date) As String
  4.     formatSQLDate = Format(dtInput, "\#m\/d\/yyyy\#")
  5. End Function


Second solution (NeoPa)
Expand|Select|Wrap|Line Numbers
  1. FixFilter = "([" & MyField & "] = #" & Format(CDate(dtInput), "m\/d\/yyyy") & "#)"

Thank you !

Share this Question
Share on Google+
8 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 29 '11 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
I wrote some example code for you, on another way to handle your general issue, albeit a bit simpler to reuse
Expand|Select|Wrap|Line Numbers
  1. Public Sub UpdateFilter()
  2.     'Other variables
  3.     Dim strFilter As String
  4.  
  5.     'Get current control
  6.         Dim ctrl As Control
  7.         Set ctrl = Screen.ActiveControl
  8.  
  9.     'Check that control has a controlsource (is bound)
  10.         If ctrl.ControlSource & "" = "" Then
  11.             Exit Sub
  12.         End If
  13.  
  14.     'Get current form
  15.         Dim myFrm As Form
  16.         Set myFrm = ctrl.Parent
  17.  
  18.     'Get the underlying field for that control
  19.         Dim myField As DAo.Field
  20.         Set myField = myFrm.Recordset.Fields(ctrl.ControlSource)
  21.  
  22.     'Check that control has a value to sort on
  23.         If IsNull(ctrl.Value) Then
  24.             strFilter = "[" & myField.Name & "] is Null"
  25.         Else
  26.             'Determine field type
  27.             Select Case myField.Type
  28.                 Case dbLong, dbint, dbByte, dbBoolean, dbDouble, dbDecimal, dbFloat, dbSingle
  29.                     strFilter = "[" & myField.Name & "]=" & ctrl.Value
  30.  
  31.                 Case dbDate, dbTime
  32.                     strFilter = "[" & myField.Name & "]=#" & ctrl.Value & "#"
  33.  
  34.                 Case dbText, dbMemo, dbChar
  35.                     strFilter = "[" & myField.Name & "]='" & ctrl.Value & "'"
  36.  
  37.                 Case Else
  38.                     'There are a few more datatypes out there.
  39.  
  40.             End Select
  41.  
  42.         End If
  43.  
  44.     If myFrm.Filter & "" <> "" And myFrm.FilterOn = True Then
  45.         myFrm.Filter = myFrm.Filter & " AND (" & strFilter & ")"
  46.     Else
  47.         myFrm.Filter = "(" & strFilter & ")"
  48.     End If
  49. 'cleanup
  50.     Set ctrl = Nothing
  51.     Set myFrm = Nothing
  52.     Set myField = Nothing
  53. 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.
Nov 29 '11 #3

100+
P: 759
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.
Nov 29 '11 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Case "Date" 
  2.             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.
Nov 30 '11 #5

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 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?
Dec 2 '11 #6

100+
P: 759
Thank you !
Dec 2 '11 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 2 '11 #8

100+
P: 759
First solution (TheSmileyCoder)
Expand|Select|Wrap|Line Numbers
  1. FixFilter = "([" & MyField & "] = " & formatSQLDate(dtInput) & ")"
  2.  
  3. Public Function formatSQLDate(dtInput As Date) As String
  4.     formatSQLDate = Format(dtInput, "\#m\/d\/yyyy\#")
  5. End Function


Second solution (NeoPa)
Expand|Select|Wrap|Line Numbers
  1. FixFilter = "([" & MyField & "] = #" & Format(CDate(dtInput), "m\/d\/yyyy") & "#)"

Thank you !
Dec 2 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.