Hi experts
I have a query....
SELECT tblSuppliers.SupplierID, shnCombine("tblSuppliersSu
b","Suppli
erID",[tbl
Suppliers.
SupplierID
],"Contact
FName") AS Corr, tblSuppliersSub.ContactLNa
me
FROM tblSuppliers INNER JOIN tblSuppliersSub ON tblSuppliers.SupplierID = tblSuppliersSub.SupplierID
GROUP BY tblSuppliers.SupplierID, shnCombine("tblSuppliersSu
b","Suppli
erID",[tbl
Suppliers.
SupplierID
],"Contact
FName"), tblSuppliersSub.ContactLNa
me
HAVING (((tblSuppliers.SupplierID
)=[forms]!
[frmSuppli
ersNew]![S
upplierID]
))
ORDER BY shnCombine("tblSuppliersSu
b","Suppli
erID",[tbl
Suppliers.
SupplierID
],"Contact
FName");
This is the function that the query calls (I got this from an wonderful Expert here on EE)....
Function shnCombine(strTable As String, strIDField As String, strIDValue As Long, strGetField As String) As String
Dim rst As dao.Recordset
Dim strCombine As String
'Set rs = CurrentDb.OpenRecordset("s
elect * from tblSuppliersSub where SupplierID=" & Me!SupplierID & " and YesNofield = true")
Set rst = CurrentDb.OpenRecordset("S
ELECT DISTINCTROW [" & strGetField & "] FROM [" & strTable & "]WHERE [" & strIDField & "]=" & strIDValue And tblSuppliersSub.ReceivesCo
rr = True)
While Not rst.EOF
strCombine = strCombine & rst(strGetField) & " and "
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
If strCombine <> "" Then strCombine = Left$(strCombine, Len(strCombine) - 4)
shnCombine = strCombine
End Function
When I run the query I get this message...
This expression is typed incorrectly, or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
The query was working okay until I needed to put the Where ReceivesCorr = True in the function. I only want to combine the suppliers that Receives Correspondene checkbox is true.
I hope I have provided enough information...if not let me know...:-)