Use Switch for Field Parameter in Select Statement (MS Access)

[bs0d]

I've read here: http://stackoverflow.com/questions/5...ter-in-a-query that you can use SWITCH to define a field as a parameter in a query. The example solution is said to work in Access, but uses an UPDATE query. See below:

PARAMETERS WhichDate Long;
UPDATE tblTarget AS t INNER JOIN tblSource AS s ON t.id = s.id
SET t.date_assigned = Switch(WhichDate=1,date1,WhichDate=2,date2);

How might the query look as a SELECT statement, or can this be done? I'm trying to use this to display 1 of 3 fields depending on which the user picks.
However, the code does not work. Thoughts?

PARAMETERS WhichField Text ( 255 );
SELECT p.myID, 
s.docDate AS [myDate], 
myField = Switch(WhichField="Sales1",'s.Sales1',WhichField="Sales2",'s.Sales2',WhichField="Sales3",'s.Sales3')
FROM prod AS p INNER JOIN sumDaily AS s ON p.prodID = s.entityID
GROUP BY p.myID, s.docDate;

[ssanfu]

I have never used a parameter in a query like this. Your Switch clause was wrong. The syntax is wrong for: "myField = Switch(Wh...)". You have to use the "AS" keyword.

Maybe:

PARAMETERS WhichField Text ( 255 );
SELECT p.MyID, s.docDate AS MyDate,
 Switch([WhichField]="Sales1","s.Sales1",[WhichField]="Sales2","s.Sales2",[WhichField]="Sales3","s.Sales3") AS myfield 
FROM Prod AS p INNER JOIN sumDaily AS s ON p.MyID = s.entityID
GROUP BY p.MyID, s.docDate;

[bs0d]

Thanks Steve. That cleans things up, but when I type "Sales2" in the parameter, it's returning the text "s.Sales2" for each row, instead of using values from [s].[Sales2] for that field.

I tried using sumDaily.Sales2, [sumDaily].[Sales2] and other combos, but it's still treating it as text. I wonder if this works with a SELECT statement at all?

[ssanfu]

Doh!!

Remove the quotes from s.Sales1, s.Sales2 & s.Sales3. These are fields, not text strings. You want the VALUE.

Switch([WhichField]="Sales1",s.Sales1,[WhichField]="Sales2",s.Sales2,[WhichField]="Sales3",s.Sales3) AS myfield

[bs0d]

PARAMETERS WhichField Text ( 255 );
SELECT p.MyID, s.docDate AS MyDate,
 Switch([WhichField]="Sales1",s.Sales1,[WhichField]="Sales2",s.Sales2,[WhichField]="Sales3",s.Sales3) AS myfield 
FROM Prod AS p INNER JOIN sumDaily AS s ON p.MyID = s.entityID
GROUP BY p.MyID, s.docDate, myfield;

This results in: Your query does not include the specified expression
'Switch([WhichField]="Sales1",s.Sales1,[WhichField]="Sales2",s.Sales2,[WhichField]="Sales3",s.Sales3)' as a part of an aggregate function

If I replace s.Sales1 with [sumDaily].[Sales1] and so on, then it asks for the value as if it's a parameter. Still no glory.

Also to be clear, my desired output is: if parameter = "Sales1" to display the values in the field Sales1 from table sumDaily.

[orange]

Can I ask you to tell readers what exactly you are trying to do?
Is it to have a Parameter query, or is it to use the Switch()?

If Parameters, then here's a sample

PARAMETERS [Bdate] DateTime, [Edate] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipVia, Orders.ShipName
FROM Orders
WHERE (((Orders.OrderDate) Between [Bdate] And [EDate]));

another

PARAMETERS TopStep IEEEDouble;
SELECT xActionstbl.contractNum
FROM xActionstbl
WHERE step=TopStep;

[bs0d]

Both.

I want to use a parameter to tell the SQL which field to display in the query result.

In my example, there are 3 options.

Input:
"Sales1" --> use/display Sales1 field from SumDaily table.
"Sales2" --> use/display Sales2 field from SumDaily table.
"Sales3" --> use/display Sales3 field from SumDaily table.

This was demonstrated in my first post, but utilized an UPDATE query. My question is, can this be done in a SELECT statement? I can't get it to work. But I'm no SQL guru either. So I thought I would ask on this forum if anyone has experience doing this.

[orange]

This code works for me on an existing query. I just added the SWITCH function.

PARAMETERS whichfield Long;
SELECT Animal.AName, 
Switch(whichfield=1,AnimalLocs.SightingDate,WhichField=2,AnimalCapture.CaptureDate,Whichfield=3
,AnimalLocs.GPSLat & " " & AnimalLocs.GPSLong) AS SwitchExample
FROM (Animal INNER JOIN AnimalCapture ON Animal.AnimalId = AnimalCapture.AnimalId) INNER JOIN AnimalLocs ON Animal.AnimalId = AnimalLocs.AnimalId
WHERE (((AnimalLocs.SightingDate)>[AnimalCapture].[CaptureDate]))
ORDER BY Animal.AName, AnimalLocs.SightingDate;

[bs0d]

Weird; I wonder if version of Access makes a difference? I'm using 2013 (I still get the aggregate function error point to Switch()).

[orange]

My sample doesn't do any aggregation ---simple select with 3 tables.

[bs0d]

That did it. It works. This can be handy.

Thanks for your help!

[ssanfu]

Can't use "MyField" in the Group By clause.
I'm really not sure, but try:

PARAMETERS WhichField Text ( 255 );
SELECT p.MyID, s.docDate AS MyDate,
 Switch([WhichField]="Sales1",s.Sales1,[WhichField]="Sales2",s.Sales2,[WhichField]="Sales3",s.Sales3) AS myfield 
FROM Prod AS p INNER JOIN sumDaily AS s ON p.MyID = s.entityID
GROUP BY p.MyID, s.docDate, Switch([WhichField]="Sales1",s.Sales1,[WhichField]="Sales2",s.Sales2,[WhichField]="Sales3",s.Sales3);

[bs0d]

I got it to run by changing GROUP BY to ORDER BY

[ssanfu]

Great. I saw the post from Orange... I had created the query without the aggregation, but it was on your original query, so I left it as is. Glat you got it working.