By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

left, right & mid functions HELP!

P: 1
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
Dec 20 '07 #1
Share this Question
Share on Google+
2 Replies


P: 68
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
Dec 20 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
If it's always formatted this way, the state portion would be

mid(strA,len([Field1])-7,2)

Welcome to TheScripts!

Linq ;0)>
Dec 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.