Nightmare- Query!

[ianbar]

Hi I have a form that has several search criteria text fields on it, when some fields are blank the query fails to work even though I have used IIF statements in the query for fields that could possibly be blank.

Here is a copy of the query from sql view:

SELECT TRACKER.RecordID, TRACKER.SOCCSType, TRACKER.DateRaised, TRACKER.RaisedBy, TRACKER.Company, TRACKER.DueDate, TRACKER.Status, TRACKER.ClosedDate, TRACKER.Link
FROM TRACKER
WHERE (((TRACKER.SOCCSType)=IIf([Forms]![Search].[txtsoccstype]<>&quot;&quot;,[Forms]![Search].[txtsoccstype],&quot;*&quot) AND ((TRACKER.Company) Like &quot;*&quot; & [Forms]![Search].[txtsearchcriteria] & &quot;*&quot AND ((TRACKER.Status)=IIf([Forms]![Search].[txtsoccstype]<>&quot;&quot;,[Forms]![Search].[txtstatus],&quot;*&quot));


Can anyone help me?

 

[telephoto]

Try breaking it down, check out my code post Mar 6 at this thread

thread702-490663

Split up the sql string and rebuild it after checking each search criteria, finally adding the last bit of the string.

The counter is just a check as to whether criteria exist, if no criteria then it sets sql without a where condition.

Telephoto