Select query, format to remove time stamp.

[travismp]

I need help dropping a time stamp. I am designing a select query using totals.

MaxDateBilled: DateBilled

This will show me the most recent record entered into my system. It has a date & time stamp thou. How can I change this to only show me the date? I do not want the time stamp listed or factored into this query.

 

[boblarson]

MaxDateBilled: DateValue([DateBilled])

 

[travismp]

It gave me "Data type mismatch in criteria expression"
Here is my exact SQL code without the formatting.

SELECT Max(tbl_TESTS.DateBilled) AS MaxDateBilled
FROM tbl_TESTS
HAVING (((Max(tbl_TESTS.DateBilled)) Is Not Null))
ORDER BY Max(tbl_TESTS.DateBilled) DESC;

Here is my code with the changes & error

SELECT Max(DateValue([DateBilled])) AS MaxDateBilled
FROM tbl_TESTS
HAVING (((Max(DateValue([DateBilled]))) Is Not Null))
ORDER BY Max(DateValue([DateBilled])) DESC;

Am I missing something?

Thanks Bob.

 

[boblarson]

Travis:

I just tried it out in a sample db (with same names and all) and it worked fine for me. Are you absolutely sure that [DateBilled] is a true Date/Time field and not Text?

 

[travismp]

Yes it is. The Data Type is Date/Time with a Format "General Date"

 

[travismp]

Also I personally appreciate that you took the time to set up a demo database with the same naming structure to test the system.

 

[boblarson]

Strange - this is what I get:

 

[travismp]

I just looked at every character & it is EXACTLY as yours however I still get the

"Data type mismatch in criteria expression" error

SELECT Max(DateValue([DateBilled])) AS MaxDateBilled
FROM tbl_TESTS
HAVING (((Max(DateValue([DateBilled]))) Is Not Null))
ORDER BY Max(DateValue([DateBilled])) DESC;

I wonder if I have some corrupt information in my table? I am not sure what else it can be. In my original code I posted to simply return the max date & time it works great.

I am working with Access 2003 could that be the issue?

 

[boblarson]

I am working with Access 2003 could that be the issue?

My test was done in Access 2003 SP2.

 

[travismp]

SP3 here. think that is it?

 

[boblarson]

Could be - have you applied the hot fix yet?

http://support.microsoft.com/kb/945674

 

[JoelM]

Probably have a NULL value which is causing the SELECT to fail. The HAVING operation only runs if the SELECT is successful. Try adding a "WHERE DateBilled is not NULL" clause so that you do not select rows with NULL DateBilled values. You can drop the HAVING clause.