Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I hide one field if another one is blank?

Posted on 2010-11-30
10
Medium Priority
?
322 Views
Last Modified: 2012-05-11
If have a query i wrote that will show a possibility of two error codes, 19 and 21, however, if error code 21 is not present, i don't want it to show the contents of error code 19 at all.  Error code 19 is a warning, error code 21 is the bypass of the warning.  if the user bypasses the warning (19), both fields should show (19 and 21) but if the user heeds the warning, then nothing should show at all.
I've attached the SQL version of the query for your review.
SELECT [2_PHM_MAC_ERR_DTL].SITE, [2_PHM_MAC_ERR_DTL].CHARTED_DATE, [2_PHM_MAC_ERR_DTL].CHARTED_TIME, [2_PHM_MAC_ERR_DTL].LOCATION, [2_PHM_MAC_ERR_DTL].PRIMARY_NAME, [2_PHM_MAC_ERR_DTL].COMP_NO, [2_PHM_MAC_ERR_DTL].ERROR_CODE, [2_PHM_MAC_ERR_DTL].ADMIN_STATUS, [2_PHM_MAC_ERR_DTL].ADMIN_DATE, [2_PHM_MAC_ERR_DTL].ADMIN_TIME, [2_PHM_MAC_ERR_DTL].NURSE_ID, [2_PHM_MAC_ERR_DTL].PAT_NUM, [2_PHM_MAC_ERR_DTL].DRUG_CODE, SITE.NAME, [2_PHM_MAC_ERR_DTL].REASON, [2_PHM_MAC_ERR_DTL].ROUTE, [2_PHM_MAC_ERR_DTL].PTNAME, [2_PHM_MAC_ERR_DTL].PTBED, [2_PHM_MAC_ERR_DTL].PMP, [2_PHM_MAC_ERR_DTL].PRIMARY_INGRED, [2_PHM_MAC_ERR_DTL].SIG, [2_PHM_MAC_ERR_DTL].ADMIN_STATUS, [2_PHM_MAC_ERR_DTL].SCANNED_DATA, [2_PHM_MAC_ERR_DTL].DOSE, [2_PHM_MAC_ERR_DTL].ADM_DOSE_OVR, [2_PHM_MAC_ERR_DTL].EARLY_OR_LATE_RSN, PHM_MAC_USERS.USER_NAME
FROM (2_PHM_MAC_ERR_DTL LEFT JOIN SITE ON [2_PHM_MAC_ERR_DTL].SITE = SITE.SITE) INNER JOIN PHM_MAC_USERS ON [2_PHM_MAC_ERR_DTL].NURSE_ID = PHM_MAC_USERS.USER_INITIALS
WHERE ((([2_PHM_MAC_ERR_DTL].SITE)="1") AND (([2_PHM_MAC_ERR_DTL].CHARTED_DATE)="20101129") AND (([2_PHM_MAC_ERR_DTL].ERROR_CODE)="21" Or ([2_PHM_MAC_ERR_DTL].ERROR_CODE)="19") AND (([2_PHM_MAC_ERR_DTL].PRIMARY_INGRED)="YES"));

Open in new window

0
Comment
Question by:davidpel
[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
  • 6
  • 4
10 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 34240723
I'm not sure if I've entirely understood you, but you should be able to get what you want by adding an EXISTS() clause to your WHERE criteria - although the query might be slow as a result!
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34240741
If you post a sample database, I'll show you where I'm coming from!  :-)
0
 

Author Comment

by:davidpel
ID: 34240755
can you expand on that in terms of an actual statement?, in other words, if, for a single patient (based on their account #) they should show both error codes (19 and 21), if only 19 is present, then suppress it.
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.

 

Author Comment

by:davidpel
ID: 34240777
i can't, in that i don't have test data for this.
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34240808
Am I right in thinking that the error codes you want the query to return are in separate RECORDS not separate FIELDS?
0
 

Author Comment

by:davidpel
ID: 34240842
yes, that is correct.  but remember each different patient would have a unique acct # and based on that, i would then check the error codes so that if PAT_NUM is the same as the next PAT_NUM on the list, then check error codes and if both 19 and 21 don't both exist for this record, then show nothing, otherwise show both.
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34240845
I can't test my idea without sample data - and nor can you either!  ;-)

I'm afraid I'm out of time today, but I was thinking along these lines (note that the EXISTS() sub-queries will definitely need modifiying):
SELECT [2_PHM_MAC_ERR_DTL].SITE, [2_PHM_MAC_ERR_DTL].CHARTED_DATE, [2_PHM_MAC_ERR_DTL].CHARTED_TIME,
[2_PHM_MAC_ERR_DTL].LOCATION, [2_PHM_MAC_ERR_DTL].PRIMARY_NAME, [2_PHM_MAC_ERR_DTL].COMP_NO,
[2_PHM_MAC_ERR_DTL].ERROR_CODE, [2_PHM_MAC_ERR_DTL].ADMIN_STATUS, [2_PHM_MAC_ERR_DTL].ADMIN_DATE,
[2_PHM_MAC_ERR_DTL].ADMIN_TIME, [2_PHM_MAC_ERR_DTL].NURSE_ID, [2_PHM_MAC_ERR_DTL].PAT_NUM,
[2_PHM_MAC_ERR_DTL].DRUG_CODE, SITE.NAME, [2_PHM_MAC_ERR_DTL].REASON,
[2_PHM_MAC_ERR_DTL].ROUTE, [2_PHM_MAC_ERR_DTL].PTNAME, [2_PHM_MAC_ERR_DTL].PTBED,
[2_PHM_MAC_ERR_DTL].PMP, [2_PHM_MAC_ERR_DTL].PRIMARY_INGRED, [2_PHM_MAC_ERR_DTL].SIG,
[2_PHM_MAC_ERR_DTL].ADMIN_STATUS, [2_PHM_MAC_ERR_DTL].SCANNED_DATA, [2_PHM_MAC_ERR_DTL].DOSE,
[2_PHM_MAC_ERR_DTL].ADM_DOSE_OVR, [2_PHM_MAC_ERR_DTL].EARLY_OR_LATE_RSN, PHM_MAC_USERS.USER_NAME  
FROM (2_PHM_MAC_ERR_DTL LEFT JOIN SITE ON [2_PHM_MAC_ERR_DTL].SITE = SITE.SITE) INNER JOIN PHM_MAC_USERS
ON [2_PHM_MAC_ERR_DTL].NURSE_ID = PHM_MAC_USERS.USER_INITIALS
WHERE [2_PHM_MAC_ERR_DTL].SITE = "1" AND
[2_PHM_MAC_ERR_DTL].CHARTED_DATE = "20101129" AND
([2_PHM_MAC_ERR_DTL].ERROR_CODE = "19" AND EXISTS(SELECT Null FROM [2_PHM_MAC_ERR_DTL] WHERE ERROR_CODE = "21") OR
[2_PHM_MAC_ERR_DTL].ERROR_CODE = "21" AND EXISTS(SELECT Null FROM [2_PHM_MAC_ERR_DTL] WHERE ERROR_CODE = "19"))
[2_PHM_MAC_ERR_DTL].PRIMARY_INGRED = "YES"

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34240874
Be warned that the above query may well run very slowly!  ;-(

You'll get much quicker results by adding another JOIN - but I'll definitely need a sample database to help you with that.
0
 
LVL 17

Accepted Solution

by:
JezWalters earned 375 total points
ID: 34703095
How are you getting on with this?
0
 

Author Closing Comment

by:davidpel
ID: 36217102
solution couldn't be reached.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 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