[AccessD] Switch function in a query

Not sure yet what your problem is, but how replacing the switch with

... Val(Right([ConfigMaster]![PID],1)) - 4 As Area...

-- 
Stuart

On 24 Nov 2008 at 14:26, Kaup, Chester 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]="WGRP
> 116",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; 
>