code working in access but not able to run using excel

[lokeshsu]

Hi all,

I have a query below which works in access data base but when i tried to run the same query from excel i am getting a run time error. Need help for the below query.

SELECT count(expr1) AS temp
FROM [SELECT DISTINCT [Union Query].SRNo_Out, Min(Inbound.ActivityCreatedDate) AS MinOfActivityCreatedDate1, Min(Outbound.ActivityCreatedDate) AS MinOfActivityCreatedDate, CalcWorkdays(MinOfActivityCreatedDate1,MinOfActivityCreatedDate) AS expr1
FROM ([Union Query] INNER JOIN Inbound ON [Union Query].SRNo_Out = Inbound.SRNo_In) INNER JOIN Outbound ON [Union Query].SRNo_Out = Outbound.SRNo_Out
WHERE ((([Union Query].Segment)="Team") AND ((Inbound.OpenedDate) Between #7/1/2011# And #7/31/2011 23:59:59#) AND ((Outbound.OpenedDate) Between #7/1/2011# And #7/31/2011 23:59:59#) AND (([Union Query].Priority)="1-ASAP"))
GROUP BY [Union Query].SRNo_Out]. AS SQ
WHERE expr1<=1;

And below is the code which i tried in excel VBA

sql = "SELECT count(expr1) AS temp FROM [SELECT DISTINCT [Union Query].SRNo_Out, Min(Inbound.ActivityCreatedDate) " & _
       "AS MinOfActivityCreatedDate1, Min(Outbound.ActivityCreatedDate) AS MinOfActivityCreatedDate, " & _
       "CalcWorkdays(MinOfActivityCreatedDate1,MinOfActivityCreatedDate) AS expr1 FROM ([Union Query] INNER JOIN " & _
       "Inbound ON [Union Query].SRNo_Out = Inbound.SRNo_In) INNER JOIN Outbound ON [Union Query].SRNo_Out = Outbound.SRNo_Out " & _
       "WHERE ((([Union Query].Segment)=""Team"") AND ((Inbound.OpenedDate) " & _
       "Between #" & Range("L1").Value & "# And #" & Range("L2").Value & "#) AND ((Outbound.OpenedDate) " & _
       "Between #" & Range("L1").Value & "# And #" & Range("L2").Value & "#) AND (([Union Query].Priority)=""1-ASAP"")) " & _
       "GROUP BY [Union Query].SRNo_Out]. AS SQ WHERE expr1<=1;"
     MsgBox sql

rs.Open sql, con

 

[lokeshsu]

This function will calculate the difference between 2 days excluding the weekends. so what i did is i took the function in the access and pasted in the excel and ran the function from excel it self and it worked.

 

[Norie]

Glad you got it sorted.

If that worked you might also want to look into using some of the built-in Excel worksheet functions for this sort of calculation.

eg NETWORKDAYS

Might not make much difference but if you have a lot of records it could speed up calculation.