Query Date Range Criteria Doesn't Include Last Date in Range

[Karl Burrows]

I have used this code several times in other queries for reports and for
some reason this time it will not include records from the last date input
in the query/report. Any ideas?

Not Is Null And Between [Forms]![frmReports]![edtStartDate] And
[Forms]![frmReports]![edtEndDate]

Thanks!

 

[Allen Browne]

The Date/Time field in Access is always made up of date and time parts. If
the value in the table is 3am on June 9, and you ask for records up to June
9, that record is 3 hours too late to be included.

Solutions:
1. Store only a date, with no time.
If this is appropriate (i.e. the time is not meaningful), then get rid of
the time part, and just store the date. This often happens where people used
Now() as the default value, and they intended Date().

2. Change the criteria to less than the next day, so it includes all times:

= [Forms]![frmReports]![edtStartDate] And

< ([Forms]![frmReports]![edtEndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have used this code several times in other queries for reports and for
some reason this time it will not include records from the last date input
in the query/report. Any ideas?

Not Is Null And Between [Forms]![frmReports]![edtStartDate] And
[Forms]![frmReports]![edtEndDate]

Thanks!

 

[Karl Burrows]

Allen, you are my hero!

The Date/Time field in Access is always made up of date and time parts. If
the value in the table is 3am on June 9, and you ask for records up to June
9, that record is 3 hours too late to be included.

Solutions:
1. Store only a date, with no time.
If this is appropriate (i.e. the time is not meaningful), then get rid of
the time part, and just store the date. This often happens where people used
Now() as the default value, and they intended Date().

2. Change the criteria to less than the next day, so it includes all times:

= [Forms]![frmReports]![edtStartDate] And

< ([Forms]![frmReports]![edtEndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have used this code several times in other queries for reports and for
some reason this time it will not include records from the last date input
in the query/report. Any ideas?

Not Is Null And Between [Forms]![frmReports]![edtStartDate] And
[Forms]![frmReports]![edtEndDate]

Thanks!

 

[Karl Burrows]

I tried the criteria formula you gave me and got a expression too
complicated message. I also checked the value for the form and they are set
to DateSerial(Year(Date()),1,1) for the start date (first day of year) and
Date() for the end date, so not sure why it is not picking up the current
day. I verified the table and query as well and don't see any formatting
that would create a time value vs. date.

Any other suggestions?

Thanks again!

The Date/Time field in Access is always made up of date and time parts. If
the value in the table is 3am on June 9, and you ask for records up to June
9, that record is 3 hours too late to be included.

Solutions:
1. Store only a date, with no time.
If this is appropriate (i.e. the time is not meaningful), then get rid of
the time part, and just store the date. This often happens where people used
Now() as the default value, and they intended Date().

2. Change the criteria to less than the next day, so it includes all times:

= [Forms]![frmReports]![edtStartDate] And

< ([Forms]![frmReports]![edtEndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have used this code several times in other queries for reports and for
some reason this time it will not include records from the last date input
in the query/report. Any ideas?

Not Is Null And Between [Forms]![frmReports]![edtStartDate] And
[Forms]![frmReports]![edtEndDate]

Thanks!

 

[Rick Brandt]

I tried the criteria formula you gave me and got a expression too
complicated message. I also checked the value for the form and they are set
to DateSerial(Year(Date()),1,1) for the start date (first day of year) and
Date() for the end date, so not sure why it is not picking up the current
day. I verified the table and query as well and don't see any formatting
that would create a time value vs. date.

Any other suggestions?

It's not a question of formatting. If the values *stored* in the table contain
any time besides midnight then you have to add a day to your end date to pick
those up.

 

[Karl Burrows]

How do I get the criteria formula to work? I keep getting an error when I
try it.

Thank!

I tried the criteria formula you gave me and got a expression too
complicated message. I also checked the value for the form and they are
set
to DateSerial(Year(Date()),1,1) for the start date (first day of year) and
Date() for the end date, so not sure why it is not picking up the current
day. I verified the table and query as well and don't see any formatting
that would create a time value vs. date.

Any other suggestions?

It's not a question of formatting. If the values *stored* in the table
contain
any time besides midnight then you have to add a day to your end date to
pick
those up.

 

[Allen Browne]

The "Too complicated" error message can mean things like this:
1. The criteria are malformed, e.g. the brackets don't match.

2. Access is misunderstanding the SQL statement, e.g. if you have a field
called Where, or incorrect bracketing.

3. Access is misunderstanding the data type. Suggestions on helping it out:
http://allenbrowne.com/ser-45.html