Querydef Help!

[mousemat]

This is my querydef which works fantastically well,

strSQL = "SELECT tblMembers.[Forename], tblMembers.[Surname], tblMembers.[Group], tblMembers.[Appointment], tblMembers.[Telephone], tblGroups.[Order], [Forms]![frmNoOfModules]![txtModule]" & _
"FROM tblMembers INNER JOIN tblGroups ON tblMembers.Group = tblGroups.Group " & _
"WHERE (((tblMembers.[Complete2]) Is Null));"

I now need to change things slightly, but just cant get my head round this at the moment. I need to run the sql, but change the where statement to exclude the following Appointment: Treasurer, Secretary and Chairperson.

I have written a query which does just that, but I need to put this into a query def.

The sql of the query is below.

SELECT tblMembers.Forename, tblMembers.Surname, tblMembers.Group, tblMembers.Appointment, tblMembers.Telephone, tblGroups.Order
FROM tblMembers INNER JOIN tblGroups ON tblMembers.Group = tblGroups.Group
WHERE (((tblMembers.Appointment)<>"treasurer" And (tblMembers.Appointment)<>"secretary" And (tblMembers.Appointment)<>"chairperson") AND ((tblMembers.Complete2) Is Null));

I have tried all sorts of things to make it work in the querydef but alas to no avail....yet. I can't work out the syntax

 

[Banana]

and assuming that you got the second query to work just fine and need to execute, you should use querydef. What you're calling querydef isn't actually querydef. You just made a SQL statement and executed it.

A querydef should be something like this:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = Currentdb()
Set qdf = dbs.QueryDefs("NameOfYourQuery")

qdf.Execute

(This is an air code, so syntaxes need to be checked)

 

[mousemat]

Here is the full querydef for what I am currently doing.

Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
'set variable values
Set dbs = CurrentDb
strQueryName = "qryIncompleteModules"
'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT tblMembers.[Forename], tblMembers.[Surname], tblMembers.[Group], tblMembers.[Appointment], tblMembers.[Telephone], tblGroups.[Order], [Forms]![frmNoOfModules]![txtModule]" & _
"FROM tblMembers INNER JOIN tblGroups ON tblMembers.Group = tblGroups.Group " & _
"WHERE (((tblMembers.[Complete2]) Is Null));"
'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
'Open report for viewing
DoCmd.OpenReport "rptIncompleteModules", acViewPreview

It takes the variable from the form frmNoOfModules.

What I need to do now, is change the querydef to exclue certain appointments from the table tblMembers.

I have written a query to do this, but it needs to be run from a querydef (too long and complicated to go into detail as to why) I need to convert the new query into the querydef so I can extract the data required.

This is the sql veiw of the query that I have created.

SELECT tblMembers.Forename, tblMembers.Surname, tblMembers.Group, tblMembers.Appointment, tblMembers.Telephone, tblGroups.Order
FROM tblMembers INNER JOIN tblGroups ON tblMembers.Group = tblGroups.Group
WHERE (((tblMembers.Appointment)<>"treasurer" And (tblMembers.Appointment)<>"secretary" And (tblMembers.Appointment)<>"chairperson") AND ((tblMembers.Complete2) Is Null));

I just need to convert it to a querydef in the code on the form!

 

[pbaldy]

I can't imagine why that would have to be created as a querydef every time, since it's not dynamic, but there's nothing tricky about putting it into code. The mistake you're probably making (I say probably since you didn't post your attempt at it) is using double quotes around your 3 text values. Since you're already surrounding the string with double quotes, you'd have to use single quotes around those values.

 

[mousemat]

Thanks Paul, that was the bit I was missing. I had a variety of single and double quotes, just couldn't see the wood for the trees!!!!

The reason im building a querydef is simple.

The form contains 20 textboxes, each textbox relates to a differant training module. Because of the way the tables have been set up (I Inherited the Database) it was either write 20 querys or write a query def on the doubleclick event of each textbox!