[AccessD] Switch function in a query

The way the DB is set up there has to be a match for each of the three switch conditions. I really don't want an other condition. Also the PID field in the table is sorted but the values are such that thing like WGRP7 comes after WGRP69.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Monday, November 24, 2008 3:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Switch function in a query

Correction: should have said 'which does not have the value "WGRP115",
"WGRP116" or "WGRP117".'  

So if these cryptic data do indeed simply get 'bigger' (next value "WGRP118"
etc) then the switch statement would be something along the lines of

Area:Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",
2,[ConfigMaster]![PID]="WGRP117",3,[PID]>Area:Switch([ConfigMaster]![PID]="W
GRP115",1,[ConfigMaster]![PID]="WGRP116",2,[ConfigMaster]![PID]="WGRP117",3,
[PID]>"WGRP117"),"Out of Range")

Lambert

-----Original Message-----
From: Heenan, Lambert 
Sent: Monday, November 24, 2008 4:15 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Switch function in a query

You have data in the PID field that does not have the value 1,2 or 3. The
Switch statement does not say what to do in that circumstance, so nothing is
displayed in the Area field for those records.

There is no "Else" part to a switch statement, so you need some other
catch-all condition for the other data items, e.g.:

Area:Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",
2,[ConfigMaster]![PID]="WGRP117",3,[PID]>3"Out of Range")

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Monday, November 24, 2008 3:26 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Switch function in a query

This is my first attempt to use this expression in a query. When I use the
expression below I get a value of 1,2 or 3 for each record and also a
duplicate record with the area field blank. What am I doing wrong. I have
also listed below the entire SQL string. The field ChildPID may appear in
the ConfigMaster table multiple times.

Area:Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",
2,[ConfigMaster]![PID]="WGRP117",3)

SELECT [qry Status Date Closest to 1 Month Prior].PID, [qry Status Date
Closest to 1 Month Prior].Well_Number, [qry Status Date Closest to 1 Month
Prior].RecordDate, dbo_DSS_StatusChanges.Status,
Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",2,_
[ConfigMaster]![PID]="WGRP117",3) AS Area FROM ([qry Status Date Closest to
1 Month Prior] INNER JOIN dbo_DSS_StatusChanges ON ([qry Status Date Closest
to 1 Month Prior].MaxOfStatusDate = dbo_DSS_StatusChanges.StatusDate) AND
([qry Status Date Closest to 1 Month Prior].PID =
dbo_DSS_StatusChanges.PID)) INNER JOIN ConfigMaster ON [qry Status Date
Closest to 1 Month Prior].Well_Number = ConfigMaster.ChildPID GROUP BY [qry
Status Date Closest to 1 Month Prior].PID, [qry Status Date Closest to 1
Month Prior].Well_Number, [qry Status Date Closest to 1 Month
Prior].RecordDate, dbo_DSS_StatusChanges.Status,
Switch([ConfigMaster]![PID]="WGRP115",1,[ConfigMaster]![PID]="WGRP116",2,
[ConfigMaster]![PID]="WGRP117",3)
ORDER BY [qry Status Date Closest to 1 Month Prior].Well_Number;

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

 
No trees were killed in the sending of this message. However a large number
of electrons were terribly inconvenienced.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com