Date Range Search Criteria not returning expected dates (1 Viewer)

[Sticky99]

Hi Guys,

Could somebody help with an issue I have regarding a date range search.
I have a form with two date picker field (Date From & Date To) which set the range to select from a query. Below is what I'm using to set the date range, the "To" date works but the "From" date also selects dates prior to the required date, does this make sense?

Private Sub btnSearchDateFrom_Click()
Call Search
End Sub
Sub Search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.txtSearchFrom) Or IsNull(Me.txtSearchTo) Then
MsgBox "Please Enter Date Range", vbInformation, "Date Range Is Required"
Me.txtSearchFrom.SetFocus
Else
strCriteria = "([DateofDelivery] >= #" & Me.txtSearchFrom & "# And [DateofDelivery] <= #" & Me.txtSearchTo & "#)"
task = "select * from qrysearchdaterange where (" & strCriteria & ") order by [dateofdelivery]"
DoCmd.ApplyFilter task

End If

End Sub

Any suggestions would be gratefully received.

Thanks

 

[jdraw]

You could try this for criteria (untested)
strCriteria = "[DateofDelivery] BETWEEN #" & Me.txtSearchFrom & "# AND #" & Me.txtSearchTo & "#"

Do you have examples where the unexpected(outside your range) Dates were returned?

 

[Minty]

If you are outside of the US you may need to format your dates to something access can't misinterpret;

Have a read here http://allenbrowne.com/ser-36.html
And you can use a variation of the function provided to get you a suitable date string;

Function fnVbaDate(vDate As Variant) As String

‘Usage – in a SQL string with a text box reference would be something like
‘Docmd.OpenQuery “SELECT Names, StartDate FROM Table1 WHERE StartDate >= “ & fnVbaDate(Me.txtStartDate)

    If IsDate(vDate) Then               ' Check that this is a valid date
        If TimeValue(vDate) = 0 Then                         ' If there is no time value just return a date
            fnVbaDate = Format$(vDate, "\#yyyy-mm-dd\#")
        Else
            fnVbaDate = Format$(vDate, "\#yyyy-mm-dd hh:nn:ss\#")  ‘ There is a time part so include it.
        End If
    End If

End Function

 

[HalloweenWeed]

Your date picker probably results on a DateSerial, meaning that you need to take out the "#"'s.

 

[ontopofmalvern]

If have use this many times, never let me down. The problem I had with dates and access was understanding in SQL dates need to be strings in format "#mm/dd/yy#"

Dim date1 As Date
Dim date2 As Date
Dim filter As String


date1 = Format( ????source of date1 , "mm/dd/yy")
date2 = Format( ????source of date2 , "mm/dd/yy")
filter = "[Datex] between #" & date1 & "# AND #" & date2 & "#"

Me.filter = filter

Me.FilterOn = True

 

[Sticky99]

If have use this many times, never let me down. The problem I had with dates and access was understanding in SQL dates need to be strings in format "#mm/dd/yy#"

Dim date1 As Date
Dim date2 As Date
Dim filter As String


date1 = Format( ????source of date1 , "mm/dd/yy")
date2 = Format( ????source of date2 , "mm/dd/yy")
filter = "[Datex] between #" & date1 & "# AND #" & date2 & "#"

Me.filter = filter

Me.FilterOn = True

Hi Tried this and get an error

Dim date1 As Date
Dim date2 As Date
Dim filter As String


date1 = Format(txtdatefrom, "mm/dd/yy")
date2 = Format(txtdateto, "mm/dd/yy")
filter = "[dateofdelivery] between #" & date1 & "# AND #" & date2 & "#"

Me.filter = filter

Me.FilterOn = True

Is this correct, using my field names?

 

[Sticky99]

Your date picker probably results on a DateSerial, meaning that you need to take out the "#"'s.

Hi tried removing the # and, unfortunately, it didn't work. Just working through the other suggestions.

Thanks

 

[Sticky99]

You could try this for criteria (untested)
strCriteria = "[DateofDelivery] BETWEEN #" & Me.txtSearchFrom & "# AND #" & Me.txtSearchTo & "#"

Do you have examples where the unexpected(outside your range) Dates were returned?

Hi, Tried this and didn't work, am trying the other suggestions at the moment.

thanks for your input,

 

[Sticky99]

If you are outside of the UK you may need to format your dates to something access can't misinterpret;

Have a read here http://allenbrowne.com/ser-36.html
And you can use a variation of the function provided to get you a suitable date string;

Function fnVbaDate(vDate As Variant) As String

‘Usage – in a SQL string with a text box reference would be something like
‘Docmd.OpenQuery “SELECT Names, StartDate FROM Table1 WHERE StartDate >= “ & fnVbaDate(Me.txtStartDate)

    If IsDate(vDate) Then               ' Check that this is a valid date
        If TimeValue(vDate) = 0 Then                         ' If there is no time value just return a date
            fnVbaDate = Format$(vDate, "\#yyyy-mm-dd\#")
        Else
            fnVbaDate = Format$(vDate, "\#yyyy-mm-dd hh:nn:ss\#")  ‘ There is a time part so include it.
        End If
    End If

End Function

Hi Minty, I'm in the UK and will check out the link you added above, thanks for that. I'll let you know how I get on.

thanks

 

[Gasman]

Hi Tried this and get an error

Dim date1 As Date
Dim date2 As Date
Dim filter As String


date1 = Format(txtdatefrom, "mm/dd/yy")
date2 = Format(txtdateto, "mm/dd/yy")
filter = "[dateofdelivery] between #" & date1 & "# AND #" & date2 & "#"

Me.filter = filter

Me.FilterOn = True

Is this correct, using my field names?

You couldn't say what the error is?

Date1 and Date2 should be defined as strings as the Format() function will create a string.
Debug.Print filter and post that back here

 

[Gasman]

This worked for me ?

Private Sub txtFilter_AfterUpdate()
Dim dtf As Date, dtt As Date
dtf = #4/1/2020#
dtt = #4/30/2020#
'Me.Filter = "Client Like ""*" & Me.txtFilter & "*"""
Me.Filter = "TransactionDate Between #" & Format(dtf, "mm/dd/yyyy") & "# AND #" & Format(dtt, "mm/dd/yyyy") & "#"
Debug.Print Me.Filter
Me.FilterOn = True
Set Me.sfrmEmails.Form.Recordset = Me.Recordset
End Sub

 

[Sticky99]

You couldn't say what the error is?

Date1 and Date2 should be defined as strings as the Format() function will create a string.
Debug.Print filter and post that back here

Hi, I can’t do this tonight but will get back to you tomorrow if that’s ok. Cheers.

 

[ontopofmalvern]

opps Gasman is absolutley right the dates are strings (I think I copy and pasted and older version from my notes)

He's also right about copious use of Debug.Print, it has helped me loads to get from being completely useless to being almost useful. It is especially handy when concating strings together for filters or SQL, it is hard sometimes to see how all the "bits" & "pieces" will look like together.

On your code
date1 = Format(txtdatefrom, "mm/dd/yy")
date2 = Format(txtdateto, "mm/dd/yy")

I think you need to add where the controls 'txtdatefrom' and 'txtdateto' are, if the code is part of the form the controls are on then 'me.txtdatefrom' will do it.

ps the date thing is a wrestle for us brits.

 

[Gasman]

FWIW I have used this in some of my DBs. That saves with adding all the # each time.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

then just use that

strCriteria = "[DateofDelivery] BETWEEN  & Format(Me.txtSearchFrom,strcJetDate) & " AND " & Format(Me.txtSearchTo,strcJetDate)

HTH

 

[Sticky99]

Hi Guys,

Thank you all for your help, really appreciated. Finally got this working with the following:

Private Sub btnSearchDateFrom_Click()

Dim date1 As Date
Dim date2 As Date
Dim filter As String


    date1 = Format(Me.txtSearchFrom, "mm/dd/yy")
    date2 = Format(Me.txtSearchTo, "mm/dd/yy")
    filter = "[dateofdelivery] between #" & date1 & "# AND #" & date2 & "#"

Me.filter = filter

Me.FilterOn = True

End Sub

Thank you ontopofmalvern for your code

 

[Minty]

Just for your information, that is what the function does, and saves you having to keep typing the format expression so it would have become;

Private Sub btnSearchDateFrom_Click()

    Dim sFilter As String

    sFilter = "[dateofdelivery] between " & fnVbaDate(Me.txtSearchFrom) & " AND " & fnVbaDate(Me.txtSearchFrom)

    Me.filter = sFilter
    Me.FilterOn = True

End Sub

 

[Sticky99]

Just for your information, that is what the function does, and saves you having to keep typing the format expression so it would have become;

Private Sub btnSearchDateFrom_Click()

    Dim sFilter As String

    sFilter = "[dateofdelivery] between " & fnVbaDate(Me.txtSearchFrom) & " AND " & fnVbaDate(Me.txtSearchFrom)

    Me.filter = sFilter
    Me.FilterOn = True

End Sub

Cheeers Minty