SQL statement from passed Parameters

[Rpschwar]

I have a Query that passes parameters and I want to see what the SQL statement is after the parameters are passed. Here is the Query with Parameters:
PARAMETERS Forms!RosterDialogBox!BeginningDate DateTime, Forms!RosterDialogBox!EndingDate DateTime;


SELECT tblAttendance.RehearsalDate, tblAttendance.ID, tblAttendance.DateEntered, tblMembers.FirstName, tblMembers.LastName, tblInstrument.Instrument, tblInstrument.GroupNbr, tblInstrument.OrderNbr
FROM (tblAttendance INNER JOIN tblMembers ON tblAttendance.ID = tblMembers.ID) INNER JOIN tblInstrument ON tblMembers.Instrument = tblInstrument.Instrument
WHERE (((tblAttendance.RehearsalDate) Between [Forms]![RosterDialogBox]![BeginningDate] And [Forms]![RosterDialogBox]![EndingDate]))
ORDER BY tblAttendance.RehearsalDate DESC , tblInstrument.GroupNbr, tblInstrument.OrderNbr, tblMembers.LastName, tblMembers.FirstName;

I want to see an output like:
SELECT tblAttendance.RehearsalDate, tblAttendance.ID, tblAttendance.DateEntered, tblMembers.FirstName, tblMembers.LastName, tblInstrument.Instrument, tblInstrument.GroupNbr, tblInstrument.OrderNbr
FROM (tblAttendance INNER JOIN tblMembers ON tblAttendance.ID = tblMembers.ID) INNER JOIN tblInstrument ON tblMembers.Instrument = tblInstrument.Instrument
WHERE (((tblAttendance.RehearsalDate) Between #1/1/2016# And #1/1/2017#))
ORDER BY tblAttendance.RehearsalDate DESC , tblInstrument.GroupNbr, tblInstrument.OrderNbr, tblMembers.LastName, tblMembers.FirstName;

[aytee111]

You don't need the Parameters, the SQL is ok just as it is, with referencing the dates from the form.

Why do you need to see actual values instead of form references? You will have to create the SQL in VBA for this.

[Rpschwar]

I don't think you understood my question. The first query with parameters is what I have. Once I enter the parameters I want to see the SQL statement with the parameters inserted like I show in the second sql statement. How can I display that sql statement?

[aytee111]

Like I said, VBA. Where and how do you want to display it?

[Rpschwar]

This is within a form or report load and I displayed the recordset.sql to show the sql line with parameters but I want to assign the actual sql statement to a variable that I would like to pass to another form without it asking for the parameters again. So I want the sql with the actual values replaced it the sql line.

[aytee111]

The second form can also use the same parameters, if the form "RosterDialogBox" is still open.

How are you passing it to the second form? One option would be to use a global variable.

Then:
variablename="copy and paste from the SQL above ... WHERE (((tblAttendance.RehearsalDate) Between #" & [Forms]![RosterDialogBox]![BeginningDate] & "# And #" & [Forms]![RosterDialogBox]![EndingDate] & "#))........"

Or if you are inside the form you can use the Me keyword.

[Rpschwar]

I'm just wanting a simple command the will show the sql statement like the .sql statement gives you. If I can run the recordset, why can't I just see the sql string with the parameters replaced?

[ssanfu]

I'm not sure whether you have a saved query in Access or SQL in VBA.
But as aytee said "You will have to create the SQL in VBA".....

I'm just wanting a simple command the will show the sql statement like the .sql statement gives you

There is no "simple command". To show the .SQL statement means you are using VBA to get the SQL from a variable in code or from a saved query. Using VBA, you could create a query def object, set the query def object to the saved query, then get the SQL using the .sql property. But it still wouldn't have the actual dates.

You could use the RecordSource property to get the SQL of the saved query - you would/could see the reference to the form/control the date values were taken from, but still wouldn't be able to see anything like

WHERE (((tblAttendance.RehearsalDate) Between #1/1/2016# And #1/1/2017#))

<snip> If I can run the recordset, why can't I just see the sql string with the parameters replaced?

Because the SQL with the actual date values only exist for a split second. When the query is executed, the SQL is generated, then executed and is gone into the ether. So there is no way to "grab" the SQL that is generated with the dates in the SQL statement for that nano-second that it exists.

And actually you don't "run the recordset". You execute (run) a SQL query that returns a record set object.


Options:
You could hard code the dates in a saved query.... (not very useful IMO)

You could use VBA to modify the SQL of a saved query to set the where condition and use the saved query as the record source for the form/report. (Programmatically hard coding the dates)

You could use a saved query without a where clause, then add a where clause in the OpenForm/OpenReport command (Docmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs) ) ***** I would use this option.


My $0.02.......

[Rpschwar]

So I guess the best option is not to use the parameter option but have 2 input boxes for the dates and to just build the sql with those dates.

[ssanfu]

Sorry, but yes.

If you must be able to see the SQL with the dates, that is what you will have to do..

Good luck with your project.....