none
Assistance with MS Access SQL Switch Statement RRS feed

  • 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!

    Tuesday, March 17, 2020 12:30 AM

Answers

  • 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)

    • Marked as answer by cdtakacs1 Wednesday, March 18, 2020 12:55 PM
    Tuesday, March 17, 2020 10:05 AM

All replies

  • 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)

    Tuesday, March 17, 2020 8:58 AM
  • 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!

    Tuesday, March 17, 2020 9:50 AM
  • 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)

    • Marked as answer by cdtakacs1 Wednesday, March 18, 2020 12:55 PM
    Tuesday, March 17, 2020 10:05 AM
  • 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],

    Tuesday, March 17, 2020 6:26 PM