Problem Using Dates in a Query

[DavidW]

I have a form where the user can enter a start date and an end date
into two textboxes. The default for both textboxes is today's date.
The user can then view or print a report of transactions between those
two dates. I would like for the default to be the current day's
transactions.

The report will not pick up any transactions from a table if the dates
entered are the same date. If the dates are different, it will get the
transactions. For example, a txtStartDate of 8/29/2006 and a txtEndDate
of 8/29/2006 will not pick up any data. A txtStartDate of 8/28/2006 and
a txtEndDate of 8/29/2006 will get the transactions.

Here is my query:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[TDate] Between
[Forms]![DateQuery].[txtStartDate] And
[Forms]![DateQuery].[txtEndDate])
ORDER BY [Transactions].[TDate];

The report displaying the transactions is based on the query.

I'm almost certain that the reason for my problem is that the dates
are formatted as long in the table - 8/29/2006 11:13:00 AM. What would
be the easiest solution? If I format the start and end dates on my form
as long, I will need to use a default of today's date at 12:00 am for
the start date. How would I do that? I could also reformat the date in
the query so it will do an accurate search. What is the best way to do
that? Another option would be to change the date format in the
transaction table. I would rather not change it.

Thanks for any help.

 

[Allen Browne]

The most likely issue is that the field contains a time as well as a date.
Change the query to ask for all records less than the date after the end
date:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[TDate] >=
[Forms]![DateQuery].[txtStartDate])
And ([Transactions].[TDate] <
[Forms]![DateQuery].[txtEndDate] - 1)
ORDER BY [Transactions].[TDate];

Other possibilities include that Access is not understanding the data type.
Set the Format property of the unbound text boxes to:
Short Date
Then declare the parameters in the query. Choose Parameters on the Query
menu, and enter 2 rows in the dialog:
[Forms]![DateQuery].[txtStartDate] Date/Time
[Forms]![DateQuery].[txtEndDate] Date/Time

A 3rd possibility is that the focus is still in txtEndDate on the form, so
its Value has not been updated yet.

 

[fredg]

I have a form where the user can enter a start date and an end date
into two textboxes. The default for both textboxes is today's date.
The user can then view or print a report of transactions between those
two dates. I would like for the default to be the current day's
transactions.

The report will not pick up any transactions from a table if the dates
entered are the same date. If the dates are different, it will get the
transactions. For example, a txtStartDate of 8/29/2006 and a txtEndDate
of 8/29/2006 will not pick up any data. A txtStartDate of 8/28/2006 and
a txtEndDate of 8/29/2006 will get the transactions.

Here is my query:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[TDate] Between
[Forms]![DateQuery].[txtStartDate] And
[Forms]![DateQuery].[txtEndDate])
ORDER BY [Transactions].[TDate];

The report displaying the transactions is based on the query.

I'm almost certain that the reason for my problem is that the dates
are formatted as long in the table - 8/29/2006 11:13:00 AM. What would
be the easiest solution? If I format the start and end dates on my form
as long, I will need to use a default of today's date at 12:00 am for
the start date. How would I do that? I could also reformat the date in
the query so it will do an accurate search. What is the best way to do
that? Another option would be to change the date format in the
transaction table. I would rather not change it.

Thanks for any help.

I doesn't matter how the dates are formatted. It does matter that the
Time is included with the date.
Your Date Field includes a Time value (or rather it's default Time
value is midnight).
Therefore any date past 00:00:00 AM of the [txtEndDate] is not within
the Between [Forms]![DateQuery].[txtStartDate] And
[Forms]![DateQuery].[txtEndDate] criteria.
Easiest work-around is to just add one day manually to the
[txtEndDate], i.e. instead of entering 8/29/2006 enter 8/30/2006.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (in Design View, click Query + Parameter).
Then change your criteria to:
Between [Forms]![DateQuery].[txtStartDate] And
([Forms]![DateQuery].[txtEndDate]+ 1)

The best solution (if the Time of day is not necessary), is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = int([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().

 

[DavidW]

Allen and Fred,

Thanks. I added 1 to the end date in my query:
([Forms]![DateQuery].[txtEndDate]+ 1).

That was the only thing that seemed to work.

I also formatted txtStartDate and txtEndDate as Date/Time in the
parameters option. That alone did not fix it, but editing the query did
fix it.

There might be cases when a user wants to view transactions from a
previous date. For example: the user might enter txtStartDate =
8/15/2006 and txtEndDate = 8/15/2006. With my query changed to add 1 to
the end date, is there a chance that some transactions from the
following day will also be picked up? For instance, could some
transactions from 8/16 also be included when I only want 8/15?

This quote from Fred's post might address that issue:

Your Date Field includes a Time value (or rather it's default Time
value is midnight).
Therefore any date past 00:00:00 AM of the [txtEndDate] is not within
the Between [Forms]![DateQuery].[txtStartDate] And
[Forms]![DateQuery].[txtEndDate] criteria.

Thanks again

 

[fredg]

Allen and Fred,

Thanks. I added 1 to the end date in my query:
([Forms]![DateQuery].[txtEndDate]+ 1).

That was the only thing that seemed to work.

I also formatted txtStartDate and txtEndDate as Date/Time in the
parameters option. That alone did not fix it, but editing the query did
fix it.

There might be cases when a user wants to view transactions from a
previous date. For example: the user might enter txtStartDate =
8/15/2006 and txtEndDate = 8/15/2006. With my query changed to add 1 to
the end date, is there a chance that some transactions from the
following day will also be picked up? For instance, could some
transactions from 8/16 also be included when I only want 8/15?

This quote from Fred's post might address that issue:

Your Date Field includes a Time value (or rather it's default Time
value is midnight).
Therefore any date past 00:00:00 AM of the [txtEndDate] is not within
the Between [Forms]![DateQuery].[txtStartDate] And
[Forms]![DateQuery].[txtEndDate] criteria.

Thanks again

Only of the time on the EndDate was EXACTLY midnight,
i.e. 8/15/2006 00:00:00

 

[fredg]

Allen and Fred,

Thanks. I added 1 to the end date in my query:
([Forms]![DateQuery].[txtEndDate]+ 1).

That was the only thing that seemed to work.

I also formatted txtStartDate and txtEndDate as Date/Time in the
parameters option. That alone did not fix it, but editing the query did
fix it.

There might be cases when a user wants to view transactions from a
previous date. For example: the user might enter txtStartDate =
8/15/2006 and txtEndDate = 8/15/2006. With my query changed to add 1 to
the end date, is there a chance that some transactions from the
following day will also be picked up? For instance, could some
transactions from 8/16 also be included when I only want 8/15?

This quote from Fred's post might address that issue:

Your Date Field includes a Time value (or rather it's default Time
value is midnight).
Therefore any date past 00:00:00 AM of the [txtEndDate] is not within
the Between [Forms]![DateQuery].[txtStartDate] And
[Forms]![DateQuery].[txtEndDate] criteria.

Thanks again

Only of the time on the EndDate was EXACTLY midnight,
i.e. 8/15/2006 00:00:00

Oops!
I meant to write:
If you want between 8/15/2006 and 8/15/2006 you will get 8/16/2006
only if the time of the [DateField] was EXACTLY midnight, i.e.
8/16/2006 00:00:00 AM

If this is a problem, then instead of Between XXX And YYY use

= [Forms]![DateQuery].[txtStartDate] and < ([Forms]![DateQuery].[txtStartDate] +1)

 

[DavidW]

OK. I got it. Thanks for the help.