[AccessD] Switch function in a query

I am not following you here. The same thing as a case statement would like  this

Select Case Configmaster.PID
	Case "WGRP115"
		Area =1
	Case "WGRP116"
		Area =2
	Case "WGRP117"
		Area =3
End Select

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

I could be wrong, but I think you have the WGRP11x and the 1,2 & 3 swapped.

According to help:


Switch Function Example

This example uses the Switch function to return the name of a language
that matches the name of a city.

Function MatchUp (CityName As String)
    Matchup = Switch(CityName = "London", "English", CityName _
                    = "Rome", "Italian", CityName = "Paris", "French")
End Function

Switch returns a Null value if:

None of the expressions is True.


The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only
one of them. For this reason, you should watch for undesirable side
effects. For example, if the evaluation of any expression results in a
division by zero error, an error occurs.



On Mon, Nov 24, 2008 at 12:26 PM, Kaup, Chester
<Chester_Kaup at kindermorgan.com> wrote:
>
> 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