Solved

Access Like "*" in IIf Statement of Criteria Returns No Records

Posted on 2010-08-25
7
485 Views
Last Modified: 2013-11-29
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_Region]

I have the following in the criteria for the Province ID (pID) and it does not return any records:

IIf([Forms]![frm_Main]![cmb_Province]="", Like "*",[Forms]![frm_Main]![cmb_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
0
Comment
Question by:KevinS2112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 33525119
IIf([Forms]![frm_Main]![cmb_Province]="", "*",[Forms]![frm_Main]![cmb_Province])

What happens if you try it without the like?
0
 

Author Comment

by:KevinS2112
ID: 33525177
It gives the "too complex of incorrectly typed error"
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 33525501
You have a syntax error,
Also, to use  IIF correctly you should specify what to do when the result is true and when the result is False,

Try:

IIf([Forms]![frm_Main]![cmb_Province]="", Like "*" & [Forms]![frm_Main]![cmb_Province] ,"cmb_Province has a specific value")

I am sure you can adapt this to work in your database.

If not, then can you take a step back and explain to us what your ultimate goal is here, ...there may be a more efficient way to do this...

For eaxmple: If your goal is to show all records if no value is selected, the this is typically done simply like this in the query criteria for "ProvinceID:
    Like "*" & [Forms]![frm_Main]![cmb_Province] & "*"

(No real need for IIF...)



;-)

JeffCoachman
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 33525708
IIf([Forms]![frm_Main]![cmb_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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33525906
Sample attached:

Database19.mdb
0
 

Author Comment

by:KevinS2112
ID: 33526131
Thanks, boaq200! The second option you mentioned without IIf did the trick.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33526400
;-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question