Solved

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

Posted on 2010-11-30
10
314 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
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.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

789 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