assign value to variable

[rinova]

Hi all,

I have a field that is populated the a single letter code. I want to write a query that will assign a number to the code.

CODES = ASSIGNED NUMBER
H = 2
W = 2
If the code is not H or W = 3
If there is no code or blank = 1

Thanks

 

[plog]

The simplest way is to write a simple VBA function to do this logic for you.

Function getCodeNum(c)
    ' c is code
 
ret=3
    ' default value
 
If Isnull(c) OR c="" Then ret=1
If c="H" OR c="W" Then ret=2
 
getCode=ret
End Function

Paste the above into a module and then use it where you need it (query, form, report).

 

[rinova]

thanks for the quick response I will try it over the weekend and let you know how it went.

 

[plog]

I agree with bob on this in general but not specifically in this instance. Because there's only 2 definite codes (H & W) I think its ok to shove it into code. If there were more I'd have suggested a new table. And in any case, to account for blanks and nulls there's still going to need to be some sort of code logic.

 

[rinova]

The code provided by Plog worked perfectly.

Thank you Plog!