Filter Data based value in combo boxes

[tasawer]

Hi,

I have an accident claims form that I need to requery on one of three different fields.

Primary field is AccidentID
ClientName (Can appear on more than one AccidentID)
SolicitorsRef (unique to each accident claim but field can be null)
AccidentRefFina l (reference number for each claim)

from the main form, my comboboxes are:
cmbClientName and cmbAccRefFinal - These requery to find the requested data.

cmbSolRef, brings the required data plus all the nulls.
How can I avoid this to show only the record matching cmbSolRef.

SELECT tblPerson.Completed, Clients.ClientName, tblPerson.SolicitorsRef, tblPerson.AccidentID, tblPerson.AccidentRefFinal
FROM Clients INNER JOIN tblPerson ON Clients.ContactID = tblPerson.ContactID
WHERE (((Clients.ClientName) Like "*" & [Forms]![MMQ_Master_Client]![cmbClientName] & "*") AND ((tblPerson.SolicitorsRef) Like "*" & [Forms]![MMQ_Master_Client]![cmbSolRef] & "*" Or (tblPerson.SolicitorsRef) Is Null) AND ((tblPerson.AccidentID) Like "*" & [Forms]![MMQ_Master_Client]![cmbAccRefFinal] & "*"))
ORDER BY tblPerson.Completed DESC;

Your help is appreciated.

[jimatqsi]

Not sure, but maybe you need to change
AND ((tblPerson.Sol icitorsRef) Like "*"
to
AND ((nz(tblPerson. SolicitorsRef,' ')) Like "*"

Jim

[tasawer]

No.. same result

I have now modified to a different method:

I have a 'cmdShowAll' button to show all records by setting all comboboxes to NULL and requery.

cmbAccRefFinal has RowSource:

SELECT tblPerson.AccidentID, tblPerson.AccidentRefFinal, Clients.ClientName
FROM tblPerson INNER JOIN Clients ON tblPerson.ContactID = Clients.ContactID
ORDER BY tblPerson.AccidentID DESC;

cmbClientName has RowSource:

SELECT tblPerson.AccidentID, Clients.ClientName, tblPerson.AccidentRefFinal
FROM Clients INNER JOIN tblPerson ON Clients.ContactID = tblPerson.ContactID
ORDER BY Clients.ClientName;

cmbSolRef has RowSource:

SELECT tblPerson.AccidentID, tblPerson.SolicitorsRef
FROM tblPerson INNER JOIN Clients ON tblPerson.ContactID = Clients.ContactID
WHERE (((tblPerson.SolicitorsRef) Is Not Null))
ORDER BY tblPerson.SolicitorsRef;

in the AfterUpdate code of all comboboxes, I copy the first coloumn to a temporary text box, txtACCID

the underlying query is now filtered in this manner:

SELECT tblPerson.AccidentID
FROM Clients INNER JOIN tblPerson ON Clients.ContactID = tblPerson.ContactID
WHERE (((tblPerson.AccidentID) Like nz([Forms]![MMQ_Master_Client]![txtAccID],' ') & "*"));

cmbAccRefFinal and cmbClientName bring up the selected record.
but odd thing happen when I select cmbSolRef. e.g.:
when AccidentID=459, filtered records include AccidentID's beginning with 45

I am well confused.
Expert help is greatly appreciated

[tasawer]

Resolved.

I created three textboxes and filled them with data from the comboboxes.
These three text boxes are then used as the search criteria.

Thanks