KevinS2112
asked on
Access Like "*" in IIf Statement of Criteria Returns No Records
Hi Experts,
I am developing an Access db with two tables for Regions and Provinces. The tables are called tbl_Regions and tbl_Province respectively. They are joined one-to-many where each region has many provinces. I have a Main form with two combo boxes cmb_Region and cmb_Province. I want to use each box in the criteria of a query. The bound value for each is an integer (AutoNumber) value. When a user selects a region, the query should return the records for that region and all provinces. If the user also selects a province, only that provinces records should be returned.
I have the following criteria for the Region ID (rID) and it works fine:
[Forms]![frm_Main]![cmb_Re gion]
I have the following in the criteria for the Province ID (pID) and it does not return any records:
IIf([Forms]![frm_Main]![cm b_Province ]="", Like "*",[Forms]![frm_Main]![cm b_Province ])
If I just put Like “*” in the criteria I get all Provinces. If I enter a literal (like the number 4) instead of Like “*” for the true side of the IIf, I get the Province 4 records. The IIf statement seems to working correctly. It is just when I try to use Like”*” within it that I have a problem.
LikeInIIf.doc
I am developing an Access db with two tables for Regions and Provinces. The tables are called tbl_Regions and tbl_Province respectively. They are joined one-to-many where each region has many provinces. I have a Main form with two combo boxes cmb_Region and cmb_Province. I want to use each box in the criteria of a query. The bound value for each is an integer (AutoNumber) value. When a user selects a region, the query should return the records for that region and all provinces. If the user also selects a province, only that provinces records should be returned.
I have the following criteria for the Region ID (rID) and it works fine:
[Forms]![frm_Main]![cmb_Re
I have the following in the criteria for the Province ID (pID) and it does not return any records:
IIf([Forms]![frm_Main]![cm
If I just put Like “*” in the criteria I get all Provinces. If I enter a literal (like the number 4) instead of Like “*” for the true side of the IIf, I get the Province 4 records. The IIf statement seems to working correctly. It is just when I try to use Like”*” within it that I have a problem.
LikeInIIf.doc
ASKER
It gives the "too complex of incorrectly typed error"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IIf([Forms]![frm_Main]![cm b_Province ]="", "",[Forms]![frm_Main]![cmb _Province] )
Perhaps you could try an empty string as the first option in the IIF statement since you will not actually need criteria for that field.
Perhaps you could try an empty string as the first option in the IIF statement since you will not actually need criteria for that field.
ASKER
Thanks, boaq200! The second option you mentioned without IIf did the trick.
;-)
What happens if you try it without the like?