Query is changing the date!!

[MarkNie]

Hi All

PHV has helped me so far but I am getting another problem and any help would be great.

Problem:

I have a query which is working great except for one thing when I try and change the date from a long date dd/mm/yyyy hh:mm:ss into a sort date dd/mm/yyyy. It changes the actual date if the time is later than 12:00:00 such as:

Actual date in table = 01/02/2007 12:30:00
After I run the query
Date changes to = 02/02/2007

If the time is less than 12:00:00 the date stays the same such as:

Actual date in table = 01/02/2007 11:30:00
After I run the query
Date stays the same = 01/02/2007

Here is the query, if you would like to look at how I got this far please look at thread 1330813

Query:

SELECT tblTracking.PortfolioCode, tblTracking.[CRG Deadline], IIf(IsNull(qrybDistributionDeadline.Date),Null,CDate(CLng(qrybDistributionDeadline.Date))) AS D_Date, Count(*) AS [CountOfCRG Deadline], tblTracking.ProductType
FROM tblTracking AS tblTracking LEFT JOIN qrybDistributionDeadline AS qrybDistributionDeadline ON tblTracking.TrackingID = qrybDistributionDeadline.TrackingID
WHERE Not ( tblTracking.PortfolioCode In("630541","630542"))
AND tblTracking.[CRG Deadline]<Date()
AND (CLng(Nz(qrybDistributionDeadline.Date,Date()))> tblTracking.[CRG Deadline] OR qrybDistributionDeadline.Date Is Null)
GROUP BY tblTracking.PortfolioCode, tblTracking.[CRG Deadline], IIf(IsNull(qrybDistributionDeadline.Date),Null,CDate(CLng(qrybDistributionDeadline.Date))), tblTracking.ProductType
ORDER BY tblTracking.PortfolioCode;


Thanks in advance for any help..
Regards
Mark

 

[HarleyQuinn]

I would say that using the CLng function is causing the rounding of the date to the nearest whole number, so with a time portion after 11:59:59 it will round it up. You could try to use the Int() function first to strip any time portion from the underlying number (Int will just take the whole number regardless of any values after the decimal point).

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[randy700]

Or, perhaps, the FORMAT function.


Randy