Run report based on SQL

[eugz]

Hi All.
I tried to run report and used such code:

Private Sub lblDelivery_Click(Sql As String)
    Sql = "SELECT Receiving.ReceiveID, Receiving.Date, Devices.Device, qLocation.Location, Receiving.Amount" & _
    "FROM Devices" & _
    "INNER JOIN Receiving" & _
    "ON Devices.DeviceID = Receiving.DeviceID)" & _
    "INNER JOIN qLocation ON Receiving.ReceiveID = qLocation.ReceiveID" & _
    "WHERE Receiving.Date Between [Enter First Date] And [Enter Last Date]" & _
    "ORDER BY Devices.Device, Receiving.Date;"
    
    stDocName = "rDelivery"
    DoCmd.OpenReport stDocName, acPreview
    DoCmd.Maximize
    
    DoCmd.RunSQL Sql

End Sub

What is my problem?
Thanks.

 

[mhartman]

Hello:

I believe you need to set the RecordSource property of your report to your SQL statement!


rDelivery.RecordSource = Your SQL Statement

Regards
Mark

 

[eugz]

Thank Mark for replay.
When I use your suggestion I got run-time error '424' Object required.

Previously I had run-time error '3075' Syntax error (missing operator) in query expression:
"[Devices].[DeviceID] = [Receiving].[DeviceID] INNER JOIN qLocation ON [Receiving].[ReceiveID] = [qLocation].[ReceiveID]"

Do you have idea to fixed it?
Thanks

 

[pbaldy]

You certainly can't do it your way, and while Mark is on the right track, you'd either have to open the report in design view or make the variable global and use it in the report's open event. The real question is why you're doing it this way, rather than simply making that SQL the permanent source for the report. There's nothing dynamic about it, so no reason for it to be set in code.

Btw, you'd also run into spacing problems with that SQL.

 

[eugz]

I would like to open report rDelivery by click lable lblDelivery and get first message to enter Start Date and then get second message to enter End Date. If you know how to do that for better way I will appreciate if you show how to do it.
Thanks.

 

[pbaldy]

Sure; either enter that SQL directly in the report's record source property (without the quotes "&" and line continuations characters of course) and save it that way, or create a saved query with that SQL and base the report on that saved query. Either way, you'll get the 2 date prompts when running the report. You really only need to build SQL in VBA when there's something dynamic about it (something about the structure of the SQL itself changes).

 

[eugz]

Thaks it works.