1 Field into two this one is tricky

[Heeeeelppp]

I am trying to split an 11 digit field into two fields. I have succeeded
with doing this however not every field has 11 digits some of them are 9 and
that makes the right command inaccurate if it should actually end up being
blank.

Example. 12345678901 <-- starting number in original field

after conversion to two fields 123456789 <---left field 01 <----right field

Problem scenario number 123456789 <-----starting number in original field

after conversion to two fields 123456789 <--- left field 89 <----right field

the 89 in right field needs to be blank if only 9 characters exist in the
original field

 

[Jeff Boyce]

Instead of starting on the right and counting back, how about if you start
on the left and count forwards?

Take a look at the Left() function in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Heeeeelppp]

Here is what I have to work with.

left field
PCP Tax Id: Left([PCP Provider ID],9)

Right field
PCP SUF Id: Right([PCP Provider ID],2)

Use same for left field to make the correct 9 for field one

not sure what to put here to count from left.

 

[fredg]

Here is what I have to work with.

left field
PCP Tax Id: Left([PCP Provider ID],9)

Right field
PCP SUF Id: Right([PCP Provider ID],2)

Use same for left field to make the correct 9 for field one

not sure what to put here to count from left.

You don't need to count from the left if the length is either 9 or 11
characters.
Just test for 11 characters.

PCPSUFID:IIf(Len([PCP Provider ID])=11,Right([PCP Provider ID],2),"")

 

[Heeeeelppp]

Thanks that works.

Here is what I have to work with.

left field
PCP Tax Id: Left([PCP Provider ID],9)

Right field
PCP SUF Id: Right([PCP Provider ID],2)

Use same for left field to make the correct 9 for field one

not sure what to put here to count from left.

You don't need to count from the left if the length is either 9 or 11
characters.
Just test for 11 characters.

PCPSUFID:IIf(Len([PCP Provider ID])=11,Right([PCP Provider ID],2),"")