Using TempVars in field definitions in SQL queries

[Petrol]

Is there anything intrinsically wrong in using a TempVar in a field definition of an Access SQL query?
As a simplified test I have witten a simple query qry_LOI3 thus:

 SELECT EventTypes.EventCode, [TempVars]![Drv] AS Expr1
FROM EventTypes;

The datasheet it generates is shown in the attached screenshot.
I then try to open it as a recordset, thus:

 Public Sub TestProc()
Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_LOI3", dbOpenForwardOnly)
End Sub

This gives Error 3061 - "Too few parameters. Expected 1."
Same result if I open as dbOpenDynaset.
??
Screenshot.docx

[Nauticalgent]

It is the way the UI resolves the TempVar as opposed to DAO. Try this:

Set rs = db.OpenRecordse t("SELECT EventCode, '" & TempVars("Drv") & "' AS Expr1 FROM EventTypes")

Disclaimer, I have not tested this, I am shooting from the hip...

[NeoPa]

Interesting question Petrol.

Your picture is unfortunately unavailable to me. You should post it as an image (.JPG) file wrapped in <IMG> ... </IMG> tags.

That said, I tried what you have & it opens fine when opened simply by the operator & complains when done from code.

NauticalGent provides a way to avoid falling over this by doing what you want another way. It doesn't quite allow you to do what you want to exactly, but provides a way to get the same result in those circumstances where it's practicable. You may find it helpful.

What you see when running it currently is just a design limitation I'm afraid.

[Petrol]

Thank you, NauticalAgent and NeoPa, for your help.

NeoPa, thanks for the tip about pasting images; I wasn't aware of the <IMG> tag; I have now used it below.
NauticalAgent, I ran your untested code and it worked! So we're on the right track.

Unfortunately, I had oversimplified my query in an attempt to make it usable in my post, and I am struggling to apply your principles to the actual case. I have now generated a more realistic test case, using a dummy table (tblLOIX) and attempting to use the actual SQL of the query. However, after looking up AI on single and double quotes in SQL queries I still can't get it right. Could somebody please point me in the right direction?
The actual SQL of the updated qry_LOI3, copied from the ACCESS SQL view, is as follows:

 SELECT tblLOIX.*, "BSB " & [Tempvars]![BankAccountBSB] & ", Account No. " & [TempVars]![BankAccountNumb er] & " (" & [Tempvars]![BankAccountName] & ")" AS BankDetails,
[Fees_payable]-[Fees_received] AS Balance, [Para5a] & [Balance] & [Para5b] AS Para5_1, [BankDetails] & [Para5c] & " " & [BankAccountName] & ". " AS Para5_2, IIf(([Balance]>0 And Nz([Para5a])<>""),([Para5_1] & " " & IIf(([Who_Pays]="P" Or [Who_Pays]="B"),[Para5_2],"")),"") AS Para5, LOIPayerText.P2 ShowBank, LOIPayerText.P2 PaymentArrangem ents, CommunitiesV4.T hisComm
FROM CommunitiesV4, tblLOIX INNER JOIN LOIPayerText ON tblLOIX.WhoPays = LOIPayerText.Wh oPays
WHERE (((CommunitiesV 4.ThisComm)=Tru e));

In the understanding that I should replace each double quote inside the quoted string with a pair of double quotes, I used

 Set rs = ("SELECT tblLOIX.*, _
& ""BSB "" & [Tempvars]![BankAccountBSB] & "", Account No. "" & [TempVars]![BankAccountNumb er] & "" ("" & [Tempvars]![BankAccountName] & "")"" AS BankDetails, _
& [Fees_payable]-[Fees_received] AS Balance, _
& [Para5a] & [Balance] & [Para5b] AS Para5_1, _
& [BankDetails] & [Para5c] & "" "" & [BankAccountName] & "". "" AS Para5_2, _
& IIf(([Balance]>0 And Nz([Para5a])<>""""), ([Para5_1] & "" "" & IIf(([Who_Pays]=""P"" Or [Who_Pays]=""B""),[Para5_2],"""")),"""" ) AS Para5, _
& LOIPayerText.P2 ShowBank, LOIPayerText.P2 PaymentArrangem ents, CommunitiesV4.T hisComm _
& FROM CommunitiesV4, _
& tblLOIX INNER JOIN LOIPayerText ON tblLOIX.WhoPays = LOIPayerText.Wh oPays _
& WHERE (((CommunitiesV 4.ThisComm)=Tru e));"

but that caused a syntax error.
Then I tried

Set rs = ("SELECT tblLOIX.*, _
& 'BSB ' & [Tempvars]![BankAccountBSB] & ', Account No. ' & [TempVars]![BankAccountNumb er] & ' (' & [Tempvars]![BankAccountName] & ')' AS BankDetails, _
& [Fees_payable]-[Fees_received] AS Balance, _
& [Para5a] & [Balance] & [Para5b] AS Para5_1, _
& [BankDetails] & [Para5c] & ' ' & [BankAccountName] & '. ' AS Para5_2, _
& IIf(([Balance]>0 And Nz([Para5a])<>''), ([Para5_1] & ' ' & IIf(([Who_Pays]='P' Or [Who_Pays]='B'),[Para5_2],'')),'') AS Para5, _
& LOIPayerText.P2 ShowBank, LOIPayerText.P2 PaymentArrangem ents, CommunitiesV4.T hisComm _
& FROM CommunitiesV4, _
& tblLOIX INNER JOIN LOIPayerText ON tblLOIX.WhoPays = LOIPayerText.Wh oPays _
& WHERE (((CommunitiesV 4.ThisComm)=Tru e));"

but then it said "Expected expression" when it got to the beginning of line 2.
Can either of you sort out my poor addled brain?
<IMG> </IMG>
PS: I am aware that the code blocks I have shown above are too wide for the window, and that Post.Bytes isn't wrapping them, but I don't know how to resolve that. Hopefully what I have shown will be enough for you to see where I am going wrong.