Query not calling all data

[MarkNie]

Hi All

Hope you are doing well today.

I am a bit stuck on the following query, any help on this will be greatly appreciated.

Code:

SELECT tblTracking.PortfolioCode, tblTracking.[CRG Deadline], qrybDistributionDeadline.Date, Count(tblTracking.[CRG Deadline]) AS [CountOfCRG Deadline], tblTracking.ProductType
FROM tblTracking LEFT JOIN qrybDistributionDeadline ON tblTracking.TrackingID = qrybDistributionDeadline.TrackingID
GROUP BY tblTracking.PortfolioCode, tblTracking.[CRG Deadline], qrybDistributionDeadline.Date, tblTracking.ProductType, Format(nz([qrybDistributionDeadline].[Date],Date()),"Short Date")
HAVING (((tblTracking.PortfolioCode)<>"630541" And (tblTracking.PortfolioCode)<>"630542") AND ((tblTracking.[CRG Deadline])<Date()) AND ((tblTracking.ProductType)<>"IT Board Papers") AND ((Format(nz([qrybDistributionDeadline].[Date],Date()),"Short Date"))>Format([CRG Deadline],"Short Date"))) OR (((qrybDistributionDeadline.Date) Is Null))
ORDER BY tblTracking.PortfolioCode
WITH OWNERACCESS OPTION;

I have 2 tables, one which holds all the clients data and the second which the user uses to sign off the date which the report was sent out.

What I am trying to do is show all the deadlines that where missed even if there is a null value in the field. The 2 fields I wish to do this by is CRG Deadline and qrybDistributionDeadline.Date. The query works when I run it but I am not getting all the values that I require. There seems to be some fields missing.

Any help on this would be great.

Thanks
Regards
Mark

 

[MarkNie]

I think the problem might lie in the date. As it looks like it is only looking at the first dd entry of the date and not the whole date.

Which means:

if the CRG Deadline is 21/01/2007 and the sign off date is 02/02/2007 it says that it is on time as the first 2 numbers are less than the first 2 numbers of the CRG Deadline.

 

[PHV]

Why using Format (ie text) for dates compare ?
SELECT T.PortfolioCode, T.[CRG Deadline], D.Date, Count(*) AS [CountOfCRG Deadline], T.ProductType
FROM tblTracking AS T LEFT JOIN qrybDistributionDeadline AS D ON T.TrackingID = D.TrackingID
WHERE Not (T.PortfolioCode In('630541','630542))
AND T.[CRG Deadline]<Date() AND t.ProductType<>'IT Board Papers'
AND (Nz(D.Date,Date())>T.[CRG Deadline] OR D.Date Is Null)
GROUP BY T.PortfolioCode, T.[CRG Deadline], D.Date, T.ProductType
ORDER BY T.PortfolioCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkNie]

Hi PHV

Well the problem is that the D.Date has time behind it. This causes it to say that some of the reports are late as they have a time behind it. Such as:

CRG Deadline 01/01/2007
D.Date 01/01/2007 09:00:12

This is why I was using formats.

Thanks for your help so far.

Regards
Mark

 

[PHV]

So, what about this ?
SELECT T.PortfolioCode, T.[CRG Deadline], CDate(CLng(D.Date)) AS D_Date, Count(*) AS [CountOfCRG Deadline], T.ProductType
FROM tblTracking AS T LEFT JOIN qrybDistributionDeadline AS D ON T.TrackingID = D.TrackingID
WHERE Not (T.PortfolioCode In('630541','630542))
AND T.[CRG Deadline]<Date() AND t.ProductType<>'IT Board Papers'
AND (CLng(Nz(D.Date,Date()))>T.[CRG Deadline] OR D.Date Is Null)
GROUP BY T.PortfolioCode, T.[CRG Deadline], CDate(CLng(D.Date)), T.ProductType
ORDER BY T.PortfolioCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkNie]

Hi PHV

When I drop this into the query it gives me an error invalid use of Null

Any Ideas

Thanks
Mark

 

[PHV]

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

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkNie]

Hi PHV

Thanks for you help!!

It works perfectly.

Have a great day

Cheers
Mark

 

[MarkNie]

Hi PHV

Sorry seems to be a problem with that query, it is adding a day to the D_Date auch as:

D.Date = 26/01/2007
when the query is generated = 27/01/2007

Any idea on this one.

Thanks
Regards
Mark

 

[MarkNie]

It seems to be that if the time of that specific date is over 12:00:00 then it throws it to the next day.

I hope this helps.

Thanks
Regards
Mark

 

[PHV]

Use Int instead of CLng

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886