Parsing and "translating" numbers

[SalGal]

Two parts to this question, but first background:

Access 2003
Linked table (ODBC) with a 2-character text field
Values stored in the field are numbers
The first number represents relationship: mother, father, grandmother, husband, wife, other
The second number is a modifier, if you will, of the relationship - natural, step, foster, in-law

Field values can look like this:
21
11
2
8
<blank>
22
7

Problem 1: Sometimes there is one number in the field, sometimes two. This presents a problem when trying to parse the second number because Right([field],1) only works correctly if there are 2 numbers in the field. I know I need some sort of If/then type statement to evaluate whether there is one or two numbers in the field, but I can't seem to get my brain around how to do that.

Problem 2: Then, once the number(s) are separated, I need to assign the text values to them. I'm pretty sure I have this part figured out, but isn't working because of Problem #1.

Appreciate any suggestions. Thanks!

 

[Axworthy]

Assuming that a single character (such as 2) would always be read as 2 blank and never blank 2, you could convert everything to a two character field like this:

Left(Trim(Nz([Field Name], "")) & "00", 2)

Your values would be converted:

21 21
11 11
2 20
8 80
<blank> 00
22 22
7 70

 

[randy700]

Why not simply use this?

If field < 10 Then

Randy

 

[PHV]

Randy, you're far away from JetSQL syntax ...

 

[SalGal]

Axworthy - so close!

Unfortunately, "0" has an assigned value (spouse) for the first character, so I can't convert the blanks in the first space to zeros; I can use that logic for the second character since it does not use "0" as a possible values.

I'll continue to play with it. . . thanks.

 

[PHV]

Replace this:

Left(Trim(Nz([Field Name], "")) & "00", 2)

with this:

Left(Trim([Field Name] & "") & "  ", 2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SalGal]

Great! Thanks for the help!