Truncate Date/Time to only return Date

[mcgraw]

I'm looking for a function in Access 2007 that will allow me to run a query that for a date field will only return the dd/mm/yyyy, and strip the time that is at the end of the line.

I've done some searches, but all I've been able to come up with is Trunc(), which apparently is only an Oracle function?

Any help would be MUCH appreciated!

 

[boblarson]

DateValue(YourFieldName)

 

[mcgraw]

When I do DateVaule() and run the query, I get #Error?

Here's the query:

SELECT DateValue([ci_issue_status].[CS_CREATE_DTS]) AS Expr1, Count(ci_lookup_status.CL_Status) AS CountOfCL_Status, ci_lookup_status.CL_STATUS
FROM qryStatusMaxDTE INNER JOIN (ci_issue_status LEFT JOIN ci_lookup_status ON ci_issue_status.FK_CLK_STATUS = ci_lookup_status.PK_CL_ID) ON (qryStatusMaxDTE.MaxOfCS_CREATE_DTS = ci_issue_status.CS_CREATE_DTS) AND (qryStatusMaxDTE.FK_CI_ISSUE_ID = ci_issue_status.FK_CI_ISSUE_ID)
GROUP BY ci_lookup_status.CL_STATUS, ci_issue_status.CS_CREATE_DTS
HAVING (((ci_lookup_status.CL_STATUS)="Approved"));

So, what it is trying to do is get a count of systems that were approved on each day to create a timeline report.

Thanks!

 

[boblarson]

What is the EXACT error message you are getting? It looks to me that your whole SQL string is not formed properly (perhaps you should use the QBE grid to set it up) because your GROUP BY needs to include ALL of the fields that you are selecting and the Count field.

 

[The_Doc_Man]

If you have a date/time field, convert it for query purposes with something such as Format$([datetimefield],"Short Date")

If that isn't what you REALLY wanted, a query can also cheat in this way...

CDate(CDbl(CLng(CDbl([datetimefield]))))

Not for the squeamish, though. This makes use of the fact that a date field is a CAST of a Double field, which can be truncated by conversion to LONG format safely (as a date). Contemporary dates are less than 100,000 when truncated this way, so a LONG is totally adequate to hold the value.

It should be noted that nesting 4 deep like that gives you sucky performance if it is a big query, even though the Date-->Double and Double-->Date conversions are essentially a non-action. But the call overhead still chews up a few CPU cycles.

 

[linear_d]

you could just use the int() function? does the same thing as trunc()