Need help extracting data from a field.

  • Thread starter Thread starter TD
  • Start date Start date
T

TD

I have a field in my database that has records with dashes "-" in the field.
Example:
123-GP-0124444
1234-GP-0123

What I need to be able to accomplish is to have a field on my form that only
show the first two groups of numbers/letters, that is the first two out of
three groups that are seperated with the dash.
The problem is that the records are not consistant in lenght, for example
the first group of numbers before the first dash may have 3 digits or they
may have 4 digits.

Could anyone please guide me in the right direction?

TIA
TD
 
TD said:
I have a field in my database that has records with dashes "-" in the
field. Example:
123-GP-0124444
1234-GP-0123

What I need to be able to accomplish is to have a field on my form
that only show the first two groups of numbers/letters, that is the
first two out of three groups that are seperated with the dash.
The problem is that the records are not consistant in lenght, for
example the first group of numbers before the first dash may have 3
digits or they may have 4 digits.

Could anyone please guide me in the right direction?
Click to expand...

If you can rely on the field to contain both dashes, you can use a
controlsource expression like

=Left([TheField], InStrRev([TheField], "-") - 1)
 
Thanks for the quick feed back. This would work, but is there anyway to
extract this info without the dashes? Sorry, I didn't mention this in my
original post.

Thanks again,
TD


Dirk Goldgar said:
TD said:
I have a field in my database that has records with dashes "-" in the
field. Example:
123-GP-0124444
1234-GP-0123

What I need to be able to accomplish is to have a field on my form
that only show the first two groups of numbers/letters, that is the
first two out of three groups that are seperated with the dash.
The problem is that the records are not consistant in lenght, for
example the first group of numbers before the first dash may have 3
digits or they may have 4 digits.

Could anyone please guide me in the right direction?
Click to expand...

If you can rely on the field to contain both dashes, you can use a
controlsource expression like

=Left([TheField], InStrRev([TheField], "-") - 1)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
T D said:
Thanks for the quick feed back. This would work, but is there anyway
to extract this info without the dashes? Sorry, I didn't mention
this in my original post.
Click to expand...

I wish you had mentioned it, because my first idea on how to do it
returned just that. Try this:

=Split([TheField], "-")(0) & Split([TheField], "-")(1)

That's not very efficient, perhaps, but it's quite simple.
 
Thank you again for all your help. This is great. So glad I found this
newsgroup.
TD

Dirk Goldgar said:
T D said:
Thanks for the quick feed back. This would work, but is there anyway
to extract this info without the dashes? Sorry, I didn't mention
this in my original post.
Click to expand...

I wish you had mentioned it, because my first idea on how to do it
returned just that. Try this:

=Split([TheField], "-")(0) & Split([TheField], "-")(1)

That's not very efficient, perhaps, but it's quite simple.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top