Select Case module called from select query

[batwings]

Can anyone please tell me how I can place a select case statement in a module and create a function and call it from an expr: field query expression in a select query?

I have a select query with an existing field [Responsibility] and I want to use that field to populate another field that will be called [EC].

The Select case should go something like this,

Select Case [Responsibility]
Case "1"
[EC] = "A"
Case "2"
[EC] = "B"
Case "3"
[EC] = "C"
Case "4"
[EC] = "D"
End Select

I’m not sure what to put in a module or how to call a function from the query..

 

[namliam]

You dont, if you have information like this that needs to be "translated", this is a perfect sample, you make a "lookup" table ! Not a function...

 

[batwings]

namliam

You dont, if you have information like this that needs to be "translated", this is a perfect sample, you make a "lookup" table ! Not a function...

OK all I'm trying to do is use the numeric data from the [Responsibility] field (which originally comes from a lookup table) and create a Text field [EC] that will show in a combo box on a form as Text and not a number.

I tried to use IIF but I have 23 options so I tried to use Switch and both create an error Query too complex so I then thought of using Select case in a function called from my query to create this dummy field that shows text not numbers..

I have 4 cascading combo boxes on a form taking their data from this query but the 4th only returns the numerical data and I want it to return text.

 

[ChrisO]

EC = Chr$(Responsibility + 64)
???

 

[batwings]

Thanks for the suggestions,
I have found a way to get what I needed.

I just created a query expression

EC: Resp([Responsibility])

and then in a module I created a function using the Select Case as follows

[SIZE=3][FONT=Times New Roman]Function Resp(Responsible As String) As String[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Select Case True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Case Responsible = 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Resp = "TEXTA"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Case Responsible = 2[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Resp = " TEXTB"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Case Responsible = 3[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Resp = " TEXTC"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Case Responsible = 4[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Resp = " TEXTD"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Case Responsible = 5[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Resp = " TEXTE"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Case Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Resp = "N/A"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End Select[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]

Seems to work fine for what I wanted...

 

[Rich]

As namliam has already stated another field in the look table would been more manageable and a better simpler solution

 

[namliam]

OK all I'm trying to do is use the numeric data from the [Responsibility] field (which originally comes from a lookup table) and create a Text field [EC] that will show in a combo box on a form as Text and not a number.

I tried to use IIF but I have 23 options so I tried to use Switch and both create an error Query too complex so I then thought of using Select case in a function called from my query to create this dummy field that shows text not numbers..

Go ahead and totaly ignore me if you want, but what your doing is WRONG to the Power of 10.

What happens if there is option 24?
Or if Option 18 needs to change??

Right you have to go into the code and change it... If however you do as I suggested you can allow the user of the application to change/add/delete as they see fit!

Giving you the easy answer of how to make the function and how to use it... Meh... I am trying to help you beyond your question! Trust me, YOU DO NOT WANT TO DO THIS THIS WAY!

<< Sorry for shouting and beeing a bit pushy, but solutions like this BITE >>

 

[boblarson]

Gotta throw my opinion in on the side of the table. It is best to achieve things in a GENERIC way so that you can just add or delete records from a table while not affecting code in any way.