Assistance with MS Access SQL Switch Statement

Question

Hi Folks -

I have the following MS Access SQL query with a single IFF statement that is comprised of multiple conditions.  I'm having trouble turning this into a SWITCH statement, could someone assist?

SELECT 
	[IO Tab].[PFC Alias],
	tblActuals.Name,
	tblActuals.FiscalYear, 
	tblActuals.Period, 
	tblActuals.Nameofoffsettingaccount, 
	tblActuals.Purchasing_Document, 
	IIf([PFC Alias]="PRD Research",[IO Tab]![IO],
		IIf(InStr([Name],"## ")=0,
			IIf(InStr([Name],"##")=0,[Name],Right([Name],(Len([Name])-InStr([Name],"##")-1))),Right([Name],(Len([Name])-InStr([Name],"## ")-2)))
	) AS [Consolidated Detail] 
FROM ((tblActuals 
	LEFT JOIN [CC Tab] ON tblActuals.CostCenter = [CC Tab].[Cost Center]) 
	LEFT JOIN [COA Tab] ON tblActuals.CostElement = [COA Tab].COA) 
	LEFT JOIN [IO Tab] ON tblActuals.IO = [IO Tab].[IO Shortcode]

I also need to ad another condition...

If Purchasing_Document  is not blank, just return tblActuals.Nameofoffsettingaccount otherwise <insert the swtich statement from above>.

Thank you!

Answer 1

Does this do what you want?

SELECT
    ...
    Switch(tbl.Actuals.Purchasing_Document Is Not Null, tblActuals.Nameofoffsettingaccount,
           [IO Tab].[PFC Alias] = "PRD Research", [IO Tab]![IO],
           Mid(tblActuals.[Name], InStr(tblActuals.[Name], "##") - (InStr(tblActuals.[Name], "##") > 0) + 1))
    AS [Consolidated Detail]

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

HI Hans -

Thank you!  I am getting a an error saying wrong number of arguments used in function. I tried to play around with it but can't seem to find where its failing? Thanks!

Answer 3

Sorry, my mistake. I changed the expression and forgot something. It should be

    Switch(tbl.Actuals.Purchasing_Document Is Not Null, tblActuals.Nameofoffsettingaccount,
           [IO Tab].[PFC Alias] = "PRD Research", [IO Tab]![IO], True,
           Mid(tblActuals.[Name], InStr(tblActuals.[Name], "##") - (InStr(tblActuals.[Name], "##") > 0) + 1))
    AS [Consolidated Detail]

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

This is absolutely awesome!! Thank you, Hans!

Here is my final code as I added some filtering:

		Switch(
		Len(tblActuals.Purchasing_Document)> 0 
			AND (Format(DateValue(Format([Period] & " " & [FiscalYear], "YYYY mmm")),"YYYY") > "2019" 
			OR (Format(DateValue(Format([Period] & " " & [FiscalYear], "YYYY mmm")),"YYYY") = "2019" AND Format(DateValue(Format([Period] & " " & [FiscalYear], "YYYY mmm")),"mm") >= "07")), 
				tblActuals.Nameofoffsettingaccount,
		Len(tblActuals.Purchasing_Document)<= 0 
			AND (Format(DateValue(Format([Period] & " " & [FiscalYear], "YYYY mmm")),"YYYY") > "2019" 
			OR (Format(DateValue(Format([Period] & " " & [FiscalYear], "YYYY mmm")),"YYYY") = "2019" AND Format(DateValue(Format([Period] & " " & [FiscalYear], "YYYY mmm")),"mm") >= "07")), 
				Mid(tblActuals.[Name], InStr(tblActuals.[Name], "##") - (InStr(tblActuals.[Name], "##") > 0) + 1),
	   [IO Tab].[PFC Alias] = "PRD Research", [IO Tab]![IO], True,
	   Mid(tblActuals.[Name], InStr(tblActuals.[Name], "##") - (InStr(tblActuals.[Name], "##") > 0) + 1))
	AS [Consolidated Detail],