Solved

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

Posted on 2010-11-30
10
315 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

726 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