View original (French)

[Access - VBA] Searching Between Two Dates

Solved
DecK -  
 DecK -
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

3 answers

  1. DecK
     
    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.
    2
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    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
    0
    1. DecK
       
      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#"
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      I would have kept the date in the format date #dd/mm/yyyy# or #yyyy/mm/dd# while dropping the cnum()
      eric
      0
    3. DecK
       
      No more mistakes, but it doesn't filter anything at all :(
      0
    4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      I think you tried #dd/mm/yyyy# and #yyyy/mm/dd#, I can't help you any further, sorry
      eric
      0
    5. DecK
       
      Thank you anyway for your help!
      0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello

    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
    0
    2. DecK
       
      Hello Michel-m
      Thank you for your response.
      The problem is that I already have a form with fields to fill out; I had indeed considered doing something like that, but it's not in my specifications :)
      Thanks anyway.
      0