Log in

View Full Version : Extract part of string access?


HaugeReidar
Aug 5, 2011, 04:49 AM
Hi!
I have a large database, where I need to count different items how many times they have been run over the last few years. I've got pretty much everything figured out, except this:
The database is quite stupid, in one column there is a lot of info (timestamp, explanation, the thing I need, and somethimes something after that as well, with spaces between them (not allways the same amount either). I'm reading about exctrating part of string. But don't quite get the hang of it. I have 3 characters, both letters and numbers, that I need to separate from the column into a new one hopefully. The 3 characters change, but some are the same, I want to count them to see how many it is of each.
Hope any of this makes sense,
Thanks!

ScottGem
Aug 5, 2011, 09:27 AM
It is not good design to include more than one piece of information in a single field. For example, I generally use 5 fields for people names; Salutation (Mr, Ms, etc.), First, Middle, Last and Suffix (Jr, SR, etc.). It is a lot easier to piece separate fields together then break them apart.

So I would look to redesign this database.

Extracting a string from another string can be a bear unless there is a distinct pattern. You need to identify where the 3 characters fall within the string. If they always fall in the same place (i.e. the 21-23 characters) then you can use the MID function:

MID([fieldname],21,3)

If there is another character that identifies where the 3 characters you want start, then you can use the InStr function to find that character and use it in place of the 21 above:

MID([fieldname],INSTR(1,[fieldname],"char"),3)

HaugeReidar
Aug 8, 2011, 12:03 AM
Hi Scott!
I know it's not a good design. I'm not the one who made it, but I have to work with it!
Thanks to you, I figured it out, and got what I wanted!
Thank you for your help! :D