Parsing Middle of String

[OprEowyn]

I am pulling data from an external database and have linked the table to my Access database. In that table, there is a field "Description" that has several pieces of information that I need:
AAAAAA-######-AA-AAAAAA
Name-CustID-State-City

The Name, CustID, and City all have variable lengths.

I've parsed out the Name successfully in a query:

Left([Description],InStr([Description],"-")-1)

Here is the code I'm using to parse the CustID in a query:

Mid([Description],InStr(1,[Description],"-")+1,InStr(InStr(1,[Description],"-")+1,[Description],"-")-1)

This code yields:
######-AA-AA

The string after the CustID is equal in length to the corresponding Name for that field.

I can't see where I'm going wrong with my code - could you help?

The current work-around I have is to then further parse the ######-AA-AA result I get, but then when I try to run a summary query and count records by CustID, I get the following error:


ODBC--Call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the SUBSTRING function. (#536)

Thank you!

[recyan]

Till some one comes along, just check out :

Mid([Description],InStr(1,[Description],"-") + 1,InStr(InStr(1,[Description],"-")+1,[Description],"-")-InStr(1,[Description],"-")-1)

Thanks

[OprEowyn]

That solved the initial problem - my parsed string is now just the CustID. Thank you!

When I run the summary query on the CustID, I'm still getting this error:
ODBC--Call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the SUBSTRING function. (#536)

When I do a detail query on the same field, I don't get the error. Any suggestions? My ultimate goal is to have a count of billing items by CustID.

[OprEowyn]

I determined that the error was happening because my data was inconsistant. Sometimes it was AAAAAAAA-######-AA-AAAAAA, sometimes it was AAAAAAAA-######. Here is the final code I wrote that worked both to display the CustID and still worked in the summary query:

IIf([Description] Like "*-*-*",Mid([Description],InStr(1,[Description],"-")+1,InStr(InStr(1,[Description],"-")+1,[Description],"-")-InStr(1,[Description],"-")-1),IIf([Description] Like "*-*",Right([Description],InStr(1,[Description],"-")),"CHECK DATA"))

Thank you, recyan, for your help!

[recyan]

Glad you got things working.

Thanks