Selecting numbers from text string and placing in new fields

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have a table with a text field called LLDIS. The field
contains two data parts, a land lot (ll) and a district
(dis). I want to put the values for each part in a new
field for each.

For example, LLDIS field might contain "ll 123 dis 5"
or "ll 12 dis 13" or "ll 3 dis 6" and so on. I want to
take the numbers after the last 'l' but before the
letter 'd' and put them in a field called LANDLOT. And
everything after the 's' and put it in a field called
DISTRICT.

Any assistance would be appreciated.
 
Do all the fields start with "ll " (L L blank)? If so, you can do it like
this:

LandLot: Trim(Mid([LLDIS], 3, Instr([LLDIS], "dis") - Instr([LLDIS], " ")))

District: Trim(Mid([LLDIS], Instr([LLDIS], "dis") +3))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
You were right on. That worked beautifully. Thanks.
-----Original Message-----
Do all the fields start with "ll " (L L blank)? If so, you can do it like
this:

LandLot: Trim(Mid([LLDIS], 3, Instr([LLDIS], "dis") - Instr([LLDIS], " ")))

District: Trim(Mid([LLDIS], Instr([LLDIS], "dis") +3))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I have a table with a text field called LLDIS. The field
contains two data parts, a land lot (ll) and a district
(dis). I want to put the values for each part in a new
field for each.

For example, LLDIS field might contain "ll 123 dis 5"
or "ll 12 dis 13" or "ll 3 dis 6" and so on. I want to
take the numbers after the last 'l' but before the
letter 'd' and put them in a field called LANDLOT. And
everything after the 's' and put it in a field called
DISTRICT.

Any assistance would be appreciated.
Click to expand...


.
Click to expand...
 
Back
Top