date range input to query 1

[Clegg25]

okay,
i have asked a very similiar question before, but i want to repost, because i dont think i explained it well. Here goes:
I have a database with only 3 fields. The first field is an autodate/time field. I want the "user" to be able to push a button and something come up to ask him a date he wants to run. The output would be a query or report of the information contained on all the records within that date. is there kindof an easy way to do this?
Please help.
Thanks,

 

[BNPMike]

You can put criteria in the query build screen. For example if you put, under your date, [What date do you want?], every time the query is run a message box will be displayed and you will have records selected for only that date.

 

[Clegg25]

i tried it out, but it seems to not like the fact that my date/time field is an auto (NOW()) field. Is there a way around this?

 

[BobJacksonNCI]

Clegg25,

How about bringing up your query in design view, select View - SQL, and then copy/paste the SQL code here.

Then, Mike, I or someone can help you.

Bob

 

[Clegg25]

Here is the code from my query:

SELECT [Shift Log Entries].[Pass On?], [Shift Log Entries].Time, [Shift Log Entries].Entry
FROM [Shift Log Entries]
WHERE ((([Shift Log Entries].Time)=[Enter Date]));

 

[mgolla]

The problem with the where is that the Enter Date will never be equal unless the enter the Time as well. Try changing it to:

WHERE ((([Shift Log Entries].Time)Like[Enter Date]))

And see if that helps

HTH

 

[Clegg25]

i tried that sql. it did not work. It still gives me the latest date and time. This is driving me nutz!

 

[Clegg25]

hey!
if i put an asterik after the date i input, it gives me the dates!!! It also gives me the current date at the bottom of the list though.
I think it's almost there.

 

[BobJacksonNCI]

Driving you nuts?...

And you're using Access?

Not surprising ;-)


SELECT [Shift Log Entries].[Pass On?], [Shift Log Entries].Time, [Shift Log Entries].Entry
FROM [Shift Log Entries]
WHERE ((DateValue([Shift Log Entries].Time)=[Enter Date]);

HTH,
Bob

 

[Clegg25]

That last one workeD!!! Thank you very much. Is there a way to remove the last entrie on the list? it

 

[Clegg25]

my follow up question to this is:
When i try to create a report based on this query, the report comes up blank. it asks me for a date, but when i put in a date, the report doesnt show any of the fields. does this make sense?

 

[BobJacksonNCI]

That suggests the query is not finding a match. Probably best to debug the query by itself, first. Again, if you'll post the SQL code, someone can help. It may be that the instruction I suggested is incorrect, causing an internal data type mismatch, and needs to be:

WHERE ((DateValue([Shift Log Entries].Time)=DateValue([Enter Date]));



HTH,
Bob

 

[Clegg25]

that last post worked!! Thanks Bob!!

 

[BobJacksonNCI]

Thanks for the star!

Food for thought:

Someone using the database may enter an invalid date. If so, the error message received will be of Microsoft's making and often is not user-friendly. Most folks here recommend using a form to prompt for the date and then coding a before-update event for the control that checks the entry and lets you give them a message that's easy to understand if there's a boo-boo. Then, the query can use something like "Forms!frmDatePrompt.txtDate" in the query criteria.

Bob