Solved

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

Posted on 2010-11-30
10
310 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 125 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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