Tech Support Guy banner
Status
Not open for further replies.

MS Access 2000-Pulling Current Day's Records From Time Stamp

1K views 4 replies 3 participants last post by  lp092jl 
#1 ·
I have a table with a Date/Time field called Date Created, it is in the General Date format, and it is a time stamp (EX: 12/3/2004 3:00 AM). I know that if you have a date field that is formatted as a Short Date you can easily pull records in a query based off of a current date (EX: Date() as the criteria). I have tried using the Date() criteria as a parameter for a query, but I am unable to pull the current date's records. The query will work if I test the "Between #12/1/2004# and #12/4/2004#" parameter range, but I would like to be able to pull records for the current date without having to change the date range or even the date field to a short date in the main table. I've even tried converting the date within a query to display the date as a short date, by adjusting the field properties in the query, but the Date() criteria still doesn't work. Is there any other type of syntax that I could use within the query to make this work? Your help would be greatly appreciated.
 
#2 ·
Let me restate the problem to see if I understand it correctly. If so then the answer is far simpler than you think…

I understand that you have a table and in that table one of the fields is a date field (a timestamp that happens to be formatted as a General Date). You want to create a query that pulls out records where this data field is equal to the current date and you want to obtain the current date by using the "Date" function. Correct so far?

If so, there's no need to generate ascii strings enclosed in "#'s", simply go into the Access SQL Query Builder and enter "Date()" (without the double quotes) into the criteria cell in the column where the timestamp field appears. In SQL this would look like:

SELECT Table1.Field1, Table1.EtCetra, Table1.TimeStampField
FROM Table1
WHERE (((Table1.TimeStampField)=Date()));

You simply have to test for equality between the time stamp field and the variant returned from the Date() function. Did that help?
 
#3 ·
I've actually tried that, and I had no luck running the criteria, it looks as though Access is looking for critera that matches the exact timestamp within the record.

I have however found an easier way to pull the current day's records.

Using the "Between" date range criteria, I was able to pull the current date using a variation of Date().

I used:

Between Date() And Date()+1 and it was able to pull up what I needed.

Thanks for your help!
 
#4 ·
Another thing you could do is use the DateValue function in your criteria - e.g. if you are in query design view go to a blank column, in the "field" row type:

DateValue(Table1!TimeStampField)

and in the Criteria row type:

Date()

in sql it looks something like:

WHERE ((DateValue(Table1.TimeStampField))=Date())

The DateValue function will return the date portion of anything which can be resolved as a date, including datetime values and even strings with formats that can be recognized as dates.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top