Thanks John. I will try this. This makes more sense than what I was trying.
Bill Beshlian
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, December 11, 2013 10:35 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Proper use of Switch in Access 2010 Query
Bill-
Do you need this for something other than display? For example, do you need to do a JOIN on Lic State? If not, then consider:
LicState: IIf(Not IsNull([Lic State]), [Lic State], Switch([Lic Cert Code] = "ABC", [Work State], [Lic Cert Code] = "XYZ", [Other State], …))
I used an IIf to check first if Lic State is empty. If not, you'll get that value. If it is empty, then it'll fall into the Switch evaluation to look at the Cert Code to determine the correct field from which to pull the state. Make sense? Then use LicState as the display value.
If you need to permanently update Lic State, then you could do:
UPDATE MyTable
SET [Lic State] = Switch( ….. )
WHERE [Lic State] IS NULL
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Beshlian,Bill
Sent: Wednesday, December 11, 2013 2:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Proper use of Switch in Access 2010 Query
John,
I am doing this in a query. Here is the problem. I am querying license and certification data from our database. While some of the results have the proper code in the [Lic State] field others are blank. Depending on the license/certification the [Lic State] field needs the two letter state abbreviation the where the employee works, where the license was awarded or where the employee calls their home state.
Thanks,
Bill
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Tuesday, December 10, 2013 4:13 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Proper use of Switch in Access 2010 Query
Bill-
If you're assigning the result to the Work State field, then maybe yes. Are
you doing this in code, a query, or what? What is the business problem
you're trying to solve?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of William
Sent: Tuesday, December 10, 2013 10:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Proper use of Switch in Access 2010 Query
Rather than driving myself crazy with a nested IIF statement I want to use
the Switch function. After reviewing the syntax for the function I have a
question. Will a statement like Switch([Lic Cert Code] = "ABC" AND [Lic
State] IS NULL, [Work State]) evaluate properly and place the current value
of [Work State] in the [Lic State] field? If not what would? I have
multiple cases and a nested IIF statement would get ugly. Thanks in
advance!
Bill
------------------------------------
Yahoo Groups Links
The information contained in this communication, including attachments, is confidential and private and intended only for the use of the addressees. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you received this communication in error, please inform us of the erroneous delivery by return e-mail message from your computer. Additionally, although all attachments have been scanned at the source for viruses, the recipient should check any attachments for the presence of viruses before opening. Alegent Creighton Health accepts no liability for any damage caused by any virus transmitted by this e-mail. Thank you for your cooperation.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar