Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: How can I return a different text value in a query based on the original value?

    I am accessing a read-only SQL database with Access and am using it to generate a report that I will have set up to auto-generate and email every evening in excel format. I have the query working but since the receiving system of this email only understands certain values, I need to change them from what exists in the database. For example, I have a dbo_BadgeTypes_Name field that can result in "SIDA 2010-2011", "SIDA VQ 2010-2011", "FBO 2010-2011", "Northramp VQ 2010-2011". I need these values to return in the query as other names, sometimes just truncated versions but not always such as "SIDA", "Secured", "Sterile". Is there any way to put an iif, instr or other formula into criteria or another field so that I can report these properly?

    EDIT:
    Here is what I was doing in Crystal Reports to solve the problem, under the display string for the item.

    If InStr({BadgeTypes.Name}, 'SIDA') > 0 then "SIDA"
    Else If InStr({BadgeTypes.Name}, 'FBO') > 0 then "Secured"
    Else If InStr({BadgeTypes.Name}, 'Northramp') > 0 then "Secured"
    Else If InStr({BadgeTypes.Name}, 'Construction') > 0 then "Secured"
    Else If InStr({BadgeTypes.Name}, 'CAB') > 0 then "Sterile"

    I am using Access 2010, linked into a read-only database.

    Thank you!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use something like:
    Code:
    SELECT Replace(Replace(Replace(Replace(BadgeTypes.Name,"FBO","Secured"),"Northramp","Secured"),"Construction","Secured"),"CAB","Sterile") AS Expr1
    FROM BadgeTypes;
    As for 'SIDA' I don't see any differences.
    Have a nice day!

Posting Permissions

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