Results 1 to 3 of 3

Thread: SQL sentence

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Exclamation Unanswered: SQL sentence

    Hi, I need to make a query that, according to the value of a field, returns an specified value.

    ie:
    TABLE:
    Col1
    1
    0

    I need to make a query like:
    SELECT (????) FROM TABLE

    and if Col1=1 returns 'Assigned' and if Col2=0 it returns 'Unassigned'

    So the result from the query for the data above is:
    Assigned
    Unassigned

    Thankx

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    SELECT Nz(Switch([Col]=0,"Unassigned",[Col]=1,"Assigned"),"Not Recognized") AS Expr1
    FROM tblSwitch;

    The Nz is in case your table has an entry that is neither 0 nor 1 as Switch would return Null. You won't need it if your data will only ever contain 0 or 1.

    tblSwitch is what I created to test the SELECT statement.

    An alternative would be to set up a translation table with 0,1;Unassigned,Assigned and do a dlookup
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    THANKS A LOT

    Originally posted by HomerBoo
    SELECT Nz(Switch([Col]=0,"Unassigned",[Col]=1,"Assigned"),"Not Recognized") AS Expr1
    FROM tblSwitch;

    The Nz is in case your table has an entry that is neither 0 nor 1 as Switch would return Null. You won't need it if your data will only ever contain 0 or 1.

    tblSwitch is what I created to test the SELECT statement.

    An alternative would be to set up a translation table with 0,1;Unassigned,Assigned and do a dlookup
    Thank you very much. The Nz didn´t worked, but it worked for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •