Filter between two dates

[Guest]

Hi, I have two unbound fields on a form (startdate and enddate) and a filter
button. I am having problems getting the code to work though... I want all
records to be displayed that are between the two dates, to include the actual
start and end dates too. My regional settings are UK, without the format$
code I received type mismatch error. Please could someone tell me where I am
going wrong...

Me.Filter = "[ReviewDate] >= " & Format$(StartDate, "mm\/dd\/yyyy") And
[ReviewDate] <= " & Format$(EndDate, "mm\/dd\/yyyy")"
Me.FilterOn = True

Thanks in advance for any help.
Sue

 

[Guest]

Try this
Me.Filter = "[ReviewDate] >= #" & StartDate & "# And [ReviewDate] <= #" &
EndDate & "#"

 

[Douglas J Steele]

You're almost there! Yes, in many places Access ignores the Short Date
format set through Regional Settings, and you have to use mm/dd/yyyy format.
However, you need to delimit dates with the # character. As well, you've got
some problems with the quotes, at least in what you've posted:

Me.Filter = "[ReviewDate] >= " & _
Format$(StartDate, "\#mm\/dd\/yyyy\#") & _
" And [ReviewDate] <= " & _
Format$(EndDate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

You could also use

Me.Filter = "[ReviewDate] Between " & _
Format$(StartDate, "\#mm\/dd\/yyyy\#") & _
" And " & _
Format$(EndDate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

 

[Guest]

Thanks, worked a treat as ever! I didn't use the between code because I
thought this did not include any records falling on the start date?

Sue

You're almost there! Yes, in many places Access ignores the Short Date
format set through Regional Settings, and you have to use mm/dd/yyyy format.
However, you need to delimit dates with the # character. As well, you've got
some problems with the quotes, at least in what you've posted:

Me.Filter = "[ReviewDate] >= " & _
Format$(StartDate, "\#mm\/dd\/yyyy\#") & _
" And [ReviewDate] <= " & _
Format$(EndDate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

You could also use

Me.Filter = "[ReviewDate] Between " & _
Format$(StartDate, "\#mm\/dd\/yyyy\#") & _
" And " & _
Format$(EndDate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I have two unbound fields on a form (startdate and enddate) and a filter
button. I am having problems getting the code to work though... I want all
records to be displayed that are between the two dates, to include the actual
start and end dates too. My regional settings are UK, without the format$
code I received type mismatch error. Please could someone tell me where I am
going wrong...

Me.Filter = "[ReviewDate] >= " & Format$(StartDate, "mm\/dd\/yyyy") And
[ReviewDate] <= " & Format$(EndDate, "mm\/dd\/yyyy")"
Me.FilterOn = True

Thanks in advance for any help.
Sue

 

[Douglas J Steele]

Between is supposed to be inclusive.

One thing to note (which may not be relevant to your situation, but I wanted
to include it for the benefit of others who might be reading this thread):
if you're using Now to populate ReviewDate, it'll include time as well as
date. That means that if you have records for today, and use <=
#06/09/2005#, you won't pick them up, since that represents midnight this
morning.

Date/Times are stored as 8 byte floating point number, where the integer
value represents the date as the number of days relative to 30 Dec, 1899,
and the decimal value represents the time as a fraction of a day. Today (06
Jun, 2005) would be 38512. Noon today would be 38512.5


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, worked a treat as ever! I didn't use the between code because I
thought this did not include any records falling on the start date?

Sue

You're almost there! Yes, in many places Access ignores the Short Date
format set through Regional Settings, and you have to use mm/dd/yyyy format.
However, you need to delimit dates with the # character. As well, you've got
some problems with the quotes, at least in what you've posted:

Me.Filter = "[ReviewDate] >= " & _
Format$(StartDate, "\#mm\/dd\/yyyy\#") & _
" And [ReviewDate] <= " & _
Format$(EndDate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True

You could also use

Me.Filter = "[ReviewDate] Between " & _
Format$(StartDate, "\#mm\/dd\/yyyy\#") & _
" And " & _
Format$(EndDate, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I have two unbound fields on a form (startdate and enddate) and a filter
button. I am having problems getting the code to work though... I want all
records to be displayed that are between the two dates, to include the actual
start and end dates too. My regional settings are UK, without the format$
code I received type mismatch error. Please could someone tell me

where I
am

going wrong...

Me.Filter = "[ReviewDate] >= " & Format$(StartDate, "mm\/dd\/yyyy") And
[ReviewDate] <= " & Format$(EndDate, "mm\/dd\/yyyy")"
Me.FilterOn = True

Thanks in advance for any help.
Sue