iif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

i'm having trouble at the moment with a query, i want to
convert text such as order status, which is currently
New,Cancelled,Refunded, to either 1=New, 2=Cancelled,
3=Refunded
 
create a new module called whatever and paste this into
it.

Function convertText(oldText as string) as integer
select case oldText
case is = "New"
convertText = 1
case is = "Cancelled"
convertText = 2
case is = "Refunded"
convertText = 3
case else
convertText = 0
end select

End Function

Once you have that created, type this into a new field
within that query (This is assuming the field name that
holds the data in question is called "status", if it's
not, just change it to whatever it is).

Expr1: convertText([status])

That should do it.
 
Create a Calculated Field in your Query with:

StatusNo: Switch([Status] = "New", 1,
[Status] = "Cancelled", 2, [Status] = "Refunded", 3)

all on 1 line.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top