INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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."


CODE

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

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

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

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

(OP)
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.

CODE

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.

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

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.

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

Alternatively if you are used to using parentheses

CODE

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.

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

When you do:
strSQL = "SELECT tblMembers.MemLName FROM tblMembers;"
DoCmd.RunSQL (strSQL)

What do you expect to happen? Where/how are you expecting to see/have/deal with MemLName data?

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

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.

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close