Avatar of Shawn
Shawn
Flag for Canada asked on

runsql with action query

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

Microsoft Access

Avatar of undefined
Last Comment
Shawn

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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 Coachman

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 Coachman

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Shawn

ASKER
boag2000:
when I do this it says I cannot execute a select query
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

What exactly are you trying to do ?

mx
Rey Obrero (Capricorn1)

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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Shawn

ASKER
>>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 - Former Microsoft Access MVP)

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
Shawn

ASKER
not quite...the CorrespondenceDate isn't a field in the form it is a column in the query
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Shawn

ASKER
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?
mbizup

Query defs are one method.  You can also write the whole query a  vba string, which would give you a lot of flexibility.
Rey Obrero (Capricorn1)

<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..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Shawn

ASKER
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

jerryb30

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.
Rey Obrero (Capricorn1)

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jerryb30

^Did not refresh
Shawn

ASKER
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 :)
mbizup

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Shawn

ASKER
thankx mbizup. Nice and clear.