SQL statement works when ported to a query, but fails in the module

[VicM]

I have a report whereby I create the RecordSource in the report's module based on selection criteria. The sql threw an error. I broke out the SQL statement and tried to isolate it in a test subroutine.

The SQL statement inserts two variables which I've determined are there by using the Debug.Print statement.

When I copy the SQL statement and put it into a query, removing the quotations and & _ characters at the ends of lines, and insert the date into the SQL as well as a number for the other variable, the query brings back the data I ask for.

But the SQL in the module throws a runtime error 3141, "The SELECT statement includes a reserved word or an argument name that is

misspelled or missing, or the punctuation is incorrrect."​

Sub testSQL()

Dim compy As Integer
Dim Edat As Date
Dim sql As String

compy = 4
Edat = #11/6/2017#

Debug.Print compy & ", " & Edat

sql = "SELECT (tblMembers.MemFName & ' ' & tblMembers.MemLName) AS MemName, " & _
"tblMembers.MemAddress1,tblMembers.MemAddress2, tblMembers.MemCity, tblMembers.MemST, " & _
"tblMembers.MemZipcode, tblAltEmpInfo.fldAltAddr1, tblAltEmpInfo.fldAltAddr2, " & _
"tblAltEmpInfo.fldAltCity, tblAltEmpInfo.fldAltState, " & _
"tblAltEmpInfo.fldAltZip, IIf(Len(tblAltEmpInfo.fldAltAddr1)>0,tblAltEmpInfo.fldAltZip," & _
"tblMembers.MemZipcode) AS TruZip" & _
"FROM tblMembers INNER JOIN tblAltEmpInfo ON tblMembers.MemPRIID = tblAltEmpInfo.PriID" & _
"WHERE (((tblMembers.Mem_UnitNo)<>999) AND ((tblMembers.MemStatusID)=17 Or " & _
"(tblMembers.MemStatusID)=2)) AND ((tblMembers.MemMemberTypeID)=3 Or " & _
"(tblMembers.MemMemberTypeID)=6) AND ((tblMembers.MemClassID)<>8 And " & _
"(tblMembers.MemClassID)<>14 And (tblMembers.MemClassID)<>15) AND ((tblMembers.MemEmp)=" & compy & _
") AND ((tblMembers.HideRec)=False) " & _
"OR (((tblMembers.Mem_UnitNo)<>999) AND ((tblMembers.MemStatusID)=20 Or " & _
"(tblMembers.MemStatusID)=21) AND ((tblMembers.MemMemberTypeID)=3 Or " & _
"(tblMembers.MemMemberTypeID)=6) AND ((tblMembers.MemClassID)<>8 And " & _
"(tblMembers.MemClassID)<>14 And (tblMembers.MemClassID)<>15) " & _
"AND ((tblMembers.MemEffective)>=#" & Edat & "#-90) AND ((tblMembers.MemEmp)=" & compy & _
") AND ((tblMembers.HideRec)=False))" & _
"ORDER BY IIf(Len(tblAltEmpInfo.fldAltAddr1)>0,tblAltEmpInfo.fldAltZip,tblMembers.MemZipcode), " & _
"tblMembers.MemLName, tblMembers.Mem_UnitNo, tblMembers.MemFName;"
        
        DoCmd.RunSQL sql



End Sub

Bear in mind in order to see all the code in the code window I had to truncate lines with " & _ on the fly. I'm confident that the SQL statement in the module is proper.

I'm scratching whats left of my head hair not being able to understand why it's throwing the error.
Remember when I put that SQL into a query and replace the Edat with a hashtag bracketed date, and replace compy with a number, the query works.

Any insights will be welcome.

Thanks,
Vic

 

[MajP]

What is the point? Maybe I am wrong, but I do not see anything dynamic. Why can you not just save the query?

 

[VicM]

Well once again, the easy answer escaped me! LOL


But something else is going on anyway. Take a look at the following code. It's a simple SQL statement, but still throws an error.

Sub testSQL()

Dim strSQL As String



        strSQL = "SELECT tblMembers.MemLName FROM tblMembers;"

        DoCmd.RunSQL (strSQL)



End Sub

A runtime error 2342 "A RunSQL action requires an argument consisting of an SQL statement."

I'm not sure what's going on.

 

[MajP]

What happens if you do this
docmd.runsql strSql

Usually the Parentheses are used to return values from function.
A procedure that does not return a value can not be called with parentheses.

 

[xwb]

Alternatively if you are used to using parentheses

    call DoCmd.RunSQL(strSQL)

I find this quite useful when I'm switching from languages that use the parentheses to those that don't. It also helps when I switch between a function call and a sub. If I just change the assignment to a call, I don't have to go round removing the parentheses and then adding them back when I change my mind and change the sub back to a function.

 

[Andrzejek]

When you do:[tt]
strSQL = "SELECT tblMembers.MemLName FROM tblMembers;"
DoCmd.RunSQL (strSQL)[/tt]
What do you expect to happen? Where/how are you expecting to see/have/deal with [tt]MemLName[/tt] data?

You may try this to see if you get the same error if you run something like:[tt]
strSQL = "UPDATE tblMembers SET MemLName = 'Jones' WHERE ID = 123"
DoCmd.RunSQL (strSQL)[/tt]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Sorry for my stupid response, I guess I should have read the post.
You can only use runql on an action query, not on a select query.

"A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database."

If you want to build a dynamic select query then you will have to use the querydef.

 

[VicM]

Folk,

That's what's great about these forums. One always learns something new each time.

Andrzejek you make a very valid point. It actually occurred to me before I read your response. But makes perfect sense.

And MajP you also point out the obvious. Unfortunately, I didn't see the forest for the trees!

I appreciate all of your inputs. You are all a great bunch of helpful folks!

Thank you all,

Vic