Link to home
Start Free TrialLog in
Avatar of Shawn
ShawnFlag 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

Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

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
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
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
Avatar of Shawn

ASKER

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

What exactly are you trying to do ?

mx
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)
Avatar of 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?
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
Avatar of Shawn

ASKER

not quite...the CorrespondenceDate isn't a field in the form it is a column in the query
SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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?
Query defs are one method.  You can also write the whole query a  vba string, which would give you a lot of flexibility.
<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..
Avatar of 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

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.
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
^Did not refresh
Avatar of 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 :)
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

Avatar of Shawn

ASKER

thankx mbizup. Nice and clear.