Search dates between two Text Fields (1 Viewer)

[StyxLykin]

Hi, I would like to search between to text fields. [AbsenceFrom] and [AbsenceTo]

The code is
Option Compare Database

Private Sub Befehl21_Click()

Call Search

End Sub

Sub Search()

Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.DateFrom) Or IsNull(Me.DateTo) Then
MsgBox "Please Enter Date Range", vbInformation, "Date Range Required"
Me.DateFrom.SetFocus
Else
strCriteria = "([AbsenceFrom] >= #" & Me.DateFrom & "# AND [AbsenceTo] <= #" & Me.DateTo & "#)"
task = "select * from Tabelle1 where (" & strCriteria & ") order by [AbsenceFrom]"
DoCmd.ApplyFilter task


End If


End Sub


I always receive an error with the dates. attached is the database with the code

Help would be awesome so I understand what is going wrong. I am new in VBA

 

[Gasman]

I always use Between when having two dates.
Also in mm/dd/yyyy format as I am in the UK

Always usefule to say what the error is as well?

 

[jdraw]

?? worked for me with Dates Apr 1 21 thru May 31 21

NOTE: REvised dates to match my test.

 

[StyxLykin]

I am in Germany and the date should be dd/mm/yyyy - The Error is

 

[StyxLykin]

Just attaching a screenshot from the table

 

[StyxLykin]

?? worked for me with Dates Aug 1 21 thru May 30 21

even if I change the dates like 01.05.2021 - 01.08.2021 I am getting an error

 

[Gasman]

Try this.
I use a constant for formatting correctly as well. I also set my filters as below.

In a module I have

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"

Then in your form

Sub Search()

Sub Search()

    Dim strCriteria, task As String

    Me.Refresh
    If IsNull(Me.DateFrom) Or IsNull(Me.DateTo) Then
        MsgBox "Please Enter Date Range", vbInformation, "Date Range Required"
        Me.DateFrom.SetFocus
    Else
        strCriteria = "[AbsenceFrom] >= " & Format(Me.DateFrom, strcJetDate) & " And [AbsenceTo] <= " & Format(Me.DateTo, strcJetDate)
        Debug.Print strCriteria
        'task = "select * from Tabelle1 where (" & strCriteria & ") order by [AbsenceFrom]"
        'DoCmd.ApplyFilter task
        Me.Filter = strCriteria
        Me.FilterOn = True
    End If

Comment out the Debug.Print when you are happy it is working. Use Debug.Print to see what you actually have, not what you think you have.

HTH

 

[StyxLykin]

Was trying to change the dateformat, but I am just not able to figure it out

 

[Gasman]

See my code.

 

[Gasman]

This is how I would also use BETWEEN

        strCriteria = "[AbsenceFrom] BETWEEN " & Format(Me.DateFrom, strcJetDate) & " AND " & Format(Me.DateTo, strcJetDate)

My dates are 01/05/2021 and 31/05/2021
Debug.print shows
[AbsenceFrom] BETWEEN #05/01/2021# AND #05/31/2021#


Previous code shows
[AbsenceFrom] >= #05/01/2021# And [AbsenceTo] <= #05/31/2021#

 

[StyxLykin]

 

[StyxLykin]

using the code it comes up with that I am utilizing a variable instead of modual

 

[Gasman]

Do not try and be clever.
You cannot have modules the same name as variables/constants. How is Access supposed to know which one you mean.

Use what works.
Create a module, call it ModConst, and put that constant in that.
Then just use it where needed.

 

[StyxLykin]

The Filter is working. Thank you. That was though

 

[Gasman]

Here it is

 

[Pat Hartman]

If Access is seeing the dates as a string, then you either need to change the data type on the server to DateTime OR change the format to yyyy-mm-dd. The ONLY date format that works correctly as a string is year, month day. String compares are character by character left to right so o2/11/2020 is > 01/11/2021