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!

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karl Burrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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, you are my hero!

"Allen Browne" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karl Burrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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!

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!

"Allen Browne" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karl Burrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

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

Thank!

"Rick Brandt" <(E-Mail Removed)> wrote in message
news:HW6qe.3037$%(E-Mail Removed)...
"Karl Burrows" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karl Burrows" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> How do I get the criteria formula to work? I keep getting an error when I
> try it.
>
> Thank!
>
> "Rick Brandt" <(E-Mail Removed)> wrote in message
> news:HW6qe.3037$%(E-Mail Removed)...
> "Karl Burrows" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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.
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com