Want to "un-concatenate" field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a linked table that pulls data from our accounting database. In the
table are account numbers formated like 999-99999-99999, is there a command
that I can put in a query to split this into 3 seperate fields?

Thx!
 
NewField1: Left ([OldFieldName],3)

NewField2: Mid([OldFieldName],5,5)

NewField3: Right([OldFieldName],5)
 
Dear JJ:

I'll assume you want to split it at the hyphens. Use the MID function to do
this. If the hyphens are always in the same postition, then you could do
this with fixed values (1,3) (4,5) (11,5). Otherwise, you may need to
search the string for the hyphens and use those values to index the MID
functions.

Please let me know if you need any further assistance with this.

Tom Ellison
 
I did something very stupid when entering the data into my database. I
concatenated all of my address fields. so it reads as:

23 wheatfield way, warboys, huntingdon, cambridge, pe14 1dj

I think that I need the mid fucntion that you described, because obviously
the parts inbetween the commas vairy in character length.

Would appreciate if you could give me any general background or help. (Can't
give you the exact field names etc, as the database is not at my home)

Hope you can help

Dan
 
Dear Daniel:

Does every instance of this field currently in the database have all 4
commas? The first thing I'd do is to write a query to verify this fact,
showing all the rows that do not have exactly 4 commas. But, bear in mind,
even though you may find some consistency now, there is no guarantee of
consistency in the future when some user puts in more rows, unless you
somehow enforce the necessary rules.

In my (somewhat perverse) opinion, putting all this in one column of data
may or may not be a mistake. The way I have done in the past is to have
users enter an address just the way they want it to look when it is printed
on a label, with carriage returns (new lines), not the commas you have
shown. I then have a set of functions written that parse the address into
its components, and I display those components in controls on the screen
next to the address. I also publish the rules by which this parsing is to
be done. The parsing functions are then guaranteed to always parse the
whole address the same way, so there are no surprises. There are some
advantages to this, but I won't go into that now.

I mention this because you are stuck with the data you have. You will
probably need some parsing functions, whose particular rules will be
determined by the data you have. You will need this whether you choose to
leave the data as recorded, or whether you use parsing to produce values for
separate columns for the address components.

I recommend you not jump to any conclusions until you have used some
"scratch queries" to investigate just how much trouble you have to overcome.

I do have a function that will parse a string by any given character (in
your case, a comma) and return the Nth instance of that. For your sample,
this function, called 5 times, would give:

N Result
1 23 wheatfield way
2 warboys
3 huntingdon
4 cambridge
5 pe14 1dj
6 <null>

Let me know if that the thing for you, although writing it is a pretty good
exercise, so you should at least try it.

What do you think?

Tom Ellison
 
Dan

Look at the Split() function too, depending on which version of Access you
are using.

Jeff Boyce
<Office/Access MVP>
 
Back
Top