Records for Current Date

[efrank]

Can anyone help me figure out how to show only the records from a table in a query for the current date. For example I have a table that has many records for each day since last year. I want to show on a subform only the records that were entered today. (within the last 24 hours) or today's date. I have a field in the table and query named date that automatically gets filled in when data is entered into the table. I want this to be a dynamic query that shows the results on the subform whenever the form is opened and I do not want to have to query the user for the current date.
I hope I'm explaining myself clearly.
Any help would be appreciated greatly.
Thanks,

efrank

 

[PHV]

Something like this (SQL snippet) ?
WHERE Int([yourTable].[yourDateField])=Int(Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[efrank]

My table name is Inspection Log and the field name in the table is Date.

So in the query criteria for the date field I wrote

WHERE Int([Inspection Log].[Date])=Int(Date())

and got an error message:

The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

What does that mean?

efrank

 

[Melagan]

This may or may not be related to the invalid syntax error, but I just wanted to mention that it is dangerous to have fields named after reserved words like "Date". You'd be better off with a slightly more descriptive field name like "OrderDate" or "OdrDate"; it helps to avoid confusion by both you and Access.

What happens if you do this?

SELECT Int(t.[date]) as intDate, t.[Date]
FROM [Inspection Log] as t
WHERE t.[Date]=#12/28/2005#;

Assuming you have some values in your table for 12/28/2005, you should see a list of dates in one column and a list of integer values in another.

 

[efrank]

Melagan,
Yes like a dummy I named the field Date. I know better than to do this but just wasn't thinking at the time. I have now renamed the field InspDate.

I retried the query with this

Where Int([Inspection Log].[InspDate])=Int(Date())

and got back the same error.

I'm not sure where to put the code you gave me.
Do I put that in the criteria field for the InspDate of the query?

efrank

 

[Melagan]

Create a new query and go to SQL view. You can cut and paste this code:

SELECT Int(t.[InspDate]) as intDate, t.[InspDate]
FROM [Inspection Log] as t
WHERE t.[InspDate]=#12/28/2005#;

Again assuming you have some InspDate values in your table that = 12/28/2005. If not, change the criteria to dates you have populated.
You should see results like:
InspDate intDate
12/28/2005 38714
12/28/2005 38714
12/28/2005 38714

After you get that to come out, we'll move on.

 

[efrank]

O.K. Melagan....
That works. But I don't want to have to keep changing the date in the query. I want this to be dynamic so that whenever the user opens the form it displays the data or records for whatever date it currently is.
Does that make sense?

efrank

 

[CosmoKramer]

Unless I'm missing something, and as long as InspDate is defined as a Date/Time field in your table, you should simply enter =Date() as the criteria for InspDate in your query....

Si hoc legere scis, nimis eruditionis habes

 

[efrank]

CosmoKramer.....
You weren't missing anything. I tried that and it works great! I thought I had tried that before.

Thanks a bunch.

efrank

 

[Melagan]

efrank,
I just wanted to make sure that the data types in your table were set up correctly; now that I know you are getting the correct integer values out of your date fields, you can use PHV's clever code above for your query. Here is the example SQL:

SELECT t.[InspDate]
FROM [Inspection Logs] As t
WHERE Int(t.[Date])=Int(Date())

 

[Melagan]

I like converting the dates to integer values for comparison =P