"In Between" function that behaves similarly to a "Mid String" function

[KP_SoCal]

"In Between" function that behaves similarly to a "Mid String" function

In Table1, I have field name WidgetID whose records have an alpha-numeric constant of 1A23 at the beginning of each string and 432A1 at the end. In between could be any number of alpha-numeric characters. I’d like to return the results for each record into the records under field name BaseID.

The results would look something like this…

Field Name: WidgetID
Record1: 1A23abcd4A321
Record2: 1A23abcdefg4A321
Record3: 1A23ab4A321
Record4: 1A23abcde4A321

Field Name: BaseID
Record1: abcd
Record2: abcdefg
Record3: ab
Record4: abcde

I would run this function within the select statement of a query object in Access 2007. Thanks ahead of time for any help!

 

[pbaldy]

Re: "In Between" function that behaves similarly to a "Mid String" function

You can use the Mid() function with the Len() function to calculate the length argument, with a little math.

 

[John Big Booty]

Re: "In Between" function that behaves similarly to a "Mid String" function

If the Prefix and Suffix of your Widget ID is static, then why not simply store the variable portion of the WidgetID and append the Prefix and Suffix for display purposes only?

 

[John Big Booty]

Re: "In Between" function that behaves similarly to a "Mid String" function

Or use you could use the Replace() Function to replace the "1A23" and "4A321" strings with Zero length Strings ("")

 

[raskew]

Re: "In Between" function that behaves similarly to a "Mid String" function

Hi -

Couple of examples using Paul's suggestion. Provided the beginning and end portions remain the same lengths, this should work for any combination in the middle:

x = "1A23abcdefg4A321"
? mid(x, 5, len(x)-9)
abcdefg


x = "1A23ab4A321"
? mid(x, 5, len(x)-9)
ab

HTH - Bob

 

[KP_SoCal]

Re: "In Between" function that behaves similarly to a "Mid String" function

Hi -

Couple of examples using Paul's suggestion. Provided the beginning and end portions remain the same lengths, this should work for any combination in the middle:

x = "1A23abcdefg4A321"
? mid(x, 5, len(x)-9)
abcdefg
 
 
x = "1A23ab4A321"
? mid(x, 5, len(x)-9)
ab

HTH - Bob

Hey, this works perfect! Awesome, thank you!!!

 

[raskew]

Re: "In Between" function that behaves similarly to a "Mid String" function

You're most welcome.

Best Wishes - Bob