A tough DateDiff SQL query

[Autoeng]

I have a functioning query that calculates DateDiff minus holidays and weekends based on a start and end date for a report. All is functioning well but I need to create a new query to perform the same calculation on a different set of fields. I am copying the original SQL query to Notepad to make the changes as I know that Access sometimes has problems with altering SQL queries. When I copy the revised query into Access and try to run it I get a "Syntax Error in FROM clause". Below is the original working query and below that the non-working new query. I think that the problem lies in that the original compared fields from two different tables while the new query is working within one table. Any help would be greatly appreciated. Note that it would probably be much easier to print this out as the SQL queries are quite long and complex.

SELECT [tblECNBCNVIP].[ECNBCNVIPId], [tblECNBCNVIP].[ReleaseDate], [tblECNDetail].[PendingDate], DateDiff("d",[tblECNBCNVIP].[ReleaseDate],[tblECNDetail].[PendingDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[PendingDate],2)>5,5-Weekday([tblECNDetail].[PendingDate],2),0)+IIf(Weekday([tblECNBCNVIP].[ReleaseDate],2)=6,1,0)-IIf(Weekday([tblECNBCNVIP].[ReleaseDate],2)>Weekday([tblECNDetail].[PendingDate],2),2,0)+IIf([tblECNBCNVIPtbl].[ReleaseDate]=[tblECNDetail].[PendingDate],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECNBCNVIP INNER JOIN tblECNDetail ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]) LEFT JOIN [SELECT Query4.[tblECNBCNVIP].[ECNBCNVIPId], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECNBCNVIP].[ECNBCNVIPId], 1 AS TEMPVAL
FROM tblHoliday, [tblECNBCNVIP] INNER JOIN [tblECNDetail] ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]
WHERE (((tblHoliday.Holiday) Between [tblECNBCNVIP].[ReleaseDate] And [tblECNDetail].[PendingDate]))) AS Query4
GROUP BY Query4.[tblECNBCNVIP].[ECNBCNVIPId]]. AS Query5 ON [tblECNBCNVIP].[ECNBCNVIPId] = Query5.[tblECNBCNVIP].[ECNBCNVIPId]
WHERE ((([tblECNDetail].[PendingDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECNBCNVIP].[DoNotProcess])<>"Do Not Process"));

The new SQL query that does not function.

SELECT [tblECNBCNVIP].[ECNBCNVIPId], [tblECNDetail].[PendingDate], [tblECNDetail].[SourcingReturnDate], DateDiff("d",[tblECNDetail].[PendingDate],[tblECNDetail].[SourcingReturnDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[SourcingReturnDate],2)>5,5-Weekday([tblECNDetail].[SourcingReturnDate],2),0)+IIf(Weekday([tblECNDetail].[PendingDate],2)=6,1,0)-IIf(Weekday([tblECNDetail].[PendingDate],2)>Weekday([tblECNDetail].[SourcingReturnDate],2),2,0)+IIf([tblECNDetail].[PendingDate]=[tblECNDetail].[SourcingReturnDate],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECNBCNVIP INNER JOIN tblECNDetail ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]) LEFT JOIN [SELECT Query4.[tblECNBCNVIP].[ECNBCNVIPId], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECNBCNVIP].[ECNBCNVIPId], 1 AS TEMPVAL
FROM tblHoliday, [tblECNBCNVIP] INNER JOIN [tblECNDetail] ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]
WHERE (((tblHoliday.Holiday) Between [tblECNDetail].[PendingDate] And [tblECNDetail].[SourcingReturnDate]))) AS Query4
GROUP BY Query4.[tblECNBCNVIP].[ECNBCNVIPId]]. AS Query5 ON [tblECNBCNVIP].[ECNBCNVIPId] = Query5.[tblECNBCNVIP].[ECNBCNVIPId]
WHERE ((([tblECNDetail].[SourcingReturnDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECNBCNVIP].[DoNotProcess])<>"Do Not Process"));

 

[AncientOne]

Have you tried printing this SQL to the debug window to ensure that you haven't missed a space somewhere? It's all too easy to do this when you're editing complex queries in Notepad.

 

[Autoeng]

Thanks for the advice but I haven't ever done this before. How do I do it?

 

[AncientOne]

It's long winded to explain, so I've done it myself.


A quick trawl throws up this:

...GROUP BY Query4.[tblECNBCNVIP].[ECNBCNVIPId]]. AS Query5 ON....

Isn't that last square bracket/dot redundant?

 

[Autoeng]

Removed the ]. Not the problem...

If it helps, after I click ok to the error the curser lands after...

LEFT JOIN [SELECT Query4.[tblECNBCNVIP].[ECNBCNVIPId],

Thanks for trying to help.

 

[AncientOne]

There shouldn't be a square bracket after LEFT JOIN....?


(SELECT [tblECNBCNVIP].[ECNBCNVIPId], 1 AS TEMPVAL should be =1 as TEMPVAL?

 

[AncientOne]

Autoeng, the SQL you call your working query also returns an ERROR IN FROM CLAUSE. Have you transposed the SQL wrongly?

 

[Autoeng]

Square bracket after LEFT JOIN = no luck.

I don't understand 2nd post.

I think the problem lies within...

FROM (tblECNBCNVIP INNER JOIN tblECNDetail ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]) LEFT JOIN [SELECT Query4.[tblECNBCNVIP].[ECNBCNVIPId], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECNBCNVIP].[ECNBCNVIPId], 1 AS TEMPVAL
FROM tblHoliday, [tblECNBCNVIP] INNER JOIN [tblECNDetail] ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]

In the working query I am pulling dates from 2 tables (tblECNBCNVIP.ReleaseDate & tblECNDetail.PendingDate)

but in the new query I am only pulling date from one table
(tblECNBCNVIP.PendingDate & tblECNDetail.SourcingReturnDate)

The original creates a join between the 2 tables using ECNBCNVIPId as the common field but with the new query there would be no reason to establish this join. I've tried restructuring the statement to eliminate the join but haven't had any luck yet.

Thanks for the effort AncientOne.

 

[AncientOne]

Hi Auto. Yes, but if you paste your first statement (the one that works) into a query, it returns the same error at the same place. So you haven't copied it correctly or it would work for me too.

Each SQL SELECT Clause should be able to work on its own, so just pasting

Select * FROM (tblECNBCNVIP INNER JOIN tblECNDetail ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]) LEFT JOIN [SELECT Query4.[tblECNBCNVIP].[ECNBCNVIPId], Sum(Query4.TEMPVAL) AS HCOUNT
into a query should not generate any synatax errors. It might not work because of impossible joins, but it should be OK for syntax.

Can you post the SQL from your working query again, pasting it directly from the query, so we know it's right, please.

 

[Autoeng]

You were correct AncientOne. I found an error in my original posting.

IIf([tblECNBCNVIPtbl].[ReleaseDate]=[tblECNDetail].[PendingDate],1,0) AS weekdays, Query5.HCOUNT

Here is the query straight from the window but I understand where you are coming from. If I copy the working query to Notepad, make NO changes, copy from Notepad to a new query window IT DOESN'T WORK! What the??? I watched it work from the SQL window then copied and still same result! I also tried the paste to WordPad and same result!

SELECT [tblECNBCNVIP].[ECNBCNVIPId], [tblECNBCNVIP].[ReleaseDate], [tblECNDetail].[PendingDate], DateDiff("d",[tblECNBCNVIP].[ReleaseDate],[tblECNDetail].[PendingDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[PendingDate],2)>5,5-Weekday([tblECNDetail].[PendingDate],2),0)+IIf(Weekday([tblECNBCNVIP].[ReleaseDate],2)=6,1,0)-IIf(Weekday([tblECNBCNVIP].[ReleaseDate],2)>Weekday([tblECNDetail].[PendingDate],2),2,0)+IIf([tblECNBCNVIP].[ReleaseDate]=[tblECNDetail].[PendingDate],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECNBCNVIP INNER JOIN tblECNDetail ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]) LEFT JOIN [SELECT Query4.[tblECNBCNVIP].[ECNBCNVIPId], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECNBCNVIP].[ECNBCNVIPId], 1 AS TEMPVAL
FROM tblHoliday, [tblECNBCNVIP] INNER JOIN [tblECNDetail] ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]
WHERE (((tblHoliday.Holiday) Between [tblECNBCNVIP].[ReleaseDate] And [tblECNDetail].[PendingDate]))) AS Query4
GROUP BY Query4.[tblECNBCNVIP].[ECNBCNVIPId]]. AS Query5 ON [tblECNBCNVIP].[ECNBCNVIPId] = Query5.[tblECNBCNVIP].[ECNBCNVIPId]
WHERE ((([tblECNDetail].[PendingDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECNBCNVIP].[DoNotProcess])<>"Do Not Process"));

 

[AncientOne]

Well, I still get an error in FROM Clause! Something is screwy here. Can you post a screen dump of your query in design view?

 

[Autoeng]

Got it (with a little help from a friend)...

SELECT [tblECNBCNVIP].[ECNBCNVIPId], [tblECNDetail].[PendingDate], [tblECNDetail].[SourcingReturnDate], DateDiff("d",[tblECNDetail].[PendingDate],[tblECNDetail].[SourcingReturnDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[SourcingReturnDate],2)>5,5-Weekday([tblECNDetail].[SourcingReturnDate],2),0)+IIf(Weekday([tblECNDetail].[PendingDate],2)=6,1,0)-IIf(Weekday([tblECNDetail].[PendingDate],2)>Weekday([tblECNDetail].[SourcingReturnDate],2),2,0)+IIf([tblECNDetail].[PendingDate]=[tblECNDetail].[SourcingReturnDate],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECNBCNVIP INNER JOIN tblECNDetail ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]) LEFT JOIN [SELECT Query4.[tblECNBCNVIP].[ECNBCNVIPId], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECNBCNVIP].[ECNBCNVIPId], 1 AS TEMPVAL
FROM tblHoliday, [tblECNBCNVIP] INNER JOIN [tblECNDetail] ON [tblECNBCNVIP].[ECNBCNVIPId] = [tblECNDetail].[ECNBCNVIPId]
WHERE (((tblHoliday.Holiday) Between [tblECNDetail].[PendingDate] And [tblECNDetail].[SourcingReturnDate]))) AS Query4
GROUP BY Query4.[tblECNBCNVIP].[ECNBCNVIPId]]. AS Query5 ON [tblECNBCNVIP].[ECNBCNVIPId] = Query5.[tblECNBCNVIP].[ECNBCNVIPId]
WHERE ((([tblECNDetail].[SourcingReturnDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECNBCNVIP].[DoNotProcess])<>"Do Not Process"));

Thanks for all the time you've taken AncientOne.