Having a bit of a problem
i need to extract the state code out of a field formated like this.
WEST BEND, WI 53095
I've managed to get the city and zip code in sperate fields using the right and left function in a query but the state is posing a problem because I cant use a mid function becasue the city lengh will change.
shipto3: IIf([id]=37,Left([field1],InStr([field1],",")-1))
my code to extract everything to the left of the comma
any one have an idea to extrac 3 characters to the right of the comma?
Thanks Ben
Identify the constants, i.e. is there always a space between the town name and state abbreviation, then another space between the latter and the zip code? If so, use InStr to find the position of the first space, then a mid function use it again with a nested Mid / Instr combination to find the second space.
Here's a sample
string = "Treated and Discharged"
InStr([string]," ") tells me the first space is the 8th chr.
InStr(Mid([string],(InStr([string]," "))+1)," ") tells me the second space is the 4th character, or the 13th if I add the two together (because I added a chr in the second equation so it wouldn't just return the first space).
From here it's just maths and nesting to use a Mid to extract the string section you want.
For what it's worth I usually resort to doing this 'longhand' first, i.e. create each element as a separate field in a query, then nest them all together as one formula once I've got it all worked out in my head.
HTH
Kevin
1
