Need help with SQL query

[NJudson]

I've written a stored query where in a form code I click a button and using QueryDef I alter the SQL so I can change the Field1 criteria based on the value of a combobox on the form. I'm getting a "Type Mismatch" on the SQL, but not sure how to fix it. Here's my form code for the button:

Private Sub cmdHopair_Click()
Dim dbs As DAO.Database
Dim Region As String
Dim strSQL As String
Dim qdf As Object
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryHopairMismatch")

If (MsgBox("Do you have the correct region selected in the combobox?", vbYesNo)) = vbNo Then Exit Sub
Region = Me.cbosubfrmGenerateAuditReportsSelectSwitch
strSQL = "SELECT zMasterHopair.Field1, zMasterHopair.Field2, zMasterHopair.Field3," _
& "IIf([zmasterHopair]![field4]<>[Hopair]![field4]," * " & [zMasterHopair]![field4] & " * ",[zMasterHopair]![Field4]) AS Field4," _
& "IIf([zmasterHopair]![field5]<>[Hopair]![field5]," * " & [zMasterHopair]![field5] & " * ",[zMasterHopair]![Field5]) AS Field5," _
& "IIf([zmasterHopair]![field6]<>[Hopair]![field6]," * " & [zMasterHopair]![field6] & " * ",[zMasterHopair]![Field6]) AS Field6," _
& "IIf([zmasterHopair]![field7]<>[Hopair]![field7]," * " & [zMasterHopair]![field7] & " * ",[zMasterHopair]![Field7]) AS Field7," _
& "IIf([zmasterHopair]![field8]<>[Hopair]![field8]," * " & [zMasterHopair]![field8] & " * ",[zMasterHopair]![Field8]) AS Field8," _
& "IIf([zmasterHopair]![field9]<>[Hopair]![field9]," * " & [zMasterHopair]![field9] & " * ",[zMasterHopair]![Field9]) AS Field9" _
& "FROM Hopair INNER JOIN zMasterHopair ON (Hopair.Field3 = zMasterHopair.Field3) AND (Hopair.Field2 = zMasterHopair.Field2) AND (Hopair.Field1 = zMasterHopair.Field1)" _
& "WHERE (((zMasterHopair.Field4)<>[Hopair]![field4])) OR (((zMasterHopair.Field5)<>[Hopair]![field5])) OR" _
& "(((zMasterHopair.Field6)<>[Hopair]![field6])) OR (((zMasterHopair.Field7)<>[Hopair]![field7]))" _
& "OR (((zMasterHopair.Field8)<>[Hopair]![field8])) OR (((zMasterHopair.Field9)<>[Hopair]![Field9]));"

qdf.SQL = strSQL
DoCmd.OpenQuery ("qryHopairMismatch")

qdf.Close
End Sub


I think the problem is stemming from my IIf statements, for example:

"IIf([zmasterHopair]![field4]<>[Hopair]![field4]," * " & [zMasterHopair]![field4] & " * ",[zMasterHopair]![Field4]) AS Field4,"

I'm thinking it doesn't like the asteriks, but I need to have those asteriks but can't find another way of writing it. Thanks for any help anyone can provide. I know this probably seems like a difficult one.

 

[pdx_man]

Woa, that's a lot to look at.

Look at this area:

[zMasterHopair]![field4] & " * ",[zMasterHopair]![Field4]) AS Field4," _

You are looking for:
[zMasterHopair]![field4] & " * " & [zMasterHopair]![Field4]) & " AS Field4," _

in each place.

 

[Pat Hartman]

There is no point that I can see to modifying the stored querydef. Your SQL string is not dynamic. You should make it the stored querydef and stop replacing it on the fly. Also, it is much better to give users forms to interact with rather than queries. You can make a form that looks like a datasheet but provides form events that you can use to trap errors.