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:
I have the following in the criteria for the Province ID (pID) and it does not return any records:
]="", Like "*",[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.