Filter / "Between" help

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hi
I have a form based on a query and on the form I have a button that calls
code to filter between a start date and a end date using:-

Me.Filter = "datevalue(FromQuery.audDate) between CDate([Required Date])
and CDate([End Required Date])"

this worked other than it did not find any 01/02/05 dates if I used 01/02/05
to 03/02/05, I now understand that I need to use the Dateadd function
however when I use it in the "between" and "AND" context it will not work it
just finds more records that the dB had to start with.
When I test the basic bit of code:-

Me.Filter = "datevalue(FromQuery.audDate) = Format(DateAdd('d', -1, [Start
Date]), 'dd\/mm\/yyyy')"

I can see this part find the day based on me adjusting the day count as it
should.

It looks like I do not understand the "between" syntax, could someone help.


Thank you for any help
Dave
 
David said:
Hi
I have a form based on a query and on the form I have a button that
calls code to filter between a start date and a end date using:-

Me.Filter = "datevalue(FromQuery.audDate) between CDate([Required
Date]) and CDate([End Required Date])"

this worked other than it did not find any 01/02/05 dates if I used
01/02/05 to 03/02/05, I now understand that I need to use the Dateadd
function however when I use it in the "between" and "AND" context it
will not work it just finds more records that the dB had to start
with. When I test the basic bit of code:-

Me.Filter = "datevalue(FromQuery.audDate) = Format(DateAdd('d', -1,
[Start Date]), 'dd\/mm\/yyyy')"

I can see this part find the day based on me adjusting the day count
as it should.

It looks like I do not understand the "between" syntax, could someone
help.
Click to expand...

You're not reporting something correctly because your first syntax WILL return
all records dated 1/2/05, all records with a date of 3/2/05, as well as all the
records with dates between those two. BETWEEN is inclusive and by using
DateValue you have removed any behaviors that a Time component would introduce.
 
Hi Rick
Thank you for the reply, are you saying that I have the correct syntax :-

Me.Filter = "datevalue(FromQuery.audDate) between CDate([Required Date])
and CDate([End Required Date])"

and that my form should be displaying records from the 1/02/05 to the
3/02/05 with the input date between the 1/02/05 3/02/05.

If that is the case why am I only filtering dates between the 2/02/05 to the
3/02/05, or have I total misunderstood your reply?

Dave

Rick Brandt said:
David said:
Hi
I have a form based on a query and on the form I have a button that
calls code to filter between a start date and a end date using:-

Me.Filter = "datevalue(FromQuery.audDate) between CDate([Required
Date]) and CDate([End Required Date])"

this worked other than it did not find any 01/02/05 dates if I used
01/02/05 to 03/02/05, I now understand that I need to use the Dateadd
function however when I use it in the "between" and "AND" context it
will not work it just finds more records that the dB had to start
with. When I test the basic bit of code:-

Me.Filter = "datevalue(FromQuery.audDate) = Format(DateAdd('d', -1,
[Start Date]), 'dd\/mm\/yyyy')"

I can see this part find the day based on me adjusting the day count
as it should.

It looks like I do not understand the "between" syntax, could someone
help.
Click to expand...

You're not reporting something correctly because your first syntax WILL
return all records dated 1/2/05, all records with a date of 3/2/05, as
well as all the records with dates between those two. BETWEEN is
inclusive and by using DateValue you have removed any behaviors that a
Time component would introduce.
Click to expand...
 
David said:
Hi Rick
Thank you for the reply, are you saying that I have the correct
syntax :-
Me.Filter = "datevalue(FromQuery.audDate) between CDate([Required
Date]) and CDate([End Required Date])"

and that my form should be displaying records from the 1/02/05 to the
3/02/05 with the input date between the 1/02/05 3/02/05.

If that is the case why am I only filtering dates between the 2/02/05
to the 3/02/05, or have I total misunderstood your reply?
Click to expand...

That's correct. Build a quick sample table and try using BETWEEN on some
numbers. If you enter the numbers 1 through 5 and then create a query with
criteria of BETWEEN 2 and 4 you should get...

2
3
4

I just did a similar test and entered the dates 1/1/05, 1/2/05, 1/3/05, and
1/4/05. A query using BETWEEN #1/2/05# and #1/3/05# returned...

1/2/05
1/3/05
 
PMFJI...

CDate will not strip off the time component of a date/time value. In the
filter statement, although the time component is being removed from the
source field, it is not being removed from the criteria values.

Thus, the equality will only match for 12 midnight on End Required Date
date, which is why you won't see entries that are after that time on the End
Required Date date (such as 9 am or 12 noon or such).

DateValue should be used for all fields:

Me.Filter = "datevalue(FromQuery.audDate) between DateValue(CDate([Required
Date])) and DateValue(CDate([End Required Date]))"

--

Ken Snell
<MS ACCESS MVP>

Rick Brandt said:
David said:
Hi Rick
Thank you for the reply, are you saying that I have the correct
syntax :-
Me.Filter = "datevalue(FromQuery.audDate) between CDate([Required
Date]) and CDate([End Required Date])"

and that my form should be displaying records from the 1/02/05 to the
3/02/05 with the input date between the 1/02/05 3/02/05.

If that is the case why am I only filtering dates between the 2/02/05
to the 3/02/05, or have I total misunderstood your reply?
Click to expand...

That's correct. Build a quick sample table and try using BETWEEN on some
numbers. If you enter the numbers 1 through 5 and then create a query
with criteria of BETWEEN 2 and 4 you should get...

2
3
4

I just did a similar test and entered the dates 1/1/05, 1/2/05, 1/3/05,
and 1/4/05. A query using BETWEEN #1/2/05# and #1/3/05# returned...

1/2/05
1/3/05
Click to expand...
 
Ken said:
PMFJI...

CDate will not strip off the time component of a date/time value. In
the filter statement, although the time component is being removed
from the source field, it is not being removed from the criteria
values.
Click to expand...

I had assumed that those were form controls and as such would have only dates
entered in them.
 
Hi All

I take it this is the point you are both unclear about , ([Required Date])
/ [End Required Date]) pop-up as enter parameter value boxes?

If it helps, I don't mind if either of you can suggest a different way of
achieving being able to request from the user a start date and a end date by
either parameter value boxes or textboxes on same form or even input
boxes, that way you would all understand the syntax and be able to explain
it to me if I had any further issues.

Thank you for your time.
Dave
 
David said:
Hi All

I take it this is the point you are both unclear about , ([Required
Date]) / [End Required Date]) pop-up as enter parameter value boxes?

If it helps, I don't mind if either of you can suggest a different
way of achieving being able to request from the user a start date and
a end date by either parameter value boxes or textboxes on same
form or even input boxes, that way you would all understand the
syntax and be able to explain it to me if I had any further issues.

Thank you for your time.
Click to expand...

If these are self-prompting parameters embedded in the query and the user is
only entering a date (no time) then the first syntax that you posted should
work.

You need to experiment a bit and break this down into smaller pieces to see what
is going on. First try replacing the parameter markers with hard coded dates
and see what happens.
 
Hi Rick

Thanks for the reply, as you confirmed that my syntax should work (I had
hard coded the dates with the same effect ie. the start date displayed on
the form was always one less than the requested one) I turned my attention
to the form set-up.

I found that the form was not displaying what I refer to as record one when
I opened the form, hence the record being displayed in the date textbox on
the form had a date of the 16/02/05 + time.

I set-up the date text box format to short date and sorted this field via
the ascending sort button of the toolbar.

Once I had done this the filter started to work correctly ie. I requested
dates between 2/02/05- 4/02/05 and the form displayed the correct start date
in the date text box and full range could be selected via the record
selectors.

I take it that I should have sorted the form before I played about with the
filtering function.


Could you supply an example of how I would modify my syntax should I ever
need to use the addDate() function.

Thank you for your continuing support.

All the best

Dave

Rick Brandt said:
David said:
Hi All

I take it this is the point you are both unclear about , ([Required
Date]) / [End Required Date]) pop-up as enter parameter value boxes?

If it helps, I don't mind if either of you can suggest a different
way of achieving being able to request from the user a start date and
a end date by either parameter value boxes or textboxes on same
form or even input boxes, that way you would all understand the
syntax and be able to explain it to me if I had any further issues.

Thank you for your time.
Click to expand...

If these are self-prompting parameters embedded in the query and the user
is only entering a date (no time) then the first syntax that you posted
should work.

You need to experiment a bit and break this down into smaller pieces to
see what is going on. First try replacing the parameter markers with hard
coded dates and see what happens.
Click to expand...
 
David said:
Hi Rick

Thanks for the reply, as you confirmed that my syntax should work (I
had hard coded the dates with the same effect ie. the start date
displayed on the form was always one less than the requested one) I
turned my attention to the form set-up.

I found that the form was not displaying what I refer to as record
one when I opened the form, hence the record being displayed in the
date textbox on the form had a date of the 16/02/05 + time.

I set-up the date text box format to short date and sorted this field
via the ascending sort button of the toolbar.

Once I had done this the filter started to work correctly ie. I
requested dates between 2/02/05- 4/02/05 and the form displayed the
correct start date in the date text box and full range could be
selected via the record selectors.

I take it that I should have sorted the form before I played about
with the filtering function.


Could you supply an example of how I would modify my syntax should I
ever need to use the addDate() function.
Click to expand...

Assuming you mean DateAdd() I'm not sure what you mean. DateAdd() returns a
DateTime result so you can use it anywhere that you need a DateTime value.
 
Hi
It's OK I found this example from Allen Browne Microsoft MVP

.. Time component in your field?
If the field contains a date and a time (e.g. if its Default Value was
=Now()), and you filter for a particular date, the records will not match.
Either remove the time component from the field (with DateValue() in an
Update query), or use:
strFilter = "([HireDate] >= " & Format(Me.txt, "\#mm\/dd\/yyyy\#") & ")
AND ([HireDate] < " & Format(DateAdd("d", 1, Me.txt), "\#mm\/dd\/yyyy\#") &
")"
 
Back
Top