Solved

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

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

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

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.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
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…
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 …

929 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now