?
Solved

Access 2010 Query Expression

Posted on 2015-01-14
18
Medium Priority
?
72 Views
Last Modified: 2015-01-18
Hi Experts,
This shouldn't be a problem for me but it is.  I can't get the following to select properly.

The field is called [AGE_GROUP] and may contain the characters "A" or 'Y".

The Criteria row code is:
IIf([00_PARAMETER_DATA].[ALPHA_PARM_3]="B",Like "*",[00_PARAMETER_DATA].[ALPHA_PARM_3])

What should happen:
- If ALPHA_PARAMETER_3 = "B" then select all records.  i.e. Value "A" and "Y" records.
- If ALPHA_PARAMETER_3  = "A" then select only value "A" records.
- If ALPHA_PARAMETER_3 = "Y" then select only value "Y" records.

What should the syntax be?

Thanks.
Bob Collison.
0
Comment
Question by:Bob_Collison
[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
  • 9
  • 7
18 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 40549556
It will probably look something like this
SELECT [AGE_GROUP], [ALPHA_PARM_3]
FROM mytable
WHERE IIf([ALPHA_PARM_3] In ("A","Y"),[AGE_GROUP]=[ALPHA_PARM_3],[AGE_GROUP] Like "*")=True;

Open in new window

0
 

Author Comment

by:Bob_Collison
ID: 40549761
Hi Expert,
I'm not up to speed on SQL Code so I really need a solution using the Access Query Design Tool.
Thanks.
Bob C.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40549783
Since you haven't told us the tablename and fields of the table containing the [AGE_GROUP] column, I can only suggest example SQL.

If you change the table name from "mytable" to your actual table name and switch from the SQL view to the QBE designer view, it should at least give you a first start.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 46

Expert Comment

by:aikimark
ID: 40549785
or post your database
0
 

Author Comment

by:Bob_Collison
ID: 40549866
Hi Expert,

Below is the SQL Code for your reference.

In actual fact there are 15 tables in the query so the SQL is very long and complex.  The specific table where this field is located is [10_MEMBER_MSTR] which contains Organization Member records.  The [AGE_GROUP] field identifies whether the Member is an Adult (A) or a Youth (Y).

The way this works is that the Form is accessing one Member Record.  The User picks two Options (One or All) Members and Age Group (A (Adult), Y (Youth) or B (Both).  The parameters are written to the [00_PARAMETER_DATA] Table and the query links this table to the Member Master Table via a common field [GENERIC_LINK], extracts the data and displays it in the report.

The criteria other than that associated with the [AGE_GROUP] field work correctly.

SELECT [00_PARAMETER_DATA].USER_OBJECT_KEY, [00_PARAMETER_DATA].SYS_USER_KEY, [00_PARAMETER_DATA].OBJECT_KEY, [00_PARAMETER_DATA].ALPHA_PARM_1, [00_PARAMETER_DATA].ALPHA_PARM_2, [00_PARAMETER_DATA].ALPHA_PARM_3, [00_PARAMETER_DATA].PADDED_PERIOD_KEY, [10_MEMBER_MSTR].SYS_MEMBER_KEY, [PADDED_PERIOD_KEY] & [10_MEMBER_MSTR].SYS_MEMBER_KEY AS PERIOD_MEMBER_KEY, [10_MEMBER_MSTR].MMS_MEMBER_ID, [10_MEMBER_MSTR].CREATE_STAMP, [10_MEMBER_MSTR].SYS_MEMBER_RECORD_TYPE, [02_SYS_MEMBER_STATUS_MSTR].REG_STATUS_CD, [02_SYS_MEMBER_STATUS_MSTR].SHORT_NAME AS 02_SYS_MEMBER_STATUS_MSTR_SHORT_NAME, [10_MEMBER_MSTR].MMS_SCREEN_STATUS_CD, [02_MMS_SCREEN_STATUS_MSTR].LKUP_SHORT_NAME, [02_SYS_MEMBER_TYPE_MSTR].SYS_MEMBER_TYPE_CD, [10_MEMBER_MSTR].PRC_STATUS_CD, [02_SYS_PRC_STATUS_MSTR].SHORT_NAME, [10_MEMBER_MSTR].PRC_START_DATE, [10_MEMBER_MSTR].PRC_END_DATE, [10_MEMBER_MSTR].VSS_STATUS_CD, [02_SYS_PRC_STATUS_MSTR_1].SHORT_NAME, [10_MEMBER_MSTR].VSS_START_DATE, [10_MEMBER_MSTR].VSS_EXPIRY_DATE, [02_SYS_MEMBER_TYPE_MSTR].SHORT_NAME AS 02_SYS_MEMBER_TYPE_MSTR_SHORT_NAME, [10_MEMBER_MSTR].SALUTATION, [10_MEMBER_MSTR].SURNAME, [10_MEMBER_MSTR].FIRST_NAME, [10_MEMBER_MSTR].MIDDLE_NAME, [10_MEMBER_MSTR].INITIALS, [10_MEMBER_MSTR].NICKNAME, [10_MEMBER_MSTR].LANGUAGE_1, [10_MEMBER_MSTR].LANGUAGE_2, [10_MEMBER_MSTR].RELIGION, [10_MEMBER_MSTR].BIRTH_DATE, [10_MEMBER_MSTR].AGE, [10_MEMBER_MSTR].AGE_GROUP, [10_MEMBER_MSTR].LAST_SCHOOL, [10_MEMBER_MSTR].LAST_GRADE, [10_MEMBER_MSTR].HIGHEST_EDUCATION, [10_MEMBER_MSTR].GENDER_CD, [02_PARENTAL_ACCESS_MSTR].PARENTAL_ACCESS_CD, [02_PARENTAL_ACCESS_MSTR].SHORT_NAME AS 02_PARENTAL_ACCESS_MSTR_SHORT_NAME, [10_MEMBER_MSTR].PHOTO_CONSENT_CD, [10_MEMBER_MSTR].HOME_EMAIL_1, [10_MEMBER_MSTR].HOME_EMAIL_2, [10_MEMBER_MSTR].HOME_PHONE_1, [10_MEMBER_MSTR].HOME_PHONE_2, [10_MEMBER_MSTR].CELL_PHONE_1, [10_MEMBER_MSTR].HOME_ADDRESS_KEY, [03_GEO_ADDRESS_MSTR].ADDR_LN_1, [03_GEO_ADDRESS_MSTR].ADDR_LN_2, [03_GEO_ADDRESS_MSTR].ADDR_LN_3, [03_GEO_ADDRESS_MSTR].ADDR_LN_4, [03_GEO_ADDRESS_MSTR].ADDR_LN_5, [03_GEO_ADDRESS_MSTR].ADDR_LN_6, [10_MEMBER_MSTR].EMPLOYER_NAME, [10_MEMBER_MSTR].JOB_TITLE, [10_MEMBER_MSTR].WORK_ADDRESS_KEY, [03_GEO_ADDRESS_MSTR_1].ADDR_LN_1, [03_GEO_ADDRESS_MSTR_1].ADDR_LN_2, [03_GEO_ADDRESS_MSTR_1].ADDR_LN_3, [03_GEO_ADDRESS_MSTR_1].ADDR_LN_4, [03_GEO_ADDRESS_MSTR_1].ADDR_LN_5, [03_GEO_ADDRESS_MSTR_1].ADDR_LN_6, [10_MEMBER_MSTR].WORK_EMAIL_1, [10_MEMBER_MSTR].WORK_PHONE_1, [10_MEMBER_MSTR].WORK_PHONE_1_EXT, [10_MEMBER_MSTR].WORK_PHONE_2, [10_MEMBER_MSTR].WORK_PHONE_2_EXT, [10_MEMBER_MSTR].HEALTH_ID, [10_MEMBER_MSTR].MEDICAL_SUMMARY, [10_MEMBER_MSTR].MEDICAL_DETAILS, [10_MEMBER_MSTR].NOTES, [10_MEMBER_MSTR].CONTACT_1_KEY, [10_MEMBER_MSTR_1].SALUTATION AS C1_SALUTATION, [10_MEMBER_MSTR_1].SURNAME AS C1_SURNAME, [10_MEMBER_MSTR_1].FIRST_NAME AS C1_FIRST_NAME, [10_MEMBER_MSTR_1].MIDDLE_NAME AS C1_MIDDLE_NAME, [10_MEMBER_MSTR_1].INITIALS AS C1_INITIALS, [10_MEMBER_MSTR_1].HOME_PHONE_1 AS C1_HOME_PHONE_1, [10_MEMBER_MSTR].CONTACT_1_RELATIONSHIP, [10_MEMBER_MSTR].CONTACT_1_ADDRESS_KEY, [03_GEO_ADDRESS_MSTR_2].ADDR_LN_1, [03_GEO_ADDRESS_MSTR_2].ADDR_LN_2, [03_GEO_ADDRESS_MSTR_2].ADDR_LN_3, [03_GEO_ADDRESS_MSTR_2].ADDR_LN_4, [03_GEO_ADDRESS_MSTR_2].ADDR_LN_5, [03_GEO_ADDRESS_MSTR_2].ADDR_LN_6, [10_MEMBER_MSTR].CONTACT_2_KEY, [10_MEMBER_MSTR_2].SALUTATION AS C2_SALUTATION, [10_MEMBER_MSTR_2].SURNAME AS C2_SURNAME, [10_MEMBER_MSTR_2].FIRST_NAME AS C2_FIRST_NAME, [10_MEMBER_MSTR_2].MIDDLE_NAME AS C2_MIDDLE_NAME, [10_MEMBER_MSTR_2].INITIALS AS C2_INITIALS, [10_MEMBER_MSTR_2].HOME_PHONE_1 AS C2_HOME_PHONE_1, [10_MEMBER_MSTR].CONTACT_2_RELATIONSHIP, [10_MEMBER_MSTR].CONTACT_2_ADDRESS_KEY, [03_GEO_ADDRESS_MSTR_3].ADDR_LN_1, [03_GEO_ADDRESS_MSTR_3].ADDR_LN_2, [03_GEO_ADDRESS_MSTR_3].ADDR_LN_3, [03_GEO_ADDRESS_MSTR_3].ADDR_LN_4, [03_GEO_ADDRESS_MSTR_3].ADDR_LN_5, [03_GEO_ADDRESS_MSTR_3].ADDR_LN_6, [10_MEMBER_MSTR].BASE_REC
FROM (((((00_PARAMETER_DATA INNER JOIN (((((((10_MEMBER_MSTR LEFT JOIN 03_GEO_ADDRESS_MSTR ON [10_MEMBER_MSTR].HOME_ADDRESS_KEY = [03_GEO_ADDRESS_MSTR].ADDRESS_KEY) LEFT JOIN 02_SYS_MEMBER_STATUS_MSTR ON [10_MEMBER_MSTR].REG_STATUS_CD = [02_SYS_MEMBER_STATUS_MSTR].REG_STATUS_CD) LEFT JOIN 02_SYS_MEMBER_TYPE_MSTR ON [10_MEMBER_MSTR].SYS_MEMBER_TYPE = [02_SYS_MEMBER_TYPE_MSTR].SYS_MEMBER_TYPE_CD) LEFT JOIN 03_GEO_ADDRESS_MSTR AS 03_GEO_ADDRESS_MSTR_1 ON [10_MEMBER_MSTR].WORK_ADDRESS_KEY = [03_GEO_ADDRESS_MSTR_1].ADDRESS_KEY) LEFT JOIN 03_GEO_ADDRESS_MSTR AS 03_GEO_ADDRESS_MSTR_2 ON [10_MEMBER_MSTR].CONTACT_1_ADDRESS_KEY = [03_GEO_ADDRESS_MSTR_2].ADDRESS_KEY) LEFT JOIN 03_GEO_ADDRESS_MSTR AS 03_GEO_ADDRESS_MSTR_3 ON [10_MEMBER_MSTR].CONTACT_2_ADDRESS_KEY = [03_GEO_ADDRESS_MSTR_3].ADDRESS_KEY) LEFT JOIN 02_PARENTAL_ACCESS_MSTR ON [10_MEMBER_MSTR].PARENTAL_ACCESS_CD = [02_PARENTAL_ACCESS_MSTR].PARENTAL_ACCESS_CD) ON [00_PARAMETER_DATA].GENERIC_LINK = [10_MEMBER_MSTR].GENERIC_LINK) LEFT JOIN 10_MEMBER_MSTR AS 10_MEMBER_MSTR_1 ON [10_MEMBER_MSTR].CONTACT_1_KEY = [10_MEMBER_MSTR_1].SYS_MEMBER_KEY) LEFT JOIN 10_MEMBER_MSTR AS 10_MEMBER_MSTR_2 ON [10_MEMBER_MSTR].CONTACT_2_KEY = [10_MEMBER_MSTR_2].SYS_MEMBER_KEY) LEFT JOIN 02_SYS_PRC_STATUS_MSTR ON [10_MEMBER_MSTR].PRC_STATUS_CD = [02_SYS_PRC_STATUS_MSTR].PRC_STATUS_CD) LEFT JOIN 02_SYS_PRC_STATUS_MSTR AS 02_SYS_PRC_STATUS_MSTR_1 ON [10_MEMBER_MSTR].VSS_STATUS_CD = [02_SYS_PRC_STATUS_MSTR_1].PRC_STATUS_CD) LEFT JOIN 02_MMS_SCREEN_STATUS_MSTR ON [10_MEMBER_MSTR].MMS_SCREEN_STATUS_CD = [02_MMS_SCREEN_STATUS_MSTR].MMS_SCREEN_STATUS_CD
WHERE ((([00_PARAMETER_DATA].USER_OBJECT_KEY)=[Forms]![M-00-000 - Scout Admin System - Main Menu]![F-00-001 - System User Lookup SubForm].[Form]![SYS_USER_KEY] & "R-10-210**") AND (([10_MEMBER_MSTR].SYS_MEMBER_KEY)=IIf([00_PARAMETER_DATA].[ALPHA_PARM_1]="All",[10_MEMBER_MSTR].[SYS_MEMBER_KEY],[00_PARAMETER_DATA].[ALPHA_PARM_2])) AND (([10_MEMBER_MSTR].SYS_MEMBER_RECORD_TYPE)="PER") AND (([10_MEMBER_MSTR].AGE_GROUP)=IIf([00_PARAMETER_DATA].[ALPHA_PARM_3]="B",([10_MEMBER_MSTR].AGE_GROUP) Like "*",[00_PARAMETER_DATA].[ALPHA_PARM_3])));

Thanks.
Bob C.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40549916
Please try this version of your WHERE clause:
WHERE ((([00_PARAMETER_DATA].USER_OBJECT_KEY)=[Forms]![M-00-000 - Scout Admin System - Main Menu]![F-00-001 - System User Lookup SubForm].[Form]![SYS_USER_KEY] & "R-10-210**")
 AND (([10_MEMBER_MSTR].SYS_MEMBER_KEY)=IIf([00_PARAMETER_DATA].[ALPHA_PARM_1]="All",[10_MEMBER_MSTR].[SYS_MEMBER_KEY],[00_PARAMETER_DATA].[ALPHA_PARM_2])) AND (([10_MEMBER_MSTR].SYS_MEMBER_RECORD_TYPE)="PER") AND 
 (IIf([00_PARAMETER_DATA].[ALPHA_PARM_3] In ("A","Y"),[10_MEMBER_MSTR].[AGE_GROUP]=[00_PARAMETER_DATA].[ALPHA_PARM_3],[10_MEMBER_MSTR].[AGE_GROUP] Like "*")=True) );

Open in new window

0
 

Author Closing Comment

by:Bob_Collison
ID: 40551097
Hi Expert,
Your solution does work but it doesn't address how to configure it using Access Query Design for those of us who don't know SQL.
Thanks.
Bob C.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40551785
There are some things that the query design wizard can not help.  One of them is writing complex expressions, such as your IIF() function.  The IIF() function comes out of the VB environment.  If you want to know more about this, then the VB language is what you should study.
0
 

Author Comment

by:Bob_Collison
ID: 40553280
Hi Experts,
Having read you comments and the Grading Criteria I agree that 'B' is the correct grading.
Thanks.
Bob C.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40553284
Looking at my IIF() function again, I realized it could be simplified as follows.  The false clause really only needs to be a constant TRUE value.  This should perform better.
WHERE ((([00_PARAMETER_DATA].USER_OBJECT_KEY)=[Forms]![M-00-000 - Scout Admin System - Main Menu]![F-00-001 - System User Lookup SubForm].[Form]![SYS_USER_KEY] & "R-10-210**")
 AND (([10_MEMBER_MSTR].SYS_MEMBER_KEY)=IIf([00_PARAMETER_DATA].[ALPHA_PARM_1]="All",[10_MEMBER_MSTR].[SYS_MEMBER_KEY],[00_PARAMETER_DATA].[ALPHA_PARM_2])) AND (([10_MEMBER_MSTR].SYS_MEMBER_RECORD_TYPE)="PER") AND 
 (IIf([00_PARAMETER_DATA].[ALPHA_PARM_3] In ("A","Y"),[10_MEMBER_MSTR].[AGE_GROUP]=[00_PARAMETER_DATA].[ALPHA_PARM_3],True)=True) );

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 40553286
Why not an A?

When Bob wrote,
What should the syntax be?
my solution comment was exactly the syntax required.
0
 

Author Comment

by:Bob_Collison
ID: 40553469
Hi Experts,
Although this worked and I can use it I can't figure out how to do another query that is very similar, so although it provided the correct result in my opinion the syntax is not understandable / re-usable by me.
Thanks.
Bob C.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40553492
until you master the subject material, you will need to post new questions for every new query.
0
 

Author Comment

by:Bob_Collison
ID: 40556086
OK
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40556132
@Bob

The query wizard is a graphical user interface (dialog window) that actually creates SQL.  If the SQL gets too complicated to render in the query wizard, you have to alter the SQL text without a GUI.  Union queries are one type of query that can't be created/altered in the query wizard.

You should be able to open up the query in the query wizard and learn how my solution SQL is rendered in the query wizard.  It will most likely be reflected in the bottom half of the grid.

Right clicking on some of the cells should display a pop-up menu.  Look for the Build menu item.  If you need help creating expressions, such as an IIF() function, this might be the place to start in the query wizard.

====================
Even if you had posted your database, the query wizard is not a window that can stay open during file transfer.  The query wizard is not a mechanism with which an expert can communicate a solution to you on EE.
0
 

Author Comment

by:Bob_Collison
ID: 40556146
Hi Expert,
Thanks for the information.
Bob C.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

770 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