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

 

[gsbelbin]

One way to investigate this is to put a break at the line rs.Open sql, con and then in the Immediate window type

?sql

and return. This will show you what you setting the variable to and let you see if it's what you want.

 

[Ruddles]

I assume cells L1 and L2 contain Excel date serials formatted to display as mm/dd/yyyy (or similar) - that is to say, they are numbers in the approximate region of 40,700 which are displayed on your worksheet as dates. But they still contain numbers.

If you examine the contents of your variable sql, you will be able to confirm this.

The dates in your SQL string should be created as follows:-

"Between #" & [COLOR=red][B]Format([/B][/COLOR]Range("L1").Value[B][COLOR=red],"mm/dd/yyyy")[/COLOR][/B] & "# And #" & [COLOR=red][B]Format([/B][/COLOR]Range("L2").Value[COLOR=red][B],"mm/dd/yyyy")[/B][/COLOR] & "#) AND ... etc.

Try this and compare the contents of your variable sql to the first time to examined it.

 

[lokeshsu]

Hi

i am getting the required sql with the details but still not able to execute the query still i am getting a run time error

 

[lokeshsu]

sql = "SELECT count(expr1) AS temp FROM [SELECT DISTINCT [Union Query].SRNo_Out, Min(Inbound.ActivityCreatedDate) " & _
       "AS MinOfActivityCreatedDate1, Min(Outbound.ActivityCreatedDate) AS MinOfActivityCreatedDate, " & _
       "[B]CalcWorkdays[/B](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)=""Wholesale"") 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;"

the one which is bold (CalcWorkdays) is a function which is in the function defined in the access, so i think the problem exist there

 

[RoryA]

Yep - you can't run that from outside Access.

 

[lokeshsu]

So do we have any other way how we can run outside access

 

[RoryA]

Not unless you can recreate that function in plain SQL, or use a make table query in Access first and then run select queries off that from outside Access.

 

[Ruddles]

You can use DDE to run the query in Access:-

Sub RunAccessQuery()
 
  Dim iChannel As Integer
 
  Shell "MSAccess database1.mdb", vbMinimizedNoFocus
  
  iChannel = DDEInitiate("MSAccess", "System")
  
  DDEExecute iChannel, "[OpenDatabase database1]"
  DDEExecute iChannel, "[SetWarnings 0]"
  DDEExecute iChannel, "[OpenQuery qrySomeQuery]"
  DDEExecute iChannel, "[CloseDatabase]"
  DDEExecute iChannel, "[Quit]"
  
  DDETerminate iChannel
 
End Sub

 

[Norie]

What does that function do?

Is it anything that you couldn't do in Excel?