Problem Using Dates in a Query

[DavidWE]

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.

 

[KeithG]

I would change the default for your textbox for the end date to Date()+1

 

[DavidWE]

Thanks, Keith. That would probably work. I'm not sure what to do with the start date. If a user runs the report at 5:00 pm, will my query need to know a beginning time as well as a date? I could try it and find out.

 

[KeithG]

I wouldn't supply a time in the start date because you want all transactions from the current day correct?

 

[DavidWE]

Yes, I want all the transactions for the current date. I'm just not sure if my query will start the search at the correct time of day. I was surprised that it did not pick up all transactions with just the dates. If I format the date field in the table as a short date, it will get them all.

There are occasions when a user might want to go back and get transactions for a previous day. Is there a way to add 1 to the end date (not today's date) after the user has entered a date? I would need something like txtEndDate.Value = txtEndDate.Value + 1, which I know won't work as written.

In the future, I might use separate columns for date and time in my tables.