runsql with action query

Shawn
Shawn used Ask the Experts™
on
I have a make table called "qryMailingSelectedLetter". I would simply like to add a condition before it is run.

Can someone help me modify the code below so it runs please. I'm not sure what I'm doing wrong.

Dim strSQL As String

strSQL = "SELECT qryMailingSelectedLetter"
strSQL = strSQL & " WHERE (CorrespondenceDate)IS NULL"

DoCmd.RunSQL strSQL

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You mean this ?

IF <YourCondition> = True ' Or False
    Dim strSQL As String

     strSQL = "SELECT qryMailingSelectedLetter"
     strSQL = strSQL & " WHERE  IsNull ([CorrespondenceDate])"

      CurrentDB.Execute strSQL ,  dbFailOnError  ' a safer way

End If
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
You have no "FROM", and you are not selecting any fields

Try this:


Dim strSQL As String

strSQL = "SELECT * FROM qryMailingSelectedLetter"
strSQL = strSQL & " WHERE (CorrespondenceDate)IS NULL"

CurrentDB.execute strSQL, dbfailonerror
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Standard basic SQL query syntax:
SELECT ...
FROM ...
WHERE ...
ORDER BY ...


So always get in the habit of "viewing" your SQL before you every try to run it.

Either use a messagebox:
    msgbox strSQL

...or what I like to do is to send it to a textbox, (this way I can actually copy and paste it into a real query and run it.)
    me.txtSQL=strSQL

;-)

JeffCoachman
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
boag2000:
when I do this it says I cannot execute a select query
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"when I do this it says I cannot execute a select query "
That is correct.

What exactly are you trying to do ?

mx
Top Expert 2016

Commented:
when you are using a select query, you either use

docmd.openquery "nameofQuery"

now since you are using sql statement in vba,
you need to use recordset to get records returned by your query

Dim strSQL As String, rs as dao.recordset

strSQL = "SELECT qryMailingSelectedLetter.* "
strSQL = strSQL & " From qryMailingSelectedLetter WHERE [CorrespondenceDate] IS NULL"

set rs=currentdb.openrecordset(strSQL)


or create a query using querydef

dim qd as dao.querydef

Dim strSQL As String

strSQL = "SELECT qryMailingSelectedLetter.* "
strSQL = strSQL & " From qryMailingSelectedLetter WHERE [CorrespondenceDate] IS NULL"

set qd=currentdb.createquerydef("q1", strSQl)

Author

Commented:
>>What exactly are you trying to do ?
well the initial query IS an action query. It creates a temp table.

It creates a table however where I would like to add a where clause before it executes.

Does that make sense?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Do you mean like this ...?

IF  IsNull Me![CorrespondenceDate] Then
     Dim strSQL As String
     strSQL = "SELECT * FROM qryMailingSelectedLetter"   ' PER Jeff - Need FROM .....
     CurrentDB.Execute strSQL ,  dbFailOnError  

End If

Author

Commented:
not quite...the CorrespondenceDate isn't a field in the form it is a column in the query
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
If your initial query (qryMailingSelectedLetter) is already an action query, you are going to have to modify it to include the new conditions you are setting for it.

An action query cannot be made into a subquery, which you appear to be attempting to do.

You need to edit the SQL for qryMailingSelectedLetter and add the criteria to its WHERE clause.

Author

Commented:
I was getting that feeling. The thing is there are 2 scenarios...one with simply the qry as is and the other with the additional Where x is Null. thought I might be able to use the same query and just alter it.


er, what about querydef...don't know it that well but wouldn't we be able to add the when then execute?
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Query defs are one method.  You can also write the whole query a  vba string, which would give you a lot of flexibility.
Top Expert 2016

Commented:
<well the initial query IS an action query. It creates a temp table.>
you can not use a select statement using a saved make table query as domain.

post the  SQL statement of qryMailingSelectedLetter, so we can shed light on this..

Author

Commented:
it's pretty ugly.....

SELECT dbo_tblCorrespondence.CorrespondenceID, dbo_tblCorrespondence.ContactID, dbo_tblCorrespondence.CorrespondenceDate, IIf(IsNull([tblContacts]![département]),"",[tblContacts]![département] & Chr(13)) & [tblSocieteAdresse]![2sadresse1] & IIf(IsNull([tblSocieteAdresse]![2sadresse2]),Chr(13),Chr(13) & [tblSocieteAdresse]![2sadresse2] & Chr(13)) & [tblSocieteAdresse]![2scode] & " " & [tblSocieteAdresse]![2sville] AS Address, [Prénom] & " " & [Nom] AS Fname, tblSociete.Societe, tblSociete.CodeClientTraductions, dbo_tblCorrespondence.MailingID, tblContacts.Nom, tblContacts.Préfix AS Title, tblContacts.Prénom, tblContacts.département, tblSocieteAdresse.[2sadresse1], tblSocieteAdresse.[2sadresse2], tblSocieteAdresse.[2sville], tblSocieteAdresse.[2scode], tblSocieteAdresse.Country, dbo_tblMailings.CorrespondenceBody, dbo_tblMailings.SentBy INTO tblMailingSelected_tmp
FROM (tblSocieteAdresse RIGHT JOIN (tblSociete RIGHT JOIN (dbo_tblCorrespondence LEFT JOIN tblContacts ON dbo_tblCorrespondence.ContactID = tblContacts.ContactID) ON tblSociete.societeID = tblContacts.SocieteID) ON tblSocieteAdresse.adresseID = tblContacts.adressID) INNER JOIN dbo_tblMailings ON dbo_tblCorrespondence.MailingID = dbo_tblMailings.MailingID
WHERE (((dbo_tblCorrespondence.MailingID)=[Forms]![frmMailings]![MailingID]))
ORDER BY tblSociete.Societe, tblContacts.Nom, tblContacts.Prénom;

Open in new window

Commented:
I have the feeling we are not seeing the whole query, given the lack of a FROM and a lack of an INTO (if you are trying to create a table.)
You can indeed alter (and execute) a query by adding conditions to the querydef, but then you have to put the query back to the original state for the next execution.
Or, use the sql from the original query, and add the conditions (conditionally) as another query.
Top Expert 2016

Commented:
assuming this is the SQL statement of your make table query

SELECT mails.* INTO selectedMails
FROM mails;

you can revised it with


SELECT mails.* INTO selectedMails
FROM mails
WHERE mails.[CorrespondenceDate] IS NULL

change "mails" and "selectedMails" accordingly with the proper table names
Top Expert 2016
Commented:
try this


SELECT dbo_tblCorrespondence.CorrespondenceID, dbo_tblCorrespondence.ContactID, dbo_tblCorrespondence.CorrespondenceDate, IIf(IsNull([tblContacts]![département]),"",[tblContacts]![département] & Chr(13)) & [tblSocieteAdresse]![2sadresse1] & IIf(IsNull([tblSocieteAdresse]![2sadresse2]),Chr(13),Chr(13) & [tblSocieteAdresse]![2sadresse2] & Chr(13)) & [tblSocieteAdresse]![2scode] & " " & [tblSocieteAdresse]![2sville] AS Address, [Prénom] & " " & [Nom] AS Fname, tblSociete.Societe, tblSociete.CodeClientTraductions, dbo_tblCorrespondence.MailingID, tblContacts.Nom, tblContacts.Préfix AS Title, tblContacts.Prénom, tblContacts.département, tblSocieteAdresse.[2sadresse1], tblSocieteAdresse.[2sadresse2], tblSocieteAdresse.[2sville], tblSocieteAdresse.[2scode], tblSocieteAdresse.Country, dbo_tblMailings.CorrespondenceBody, dbo_tblMailings.SentBy INTO tblMailingSelected_tmp
FROM (tblSocieteAdresse RIGHT JOIN (tblSociete RIGHT JOIN (dbo_tblCorrespondence LEFT JOIN tblContacts ON dbo_tblCorrespondence.ContactID = tblContacts.ContactID) ON tblSociete.societeID = tblContacts.SocieteID) ON tblSocieteAdresse.adresseID = tblContacts.adressID) INNER JOIN dbo_tblMailings ON dbo_tblCorrespondence.MailingID = dbo_tblMailings.MailingID
WHERE (((dbo_tblCorrespondence.MailingID)=[Forms]![frmMailings]![MailingID])) And dbo_tblCorrespondence.CorrespondenceDate Is Null
ORDER BY tblSociete.Societe, tblContacts.Nom, tblContacts.Prénom;

Commented:
^Did not refresh

Author

Commented:
I ended up doing just that capricorn1...'cause I coun't do what I originally wanted (thx mbizu)

thanks for your patience. I admit I wasn't too clear this time around :)
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Your querydef idea has a lot of merit for what you are trying to do.

Try revising your query like this:

SELECT dbo_tblCorrespondence.CorrespondenceID, dbo_tblCorrespondence.ContactID, dbo_tblCorrespondence.CorrespondenceDate, IIf(IsNull([tblContacts]![département]),"",[tblContacts]![département] & Chr(13)) & [tblSocieteAdresse]![2sadresse1] & IIf(IsNull([tblSocieteAdresse]![2sadresse2]),Chr(13),Chr(13) & [tblSocieteAdresse]![2sadresse2] & Chr(13)) & [tblSocieteAdresse]![2scode] & " " & [tblSocieteAdresse]![2sville] AS Address, [Prénom] & " " & [Nom] AS Fname, tblSociete.Societe, tblSociete.CodeClientTraductions, dbo_tblCorrespondence.MailingID, tblContacts.Nom, tblContacts.Préfix AS Title, tblContacts.Prénom, tblContacts.département, tblSocieteAdresse.[2sadresse1], tblSocieteAdresse.[2sadresse2], tblSocieteAdresse.[2sville], tblSocieteAdresse.[2scode], tblSocieteAdresse.Country, dbo_tblMailings.CorrespondenceBody, dbo_tblMailings.SentBy INTO tblMailingSelected_tmp
FROM (tblSocieteAdresse RIGHT JOIN (tblSociete RIGHT JOIN (dbo_tblCorrespondence LEFT JOIN tblContacts ON dbo_tblCorrespondence.ContactID = tblContacts.ContactID) ON tblSociete.societeID = tblContacts.SocieteID) ON tblSocieteAdresse.adresseID = tblContacts.adressID) INNER JOIN dbo_tblMailings ON dbo_tblCorrespondence.MailingID = dbo_tblMailings.MailingID

Open in new window


Thats removing the WHERE and ORDER BY Clauses.

Then try  this in VBA:

Dim qd As QueryDef
dim strSQL as string
dim strWhere as string
dim strOrder as string
Set qd = CurrentDb.QueryDefs("qryMailingSelectedLetter")

strWhere = " WHERE dbo_tblCorrespondence.MailingID = " & [Forms]![frmMailings]![MailingID]

strOrder = " ORDER BY tblSociete.Societe, tblContacts.Nom, tblContacts.Prénom"

strSQL = qd.SQL

' When you want to execute it with null criteria:

CurrentDB.Execute strSQL & strWhere & " AND dbo_tblCorrespondence.CorrespondenceDate Is Null " & strOrder


'  to execute it without nulls
Currentdb.execute strSQL & strWhere & strOrder

Open in new window

Author

Commented:
thankx mbizup. Nice and clear.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial