SQL error with parameter query

[dmarsh16946]

I can successfully use a fixed query to feed a function like this for a mail merge
MergeAllWord ("Select * FROM qryContactsSelected")

If the query takes data from unbound fields in a selector form, eg setting the criterion for a field called category to say

forms!frmSelect.cboSelectCategory

the function brings up an SQL error. I've run into this before and used a dodge to change the query to a make-table query first, then use the new table in the select statement.

It looks like the QueryDef function might more neatly avoid this dodge but I don't know how to set it up. I've seen other people using code that would translate into something like this

Dim qd as DAO.QueryDef
Set qd = db.QueryDefs("qryContactsSelected")

' Set parameter
qd.Parameters(0) = forms!frmSelect.cboSelectCategory

' Open recordset
rstData = qd.OpenRecordset
etc

but really not sure what would come next.

Any pointers gratefully received.

 

[Remou]

It might be easiest to simply feed the form with a query like so:

strSQL="SELECT * FROM qryContactsSelected " _
& "WHERE Category='" & Forms!frmSelect.cboSelectCategory & "'"
MergeAllWord (strSQL)

The above assumes that the Category field exists in qryContactsSelected and that Category is a text field.

If you wish to use parameters, you must add them to the query (SQL view):

PARAMETERS strCategory Text(50);
SELECT * FROM qryContactsSelected WHERE Category=strCategory

http://lessthandot.com

 

[dmarsh16946]

Thanks Remou

I think I'm already doing this as below (the example in my original post was simplified).

SELECT qryContacts.FirstName, qryContacts.LastName, qryContacts.FullName, qryContacts.Title, qryContacts.FullAddress, qryContacts.Email, qryContacts.Choose, qryContacts.SupplierTPG, qryContacts.CustomerTPG, qryContacts.SupplierGGB, qryContacts.CustomerGGB, qryContacts.FriendsAndRelatives, qryContacts.Address1, qryContacts.Address2, qryContacts.Address3, qryContacts.Town, qryContacts.County, qryContacts.PostCode
FROM qryContacts
WHERE ((([SupplierTPG]=[forms]![frmCreateMerge].[cboSupplierTPG] _ &
Or [forms]![frmCreateMerge].[cboSupplierTPG] Is Null)=True) _ &
AND (([CustomerTPG]=[forms]![frmCreateMerge].[cboCustomerTPG] _ &
Or [forms]![frmCreateMerge].[cboCustomerTPG] Is Null)=True) _ &
AND (([SupplierGGB]=[forms]![frmCreateMerge].[cboSupplierGGB] _ &
Or [forms]![frmCreateMerge].[cboSupplierGGB] Is Null)=True) _ &
AND (([CustomerGGB]=[forms]![frmCreateMerge].[cboCustomerGGB] _ &
Or [forms]![frmCreateMerge].[cboCustomerGGB] Is Null)=True) _ &
AND (([FriendsAndRelatives]=[forms]![frmCreateMerge].[cboFriends] _ &
Or [forms]![frmCreateMerge].[cboFriends] Is Null)=True));

What I'm not doing is declaring the parameters. Not sure what to use, as it can't find either of say SupplierTPG or cboSupplierTPG for the first one.

I thought there was a fundamental problem in trying to use a query of this kind in a procedure - it always points to an SQL error when trying, whereas the simple fixed query works fine.

 

[Remou]

Is that in code? If so, you need quite a few quotes in there:

strSQL="SELECT qryContacts.FirstName, qryContacts.LastName, " _ 
& "qryContacts.FullName, qryContacts.Title, qryContacts.FullAddress, " _ 
& "qryContacts.Email, qryContacts.Choose, qryContacts.SupplierTPG, " _ 
& "qryContacts.CustomerTPG, qryContacts.SupplierGGB, qryContacts.CustomerGGB, " _ 
& "qryContacts.FriendsAndRelatives, qryContacts.Address1, qryContacts.Address2, " _ 
& "qryContacts.Address3, qryContacts.Town, qryContacts.County, qryContacts.PostCode" _ 
& "FROM qryContacts" _ 
& "WHERE [SupplierTPG]='" & [forms]![frmCreateMerge].[cboSupplierTPG] _ 
& "*' AND [CustomerTPG]='" & [forms]![frmCreateMerge].[cboCustomerTPG] _ 
& "*' AND [SupplierGGB]='" & [forms]![frmCreateMerge].[cboSupplierGGB] _ 
& "*' AND [CustomerGGB]='" & [forms]![frmCreateMerge].[cboCustomerGGB]  _ 
& "*' AND [FriendsAndRelatives]='" & [forms]![frmCreateMerge].[cboFriends] & "*'"

http://lessthandot.com

 

[dmarsh16946]

Thanks again Remou

The SQL I sent was the view of the query that selects data. It allows each of the criteria for SupplierTPG etc to be -1, 0 or neither so all possible combinations of the criteria are catered for.

I tried your suggestion but got the 'error in SQL' message; then tried various edits with the same result. Will continue trying but appreciate your help so far.

 

[dhookom]

I would add code to the form frmCreateMerge to modify the SQL property of qryContactsSelected to avoid all issues with parameters.

Dim strSQL as String
Dim strWhere As String
Dim qd as DAO.QueryDef
Set qd = db.QueryDefs("qryContactsSelected")
[green]'Assuming all criteria fields are text[/green]
strSQL = "SELECT FirstName, LastName, FullName, Title, FullAddress, Email, " & _
    "[Choose], SupplierTPG, CustomerTPG, SupplierGGB, CustomerGGB, " & _
    "FriendsAndRelatives, Address1, Address2, Address3, Town, County, PostCode " & _
    "FROM qryContacts "
strWhere = "WHERE 1 = 1 "
If not IsNull(Me.cboSupplierTPG) Then
   strWhere = strWhere & " AND SupplierTPG =""" & Me.cboSupplierTPG & """ "
End If
If Not IsNull(Me.cboCustomerTPG) Then
   strWhere = strWhere & " AND CustomerTPG =""" & MecboCustomerTPG & """ "
End If
If Not IsNull(Me.cboSupplierGGB) Then
   strWhere = strWhere & " AND SupplierGGB =""" & Me.cboSupplierGGB & """ "
End If
If Not IsNull(Me.cboCustomerGGB) Then
   strWhere = strWhere & " AND CustomerGGB =""" & Me.cboCustomerGGB & """ "
End If
If Not IsNull(Me.cboFriends) Then
   strWhere = strWhere & " AND FriendsAndRelatives =""" & Me.cboFriends & """ "
End If
strSQL = strSQL & strWhere
qd.SQL = strSQL
Set qd = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

If it is from SQL view, try the code below. You should not use line continuation characters _ in the SQL design window. Using Forms!Name!Field & "*" means that all records are selected if Field is null. However, it also means that if, say, Field="A" everything begining with "A" will be selected.

SELECT qryContacts.FirstName, qryContacts.LastName, qryContacts.FullName, qryContacts.Title, qryContacts.FullAddress, qryContacts.Email, qryContacts.Choose, qryContacts.SupplierTPG, qryContacts.CustomerTPG, qryContacts.SupplierGGB, qryContacts.CustomerGGB, qryContacts.FriendsAndRelatives, qryContacts.Address1, qryContacts.Address2, qryContacts.Address3, qryContacts.Town, qryContacts.County, qryContacts.PostCode
FROM qryContacts
WHERE [SupplierTPG]=[forms]![frmCreateMerge].[cboSupplierTPG] & "*"
AND [CustomerTPG]=[forms]![frmCreateMerge].[cboCustomerTPG] & "*"
AND [SupplierGGB]=[forms]![frmCreateMerge].[cboSupplierGGB] & "*"
AND [CustomerGGB]=[forms]![frmCreateMerge].[cboCustomerGGB] & "*"
AND [FriendsAndRelatives]=[forms]![frmCreateMerge].[cboFriends] & "*"

http://lessthandot.com