Update fields by splitting text from other field

[Petermgr]

Hi,

I have one field called FULL_ADDRESS. From that field I am going to populate two other fields. One called ADD_1 and the other, ADD_2.

In the FULL_ADDRESS field I have the following text :

"BOX 2747 ROCKY MOUNTAIN HOUSE AB T4T 1L7"

I want ADD_1 updated to:
"BOX 2747 ROCKY MOUNTAIN HOUSE AB"

and ADD_2 updated to:
"T4T 1L7"

While I understand how to do an update if I was using a space as a reference, i.e.
Trim(Left([FULL_ADDRESS],Instrrev([FULL_ADDRESS]," ")-1))
Trim(Mid([FULL_ADDRESS],Instrrev([A]," ")+1))

I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.

Hope I'm making sense here.

Appreciate any tips.

Peter

 

[bob fitz]

Will the text to go in ADD_2 always be seven characters (including the space)

 

[Petermgr]

Will the text to go in ADD_2 always be seven characters (including the space)

Hi Bob,

Thanks for the reply. For the most part, yes, always 7 characters.

Peter

 

[MarkK]

Peter.
Your concept of the solution seems to be, "the string I need is delimited by the second space from the right." Bob has suggested an alternate reality where, "the string you need is 7 characters long." Doesn't that strongly suggest a solution to you?
Mark

 

[bob fitz]

Hi Bob,

Thanks for the reply. For the most part, yes, always 7 characters.

Peter

I don't wish to sound pedantic but "For the most part, yes" and "always 7 characters" are not quite the same.

If it is always seven characters you could use the Right() function to return the last seven characters and the Len() function -7 to return the rest,

 

[Petermgr]

Peter.
Your concept of the solution seems to be, "the string I need is delimited by the second space from the right." Bob has suggested an alternate reality where, "the string you need is 7 characters long." Doesn't that strongly suggest a solution to you?
Mark

Hi Mark,

There are times where someone, by error, keys in "T4T 1L". So I should clarify, it's not always 7 characters. But the majority of records are keyed with 7 characters.

Thanks,

Peter

 

[Petermgr]

I don't wish to sound pedantic but "For the most part, yes" and "always 7 characters" are not quite the same.

If it is always seven characters you could use the Right() function to return the last seven characters and the Len() function -7 to return the rest,

Thanks Bob,

That wasn't a good response on my part, sorry. I should rephrase: The majority of records are keyed with 7 characters. I will try the Right() function for the last seven characters. I'm not familiar with Len() function since I've never had to use it in the past. But I'll try it now.

Thanks again,

Peter