Search code examples
sqlms-accessvb6

convert an option value stored in a table field (without a reference table) into a corresponding text

We have a legacy vb6 solution working with an access database with one of the forms containing a number of mutually exclusive option buttons

options

There is no reference table and the options are saved/loaded using hard coding ie. values in the field optState would be either 0, 1 or 2.

We are building a query in Access to export data into XML and looking for a way to convert the options into text fields without updating the DB or VB application!. So if 0 show some text, 1 some other text ... etc.

Is there a way we can do this in the access query or access sql?

No plans to upgrade the VB or DB so looking for a workaround.

thanks

jay

Solution

  • You could use IIf (Immediate If) to do this in a query. As you have multiple values per field, you would need to nest them. Something like:

    SELECT tblStatus.*, 
IIf([Status]=0,"Available",IIf([Status]=1,"Sold",IIf([Status]=2,"Withdrawn",""))) AS StatusOut
FROM tblStatus;

    This returns an empty string if a value is not 0/1/2. Or you could use Switch in the query:

    SELECT tblStatus.*,
Switch([Status]=0,"Available",[Status]=1,"Sold",[Status]=2,"Withdrawn") AS StatusOut
FROM tblStatus;