parsing data from text field

  • Thread starter Thread starter accessuser1308
  • Start date Start date
A

accessuser1308

Hello,

I have a text field that contains data in the format text-#text (ex
something-something else). I would like to make a query that will have this
text field, then 2 other fields. The first will contain all text before the
"-" and the second will contain all text after the "-". Any help is greatly
appreciated.

Thank you
 
Depending on which version of Access you are using ...

Have you looked at the Split() function?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
BeforeDash: Left([YourField],InStr([YourField],"-")-1)

AfterDash: Trim(Mid([YourField],InStr([YourField],"-")+1))
 
First, bad idea to store this data in three different fields, better to store
it in two.

To get the data from the one field into the other two, you will need to
write an update query that looks something like:

UPDATE yourTable
Set [Field2] = Left([Field1], instr([Field1], "-") - 1),
[Field3] = Mid([Field1], instr([Field1], "-") + 1)
WHERE instr([Field1], "-") > 0

Once you get it into this format, I strongly urge you to modify your
application so that it uses the two separate fields. If you need to present
it in the format:

Some text - Some other text

then do so by concatenating it in a query.
 
There are a number of way to get the desired results, below is one possible
option

SELECT Left([FieldName],InStr([FieldName],"-")-1) AS FirstPart,
Right([FieldName],Len([FieldName])-InStr([FieldName],"-")) AS SecondPart
FROM TableName;

OR another slight variation could be

SELECT Mid([FieldName],1,InStr([FieldName],"-")-1) AS FirstPart,
Mid([FieldName],InStr([FieldName],"-")+1) AS SecondPart
FROM TableName;

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
accessuser1308 said:
I have a text field that contains data in the format text-#text (ex
something-something else). I would like to make a query that will have this
text field, then 2 other fields. The first will contain all text before the
"-" and the second will contain all text after the "-". Any help is greatly
appreciated.
Click to expand...

David Fenton recently showed us how to use the VBA Split() function in a
query. I think this query may do what you want once you substitute your
field and table names.

SELECT
YourTextField
, Split(YourTextField,"-")(0) AS before_dash
, Split(YourTextField,"-")(1) AS after_dash
FROM
YourTable;

I assumed only a single dash in YourTextField. If there are more than
one, you need to figure out what you want returned in your calculated
fields.
 
TRY THAT. I think you will find that it does not work. It doesn't even get
past the SQL compile stage. I have to admit that I have not tested it in
Access 2007 or later.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John said:
TRY THAT. I think you will find that it does not work. It doesn't even
get past the SQL compile stage. I have to admit that I have not tested
it in Access 2007 or later.
Click to expand...

You're right, John. And it doesn't work in 2007, either. I built the
Split expressions with a sample string in the Immediate Window, but I
didn't check it in a query. Sorry about that.
 
Back
Top