return all records if combo box is null

[AnnPhil]

I have a form that using a combo box to select specific record, sometimes we need to see all records, i would like if the combo box if left empty to return all records. I typed this expression but it returns no records when combo box is empty.

IIf([Forms]![FrmReports]![LoanOfficer]="IsNull","*",[Forms]![FrmReports]![LoanOfficer])

If i use this expression i get the wrong results when a Loan Officer ID is enterd;
LIke([Forms]![FrmReports]![LoanOfficer] & *
Example of wrong results, if Loan officer Id is 1, then it returns loan officers 1,11, 111

Thanks in advance for any help

 

[RuralGuy]

Try:
IIf(IsNull([Forms]![FrmReports]![LoanOfficer]),"*",[Forms]![FrmReports]![LoanOfficer])

 

[AnnPhil]

I tried your suggestion and still got nothing, any other ideas?

thanks

 

[RuralGuy]

Try posting all of the SQL for the query so we can see it.

 

[AnnPhil]

OK here it is

SELECT LoanOfficers.LoanOfficerID, Entities.EntityID, [fname] & " " & [mname] & " " & [lname] AS Individual, [Company] & [Individual] AS Borrower, LoanOfficers.LoanOfficerName, Entities.ReviewDate
FROM LoanOfficers RIGHT JOIN Entities ON LoanOfficers.LoanOfficerID = Entities.LoanOfficer
WHERE (((LoanOfficers.LoanOfficerID)=IIf(IsNull([Forms]![FrmReports]![LoanOfficer]),"*",[Forms]![FrmReports]![LoanOfficer])));

 

[RuralGuy]

I'm sure some of the query guru's will be able to fix that.

 

[Jon K]

Try this Where Clause:

WHERE IIf(IsNull(Forms!FrmReports!LoanOfficer), True, LoanOfficers.LoanOfficerID = Forms!FrmReports!LoanOfficer);

The word True in the expression will return every record when LoanOfficer on the form is null.
.