Can't Get date range query to show just one days transactions 2

[Tadynn]

I've created a form with 2 text boxes (one is "From Date", the other is "To Date&quot that queries a date range in a query. This now works okay thanks to one of the many wizards from this excellent site.
However, I'm now having another problem, with when I type in 2 values that are the same to try to query a single days transactions I get no results. I know that this the trouble is caused by my query date field format being dd/mm/yy hh:mm:ss, and my form input mask for both of the date fields is dd/mm/yy. I've tried to format the query date field to dd/mm/yy, and I've tried to change the input mask for the To Date Text field on the form to dd/mm/yy hh:mm:ss. But neither seem to work.
If anything, I don't really want to change the input masks on the form, as I want to keep it as simple and painless as possible for the user. Can anybody out there help me???
Please find my query below.

Note: the Date field that I query is called AddDate in the query.


SELECT [dbo_PICKDETAIL].[OrderKey], [dbo_PICKDETAIL].[OrderLineNumber], [dbo_PICKDETAIL].[Storerkey], [dbo_PICKDETAIL].[Sku], [dbo_LOTATTRIBUTE].[Lottable03], [dbo_PICKDETAIL].[Qty], [dbo_LOTATTRIBUTE].[Lottable01], [dbo_PICKDETAIL].[Loc], [dbo_PICKDETAIL].[Lot], [dbo_PICKDETAIL].[ID], [dbo_LOTATTRIBUTE].[Lottable02], [dbRDERS].[ExternOrderKey], [dbo_PICKDETAIL].[AddDate]
FROM (dbo_LOTATTRIBUTE INNER JOIN dbo_PICKDETAIL ON [dbo_LOTATTRIBUTE].[Lot]=[dbo_PICKDETAIL].[Lot]) INNER JOIN dbRDERS ON [dbo_PICKDETAIL].[OrderKey]=[dbRDERS].[OrderKey]
WHERE ((([dbo_PICKDETAIL].[OrderKey])>=[Forms]![Withdrawals]![From Shipment No])) And ((([dbo_PICKDETAIL].[OrderKey])<=[Forms]![Withdrawals]![To Shipment No])) And ((([dbo_PICKDETAIL].[StorerKey])>=[Forms]![Withdrawals]![From Storer])) And ((([dbo_PICKDETAIL].[StorerKey])<=[Forms]![Withdrawals]![To Storer])) And ((([dbo_PICKDETAIL].[Sku])>=[Forms]![Withdrawals]![From Commodity])) And ((([dbo_PICKDETAIL].[Sku])<=[Forms]![Withdrawals]![To Commodity])) And ((([dbo_LOTATTRIBUTE].[Lottable02])>=[Forms]![Withdrawals]![From Nature 20]) Or IsNull([Forms]![Withdrawals]![From Nature 20])) And ((([dbo_LOTATTRIBUTE].[Lottable02])<=[Forms]![Withdrawals]![To Nature 20])) And ((([dbo_LOTATTRIBUTE].[Lottable03])>=[Forms]![Withdrawals]![From Cust Lot]) Or IsNull([Forms]![Withdrawals]![From Cust Lot])) And ((([dbo_LOTATTRIBUTE].[Lottable03])<=[Forms]![Withdrawals]![To Cust Lot])) And ((([dbRDERS].[ExternOrderKey])>=[Forms]![Withdrawals]![From Nature 30]) Or IsNull([Forms]![Withdrawals]![From Nature 30])) And ((([dbRDERS].[ExternOrderKey])<=[Forms]![Withdrawals]![To Nature 30])) And ((([dbo_PICKDETAIL].[AddDate])>=[Forms]![Withdrawals]![From Date]) Or IsNull([Forms]![Withdrawals]![From Date])) And ((([dbo_PICKDETAIL].[AddDate])<=[Forms]![Withdrawals]![To Date]) Or IsNull([Forms]![Withdrawals]![To Date]));

 

[LonnieJohnson]

The cool thing about Access is that it offers more than one way to skin a cat. Here is my way of getting all records for a date range when the format has minutes and seconds and stuff. Use this expression in the query...

>DateAdd(&quot;d&quot;,-1,[FromDate]) And <DateAdd(&quot;d&quot;,1,[ToDate])

This will prompt you for a from date and a to date. It uses the date add function to get the date before and after your date and says it wants everthing greater and less respectively. Give it a try. It works for me. If you want to get your dates from your form, of course you can replace [FromDate] and [ToDate] with the references to the text box on your popup form.

Good luck. ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[CosmoKramer]

How about this:

Between [Forms]![Withdrawals]![From Date] & " 00:00" And [Forms]![Withdrawals]![To Date] & " 23:59"