I've got a search form that has ContractNo, Name, Firstname and PhoneNo as four text fields. I need to look up informtation in a table and display matching records. I've looked at a couple of thread in this forum and it seams a lot of people are having issues with query criterias liked to form fields. I'll try to explain my situation.
Table tblClients:
ClientNo; ClientName; ClientFirstname; PhoneNo1; PhoneNo2; ContractNo1; ContractNo2; ContractNo3.
In my query, I need all these fields. A client has a Name, a Firstname and a ContractNo1 but might not have phone numbers entered nor second or third contract numbers. My problem is with the phone numbers and contract numbers fields when I try to lookup information, regardless of the field it is strored in.
I've got {Like "*" & [Forms]![fLookup]![ContractNo] & "*"} and this works when there is information in a field but when a field is left empty, it doesn't.
I've gone back to the simple basics and now I've only got criterias for Name, Firstname and ContractNo1. I've tried many things like {IIF([Forms]![fLookup]![ContractNo]<>"", Like "*" & [Forms]![fLookup]![ContractNo] & "*" OR nz([Forms]![fLookup]![ContractNo]=""="",False)} a many others.
Can any one give me advice on how to do this. I have set up a dummy db with a the client table, the lookup query and the two forms I use for this. It's in Access 97.
Thanks
J-F
Table tblClients:
ClientNo; ClientName; ClientFirstname; PhoneNo1; PhoneNo2; ContractNo1; ContractNo2; ContractNo3.
In my query, I need all these fields. A client has a Name, a Firstname and a ContractNo1 but might not have phone numbers entered nor second or third contract numbers. My problem is with the phone numbers and contract numbers fields when I try to lookup information, regardless of the field it is strored in.
I've got {Like "*" & [Forms]![fLookup]![ContractNo] & "*"} and this works when there is information in a field but when a field is left empty, it doesn't.
I've gone back to the simple basics and now I've only got criterias for Name, Firstname and ContractNo1. I've tried many things like {IIF([Forms]![fLookup]![ContractNo]<>"", Like "*" & [Forms]![fLookup]![ContractNo] & "*" OR nz([Forms]![fLookup]![ContractNo]=""="",False)} a many others.
Can any one give me advice on how to do this. I have set up a dummy db with a the client table, the lookup query and the two forms I use for this. It's in Access 97.
Thanks
J-F