none
How to use Switch in Select statement RRS feed

  • General discussion

  • I'm trying to change a query so that it returns the customer's first and last name in the field "KundeNavn" if the order is not cancelled, or the string "CANCELLED" if it is. I've mostly got it working using a Switch statement, but instead of "CANCELLED" I just get an empty string. Could someone point out the problem in this statement please?

    SELECT DISTINCT tblOrdrelinjer.OrdreID, Left([LeilighetsNavn],InStr([LeilighetsNavn] & "-","-")-1) AS Leil, tblOrdre.MinFra AS Fra, tblOrdre.MaxTil AS Til, tblOrdre.Barnesenger, tblOrdre.Fond, tblOrdre.Matpakke, tblOrdre.MatpakkeKommentar, tblOrdrelinjer.Avbestilt, tblOrdrelinjer.Avbestiltdato, tblOrdre.Annet, tblOrdre.Strøm, tblOrdre.Depositum, tblOrdre.DepositumValuta, [KontraktType] & " - " & [KontraktNummer] AS Kontrakt, 
    
    Switch (
    	tblOrdrelinjer.Avbestilt=0, [Fornavn] & " " & [Etternavn],
    	tblOrdrelinjer.Avbestilt=1, 'CANCELLED'
    ) AS KundeNavn 
    
    INTO tempAnkomstliste
    FROM (tblPersoner INNER JOIN tblOrdre ON tblPersoner.KundeID = tblOrdre.KundeID) INNER JOIN (tblOrdrelinjer INNER JOIN tblLeilighet ON tblOrdrelinjer.LeilighetID = tblLeilighet.LeilighetID) ON tblOrdre.OrdreID = tblOrdrelinjer.OrdreID
    WHERE (((Left([LeilighetsNavn],InStr([LeilighetsNavn] & "-","-")-1)) Not Like 'Lunamar' & '*') AND ((tblOrdre.MinFra)>=[Forms]![frmAnkomstliste]![txtFra] And (tblOrdre.MinFra)<[Forms]![frmAnkomstliste]![txtTil]));
    

    This is a veeery old and brittle solution that I've managed to upgrade from Access 97 to 2007 version, so I'd like to change as little as possible.

    TIA!

    Dennis

    Monday, July 11, 2016 10:11 PM

All replies

  • What type of field is [tblOrdrelinjer].[Avbestilt] ?  If it's a yes/no field, its values will be 0 or -1, not 0 or 1.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, July 11, 2016 11:24 PM
  • I'm under the impression the SWITCH statement does not apply to Access SQL. Are you using an Access BE, or SQL Server?

    If Access, use the IIf function.


    -Tom. Microsoft Access MVP

    Monday, July 11, 2016 11:26 PM
  • I'm under the impression the SWITCH statement does not apply to Access SQL. Are you using an Access BE, or SQL Server?

    If Access, use the IIf function.


    Switch() is a VBA function, but like most VBA functions it can be used in Access SQL.  IIf() would be more efficient, since it is implemented natively in Jet SQL and doesn't require a call to VBA, but Switch() should work. 

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, July 12, 2016 4:04 AM
  • D'oh! Of course you're right. Changed the bit test to -1 and now everything works. Couldn't see the forest for the trees there...

    I'll look into the IIF statement, but at least I got it working. Thank you so much.

    Re

    Dennis

    Tuesday, July 12, 2016 9:14 AM
  • With boolean fields across multiple database engines, it's generally safest to test for 0 = false, nonzero = true.  In Access, boolean fields can't be Null, so you could write

        Switch (
            tblOrdrelinjer
    .Avbestilt=0, [Fornavn] & " " & [Etternavn],
            True, 
    'CANCELLED'
        ) AS KundeNavn

    or

        IIf(tblOrdrelinjer.Avbestilt=0, [Fornavn] & " " & [Etternavn], 'CANCELLED') AS KundeNavn

    I generally avoid testing for a specific nonzero value -- e.g., -1, or 1 -- as True, because that varies from database engine to database engine.  In a SQL Server bit field, True is represented as 1, while in an Access yes/no field it will be -1.  So I either test for "field = 0 means False, else means True", or I test for "field <> 0 means True".  That way if my code or queries get ported to SQL Server or some other database engine, they are more likely to work without revision.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, July 12, 2016 4:28 PM