Truncate Date/Time to only return Date (1 Viewer)

mcgraw

Registered User.
Local time
Today, 00:00
Joined
Nov 13, 2009
Messages
77
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!
 

mcgraw

Registered User.
Local time
Today, 00:00
Joined
Nov 13, 2009
Messages
77
When I do DateVaule() and run the query, I get #Error?

Here's the query:
Code:
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

Smeghead
Local time
Today, 00:00
Joined
Jan 12, 2001
Messages
32,059
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

Immoderate Moderator
Staff member
Local time
Today, 02:00
Joined
Feb 28, 2001
Messages
27,001
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

New member
Local time
Today, 00:00
Joined
Jul 12, 2018
Messages
1
you could just use the int() function? does the same thing as trunc()
 

Users who are viewing this thread

Top Bottom