Parsing field data without delimiters

[shennin202]

Hi,

I fear this question may have already been answered under a thread titled "Parsing field into two fields', but here goes...



I have a similar situation, my data actually comes from a .csv spreadsheet exported from a routing program, looks like this:

BoxNumber
2R;34R
286
186W
230;237;411CCR
271WR

I can get it parsed out to individual box numbers, so:

BoxNumber
2R
34R
286
186W
230
237
411CCR
271WR

Next steps are to a) parse out the letter suffix (I've been using a Temp table with BoxNumber and Suffix fields); b) add leading zeros for sorting (e.g. 0002, 0034, etc); and c) re-concatenate the suffix, to wind up with something like this:

BoxNumber
0002R
0034R
0286R
0186W
0230
0237
0411CCR
0271WR

Some of the bumps in the road are: The box numbers can be 1-3 digits, with a suffix of 0-3 alpha characters, so; 2R, 18TW, 411CCR, etc.

The hitch is, I'm creating this database in Access 2013 to be deployed on a Sharepoint server, so vba isn't an option. I've been banging my head trying to figure out how to write the query sql to parse out the alpha suffixes using an instr() function. Is there some kind of wildcard option that can be used with an instr() that will find the end of the digits/beginning of alpha characters? Here's a few samples of the statements I've tried, with no success so far:

UPDATE tblSSNumPad SET tblSSNumPad.StopJobNumber = Left([StopJobNumber],InStr([StopJobNumber],"*[0-9]")), tblSSNumPad.Suffix = Right([StopJobNumber],InStr([StopJobNumber],"*[a-z]"));

UPDATE tblSSNumPad SET tblSSNumPad.StopJobNumber = Left([SStopJobNumber,InStr([SStopJobNumber,"*[!0-9]")-1), tblSSNumPad.Suffix = Right([SStopJobNumber,InStr([SStopJobNumber,"*[!0-9]"));

UPDATE tblSSNumPad SET tblSSNumPad.StopJobNumber = Left([StopJobNumber],InStr([StopJobNumber],"Like*[!0-9]")-1), tblSSNumPad.Suffix = Right([StopJobNumber],InStr([StopJobNumber],"Like*[!0-9]"));

UPDATE tblSSNumPad SET tblSSNumPad.StopJobNumber = Left([StopJobNumber],InStr([StopJobNumber],"*[#]")), tblSSNumPad.Suffix = Right([StopJobNumber],InStr([StopJobNumber],"*[#]+1"));

I must have tried 20+ combinations, can anyone help?

Thanks in advance,

Steve

[June7]

Try:

x represents the field

Format(Val(x),"0000") & Mid(x,Len(CStr(Val(x)))+1)

Why not have these in two fields?

[shennin202]

Thanks for the quick reply, June, I'll try that code out here in a minute...

To try and make a long story short, I work for a company that collects and recycles used clothing, shoes, and miscellaneous household items. The Box Numbers represent the donation boxes we collect from. In our market here in Phoenix, we have 10 routes with between 22-44 boxes per route, running each route 3 times a week, so a little over 400+ locations and boxes. We are currently tracking collected weights in Excel spreadsheets, and have something like 10 different spreadsheets in use for the various reports/records that we track. My boss is a District manager over 10 different markets, and wants to dump it all into Access 2013 and use it on Sharepoint so that he can have the admins at the different markets entering the weight data only once, and being able to spit out the reports and info he needs wherever he might be, whether here in Phoenix, or Southern Cali, Detroit, etc. Unfortunately, I'm just a novice at designing a databse, so i'm struggling a bit with some of this, as you might imagine!

The reason for not having in two fields: As stated in the original post, the data is coming from a .csv file generated by a program called Street Sync, which plots and optimizes routes geographically, and has only a limited number of fields available to use for all the different data we track on each location, so locations with multiple boxes have them listed in one field. The suffixes are codes to denote certain info on the box, such as whether or not it's been refurbished, color, charity represented, etc. The idea is to be able to plot a route in Street Sync, import it into Access to populate all the pertinent location and box information, have Access organize it to record data about collections, and be able to pass route and location info back out of Access to Street Sync as needed to modify the routes. I guess from my point of view, it wouldn't really make any difference to store the suffixes in a separate field in Access, then simply concat when exporting back to Street Sync. The issue remains: how to parse the suffix without a delimiter, and without using vba?

[shennin202]

Hey! That WORKED! THANK YOU!!!!!

Whoops, mighta spoke too soon....

Just looked again at results, and some numbers have extra digits added, e.g. 011R became 00111R, 008R is 00808R, 015R is 00155R, 044R became 00444R. At first glance, it looked ok, but about 1/3 of the numbers (9 of 26 in my sample set) have extra digits added. Any other suggestions?

[June7]

Having in two fields in your database could simplify some filtering and sorting for report output. As you recognized, concatenating is easy to do, separating can be hard. Since you have to do the split to reconstruct the values, could just save them as separate values and will always be available.

My simple testing worked for me. I would have to analyse your calc and query if you want to provide. You might have used a formula I first posted then corrected shortly afterward.

[shennin202]

ATRS_TEST3.accdb
Hope I did this right, my sample table and query are attached, i copied and pasted the code you provided above and just copy-pasted my table name in brackets into the code at the x's.

[June7]

The problem is with values that have leading filler zeros. That was not in posted sample and I did not think to test for. Val() drops leading zeros but they are still needed to determine the length of the 'number' part. Makes this a bit more complicated. This version doesn't matter if there are leading zeros.

Format(Val(x),"0000") & Mid(x,InStr(x,Val(x))+Len(CStr(Val(x))))

[shennin202]

Ah, sorry bout that, the OP only had a small sample of my sample data, so no leading zeros... And that worked beautifully, thanks again for the very quick response! Like I said, no wonder you're called SUPER Moderator!