format help. Drop time stamp

[travismp]

I have a field in my database called [RecordDate] which is a date & time stamp. In my query I have

">Date() Or Date()"

to find all tests from today.

Q1.) Is there a better way to write this?
Q1.) I want to remove the time stamp from the result for this query. I need to total a column and Count the number of records for each client but it does not function correctly due to the time stamps. Sooooo how do I drop the time stamp completely in the query results?

thank you!

 

[rainman89]

you can use the left function to remove the timestamp from your results


you can also check this
http://www.access-programmers.co.uk/forums/showthread.php?t=138753
and see if that helps get you on the right track

 

[pbaldy]

What is the Or supposed to accomplish? You can drop the time with either the Format() function or the DateValue() function.

 

[travismp]

I tried

=Format(Date())

with no luck. This will only show records that have no time stamp at all. The recrods with time stamps will not show with this.

 

[pbaldy]

You'd want to use either function on your field, not Date(), which is only the date anyway.

 

[travismp]

You'd want to use either function on your field, not Date(), which is only the date anyway.

I do not understand.

So if my field name is [RecordDate] which function would I use to show all records entered today with no time stamp?

 

[pbaldy]

This type of thing:

SELECT...WHERE Format([RecordDate],"m/d/yy")="12/29/08"

 

[travismp]

OK so if I want this for today, could I do this?

Format([RecordDate],"m/d/yy")="Date()"

 

[pbaldy]

No, the quotes would goof it up ("Date()" becomes a literal string rather than the date). Try this:

WHERE DateValue([RecordDate])=Date()

 

[travismp]

Data type mismatch in criteria expression

 

[travismp]

WHERE (((Format([TestDate],"m/d/yyyy"))=Date()))

This worked. Once I added 4 ys for the year the Date() function worked.

 

[pbaldy]

If you got a data type mismatch on the one using DateValue, I suspect you have Null values in that field somewhere. They would cause that error.

 

[raskew]

Hi -

Agree with Paul. Don't believe it's the DateValue() function causing the problem.

Here's an example (from the debug (immediate) window) of the DateValue() and TimeValue() functions:

x = now()
? x
12/29/2008 7:33:09 PM 

y = datevalue(x)
? y
12/29/2008
 
z = timevalue(x)
? z
7:33:09 PM

Bob