Disecting a field in a query

[Cleetus]

Is there a way to pull the number (for this example...could be letters) out in a query? The field example is: XX, 11111-01, xxxxxxxx. The commas are not always 3 characters in, but I am looking to pull out whatever is in-between the commas. Any help is appreciated.

[pbaldy]

You can use the Mid() function along with the InStr() function to find the position of the comma, and perhaps the InStrRev() function to find the second comma, if it's also the last.

[boblarson]

So what would you want in your example? 11111-01 ?

But the commas will always be there?

If so, then this should work:

NewFieldName:Mid([OriginalFieldName], Instr(1,[OriginalFieldName],",") + 1), InstrRev([OriginalFieldName], ",")-1)

[boblarson]

oops, I took too long responding

[boblarson]

Needed to edit it anyway:

MyNewField:Mid([OriginalFieldNameHere], InStr(1, [OriginalFieldNameHere], ",") + 1, InStrRev([OriginalFieldNameHere], ",") - InStr(1, [OriginalFieldNameHere], ",") - 1)

[Cleetus]

Thanks for the help. Sorry I havent gotten a chance to try this until now. But I get an invalid procedure call message. Would this have to do with versions or some kind of add-ins?

[boblarson]

Thanks for the help. Sorry I havent gotten a chance to try this until now. But I get an invalid procedure call message. Would this have to do with versions or some kind of add-ins?

I tested it so I know it works. How have you used it? Did you change the OriginalFieldNameHere parts to the name of your actual original field? Also, do you have nulls in your field?

[Cleetus]

Yes I did replace OriginalFieldNameHere with Description. But I do have Nulls, how does that affect it? Should I add an If statement to the front checking for nulls?

[boblarson]

Yes, you would need to not try doing it when nulls exist. So changing to this:

NewFieldName:IIf(IsNull([Description]),Null,Mid([OriginalFieldName], Instr(1,[OriginalFieldName],",") + 1), InstrRev([OriginalFieldName], ",")-1))

[DerFarm]

Cletus,

It sounds as though you probably do more of this type of thing than most.

I have a set of Access VB Code string functions that will do it.

In your specific case the command would be

result=Retrieve(targetstring,delimiter,whichfield) ===>

targetstring=XX, 11111-01, xxxxxxxx
delimiter=","
whichfield=2
result=" 11111-01"

If you want it, I can post the code file here.

[Cleetus]

Bob, I tried to modify as there wasn't equal parenthisis. It's telling me sytax error (comma). item ref: IIf(IsNull([Description]),Null,(Mid([Description],InStr(1,[Description],",")+1),InStrRev([Description],",")-1))

DerFarm - thanks, but I don't know very much about VB in Access, only Excel. I would like to learn more one day.

[Cleetus]

OK, got it solved, but now I can see my data is not clean enough to be disected. Thanks for all the help.