code working in access but not able to run using excel

lokeshsu

Board Regular
Joined
Mar 11, 2010
Messages
178
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.
Code:
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

Code:
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
 
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.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.:)
 
Upvote 0

Forum statistics

Threads
1,224,606
Messages
6,179,866
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top