Query works in Access but not in ASP using ADO

[rewdee]

I have a crosstab that works in MS Access but not in the ASP page. The query is:

sql = "TRANSFORM Sum(UGNightShift.Value) AS [The Value] " & _
    "SELECT UGNightShift.EntryDate, UGNightShiftComments.Shift, UGNightShiftComments.ShiftSupervisor, UGNightShift.GroupID " & _
    "FROM (Calculations INNER JOIN UGNightShift ON Calculations.[Calculation Index] = UGNightShift.[Calculation Index]) " & _
    "INNER JOIN UGNightShiftComments ON UGNightShift.EntryDate = UGNightShiftComments.EntryDate " & _
    "WHERE UGNightShift.EntryDate = #" & Yesterday & " 7:00:00 PM# " & _
    "GROUP BY UGNightShift.EntryDate, UGNightShiftComments.Shift, UGNightShiftComments.ShiftSupervisor, UGNightShift.GroupID " & _
    "ORDER BY UGNightShift.EntryDate DESC " & _
    "PIVOT Calculations.Label;"

The code works nicely if I take out the where clause. It does not appear to be a problem with date because I've tried filtering on another parameter and it does not work either.

Any ideas? Please!!!

Thanks,
Rewdee

 

[Dynapen]

Put the SQL statment into a Query in the Access database, and just pass it a simple select with your where clauses. it will parse faster and take up less memory in the IIS.

 

[rewdee]

Thanks Dynapen for your response.

I can't figure what I did but the query works now. I don't if something with the mdb file being open (I can't see why??? but that's the only thing that is different that I can see).

I will try your suggestion though if I get inconsistent results (it works, it doesn't) though.

Thanks,
Rewdee