[Access - VBA] Searching Between Two Dates
Solved
Hello,
I am currently developing a small database in Access.
I created a table in SQL Server in which I have a DateIntervention field.
When I create a linked table in Access for this table, it retrieves the date in YYYY-MM-DD format.
As a result, to display the date in the desired format, I use a query in which I specify in SQL: Format([my_linked_table].[DateIntervention];'dd/mm/yyyy'). The field is named Expr1.
So far, everything is fine (normally!).
Then I created a search form in which I search between two dates (TxT_DateDebut and TxT_DateFin). Here is the syntax of my "formula" (found on the internet, by the way):
Private Sub Btn_Filtre_Click()
f = ""
If Not IsNull(Me.TxT_DateDebut) And Me.TxT_DateDebut <> "" Then
f = "CLng([Expr1]) BETWEEN " & CLng(Me.TxT_DateDebut) & " AND " & CLng(Me.TxT_DateFin) & ""
End If
Me.Filter = f
Me.FilterOn = True
End Sub
My problem is that when I click on the button linked to this code, it shows me the error code "3464: type mismatch in criteria expression"...
Thank you in advance for your help!
Configuration: Windows 7 / Safari 535.19
I am currently developing a small database in Access.
I created a table in SQL Server in which I have a DateIntervention field.
When I create a linked table in Access for this table, it retrieves the date in YYYY-MM-DD format.
As a result, to display the date in the desired format, I use a query in which I specify in SQL: Format([my_linked_table].[DateIntervention];'dd/mm/yyyy'). The field is named Expr1.
So far, everything is fine (normally!).
Then I created a search form in which I search between two dates (TxT_DateDebut and TxT_DateFin). Here is the syntax of my "formula" (found on the internet, by the way):
Private Sub Btn_Filtre_Click()
f = ""
If Not IsNull(Me.TxT_DateDebut) And Me.TxT_DateDebut <> "" Then
f = "CLng([Expr1]) BETWEEN " & CLng(Me.TxT_DateDebut) & " AND " & CLng(Me.TxT_DateFin) & ""
End If
Me.Filter = f
Me.FilterOn = True
End Sub
My problem is that when I click on the button linked to this code, it shows me the error code "3464: type mismatch in criteria expression"...
Thank you in advance for your help!
Configuration: Windows 7 / Safari 535.19
3 answers
-
For your information, I found it! After several days of searching.....
Cdate([Expr1]) BETWEEN #" & CDate(Me.TxT_DateDebut) & "# AND #" & CDate(Me.TxT_DateFin) & "#"
Apparently, it did not consider my dates as dates.... Now it works!
Thank you for your ideas. -
ContributorHello,
Since it's expected text, have you tried passing the date as such?
"17/03/2012" or "2012/03/17"
And to convert a date, datevalue(your date in text) works well too.
Otherwise, in VBA Excel, in some cases, to represent a date you enclose it in #, maybe that's what it's expecting too: #17/03/2012#
eric
eric-
Hello, thank you for your response.
However, there is a new error: 3075: Syntax error in date in the expression "CLng([Expr1]) BETWEEN #40544# AND #40908"
What’s strange is that the error message is missing the last hash, while when I hover over my variable "f" in debug mode, it correctly shows the formula: f="CLng([Expr1]) BETWEEN #40544# AND #40908#" -
-
-
-
-
-
ContributorHello
An example of a query between two dates from a form (F_relance) that works:
Between [forms]![F_relance]![txt_datedeb] And [forms]![F_relance]![txt_datefin]
--
Michel
