splitting text strings into new fields

  • Thread starter Thread starter toby
  • Start date Start date
T

toby

I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's Dallas.
I tried the =right([sales office],35) thinking that there would be no sales
office larger than 35 characters. that didn't work.

please help. thanks in advance
 
I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's Dallas.
I tried the =right([sales office],35) thinking that there would be no sales
office larger than 35 characters. that didn't work.

please help. thanks in advance
Click to expand...

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
 
Toby

Take a look at Access HELP on the Mid() and InStr() functions.

You can use these to use a query to "look for" a dash ("-") and then take
everything to the right.

This should work, if EVERY ONE of the entries in this field is formatted
with the dash immediately before the Office.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
yes, that is correct. I want everything to the right of that dash. and they
all have this dash. oh except for the ones that are blank, meaning they have
no dash. what happens to them? I did use your formula below and got "invalid
procedure call". could the blanks in that field be the reason for that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!



fredg said:
I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's Dallas.
I tried the =right([sales office],35) thinking that there would be no sales
office larger than 35 characters. that didn't work.

please help. thanks in advance
Click to expand...

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
 
yes, I have found that in the help. but I cannot come up with the right
combination to make it work. I do have some blanks in that field so how do I
get around that? do you happen to know? thanks much. I appreciate it!

Jeff Boyce said:
Toby

Take a look at Access HELP on the Mid() and InStr() functions.

You can use these to use a query to "look for" a dash ("-") and then take
everything to the right.

This should work, if EVERY ONE of the entries in this field is formatted
with the dash immediately before the Office.

Regards

Jeff Boyce
Microsoft Office/Access MVP

toby said:
I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's
Dallas.
I tried the =right([sales office],35) thinking that there would be no
sales
office larger than 35 characters. that didn't work.

please help. thanks in advance
Click to expand...
Click to expand...
 
yes, that is correct. I want everything to the right of that dash. and they
all have this dash. oh except for the ones that are blank, meaning they have
no dash. what happens to them? I did use your formula below and got "invalid
procedure call". could the blanks in that field be the reason for that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!

fredg said:
I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's Dallas.
I tried the =right([sales office],35) thinking that there would be no sales
office larger than 35 characters. that didn't work.

please help. thanks in advance
Click to expand...

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
Click to expand...

So if there is no Field Office listed, what do you wish to see? The
Sales office?
Try... (all on one line)...
FieldOffice:IIf(InStr([SalesOffice],"-")>0,
Mid([SalesOffice],InStr([SalesOffice],"-")+1),[SalesOffice])

Or nothing?
FieldOffice:IIf(InStr([SalesOffice],"-")>0,
Mid([SalesOffice],InStr([SalesOffice],"-")+1),"")
 
ack! I typed "=" instead of the plus sign. no matter, I redid that same
thing in the query and now I get: data type mismatch in criteria expression.
hmmmm. any ideas? thanks again.

toby said:
yes, that is correct. I want everything to the right of that dash. and they
all have this dash. oh except for the ones that are blank, meaning they have
no dash. what happens to them? I did use your formula below and got "invalid
procedure call". could the blanks in that field be the reason for that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!



fredg said:
I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's Dallas.
I tried the =right([sales office],35) thinking that there would be no sales
office larger than 35 characters. that didn't work.

please help. thanks in advance
Click to expand...

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
Click to expand...
 
I'm not sure what you mean by field office. I have a field called sales
office. this is the one I'm trying to get a piece of instead of the entire
text.

neither of those work........it returns one line with nothing in each field.

fredg said:
yes, that is correct. I want everything to the right of that dash. and they
all have this dash. oh except for the ones that are blank, meaning they have
no dash. what happens to them? I did use your formula below and got "invalid
procedure call". could the blanks in that field be the reason for that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!

fredg said:
On Mon, 11 Feb 2008 14:53:01 -0800, toby wrote:

I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's Dallas.
I tried the =right([sales office],35) thinking that there would be no sales
office larger than 35 characters. that didn't work.

please help. thanks in advance

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
Click to expand...

So if there is no Field Office listed, what do you wish to see? The
Sales office?
Try... (all on one line)...
FieldOffice:IIf(InStr([SalesOffice],"-")>0,
Mid([SalesOffice],InStr([SalesOffice],"-")+1),[SalesOffice])

Or nothing?
FieldOffice:IIf(InStr([SalesOffice],"-")>0,
Mid([SalesOffice],InStr([SalesOffice],"-")+1),"")
Click to expand...
 
Toby,

Are you putting the expression in the field "cell" (correct) or in a
criteria "cell" (incorrect)? I would use the following expression if Sale
Office could ever be null (blank).

Field: TheOffice: Mid([sales office],InStr([sales office] & "" ,"-")+1)

Alternative expression to use
Field: TheOffice: IIF([Sales Office] is not Null, Mid([sales
office],InStr([sales office],"-")+1),Null)

Why test for null or eliminate it by appending a zero-length string: If the
field is Null, then Instr will return null which will cause Mid to generate
an error since it does not handle null as its second argument..
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

toby said:
ack! I typed "=" instead of the plus sign. no matter, I redid that same
thing in the query and now I get: data type mismatch in criteria
expression.
hmmmm. any ideas? thanks again.

toby said:
yes, that is correct. I want everything to the right of that dash. and
they
all have this dash. oh except for the ones that are blank, meaning they
have
no dash. what happens to them? I did use your formula below and got
"invalid
procedure call". could the blanks in that field be the reason for that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!



fredg said:
On Mon, 11 Feb 2008 14:53:01 -0800, toby wrote:

I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's
Dallas.
I tried the =right([sales office],35) thinking that there would be no
sales
office larger than 35 characters. that didn't work.

please help. thanks in advance

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
Click to expand...
Click to expand...
 
Toby

If that field is empty in some rows, you probably don't want to "parse" it?!

Use a query to return only those records that DO have a value, then use the
functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

toby said:
yes, I have found that in the help. but I cannot come up with the right
combination to make it work. I do have some blanks in that field so how
do I
get around that? do you happen to know? thanks much. I appreciate it!

Jeff Boyce said:
Toby

Take a look at Access HELP on the Mid() and InStr() functions.

You can use these to use a query to "look for" a dash ("-") and then take
everything to the right.

This should work, if EVERY ONE of the entries in this field is formatted
with the dash immediately before the Office.

Regards

Jeff Boyce
Microsoft Office/Access MVP

toby said:
I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's
Dallas.
I tried the =right([sales office],35) thinking that there would be no
sales
office larger than 35 characters. that didn't work.

please help. thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure what you mean by cell. I'm working with an Access query and I
am putting the formula on the criteria line in the query for the field "sales
office". I tried both of your formulas and both return one line with nothing
in every field. I don't get it.....I am wondering if the fact that there are
blanks in this field (along with the ones that look like my original sample)
is causing the trouble. I don't know......thanks for trying to help.

John Spencer said:
Toby,

Are you putting the expression in the field "cell" (correct) or in a
criteria "cell" (incorrect)? I would use the following expression if Sale
Office could ever be null (blank).

Field: TheOffice: Mid([sales office],InStr([sales office] & "" ,"-")+1)

Alternative expression to use
Field: TheOffice: IIF([Sales Office] is not Null, Mid([sales
office],InStr([sales office],"-")+1),Null)

Why test for null or eliminate it by appending a zero-length string: If the
field is Null, then Instr will return null which will cause Mid to generate
an error since it does not handle null as its second argument..
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

toby said:
ack! I typed "=" instead of the plus sign. no matter, I redid that same
thing in the query and now I get: data type mismatch in criteria
expression.
hmmmm. any ideas? thanks again.

toby said:
yes, that is correct. I want everything to the right of that dash. and
they
all have this dash. oh except for the ones that are blank, meaning they
have
no dash. what happens to them? I did use your formula below and got
"invalid
procedure call". could the blanks in that field be the reason for that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!



:

On Mon, 11 Feb 2008 14:53:01 -0800, toby wrote:

I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case it's
Dallas.
I tried the =right([sales office],35) thinking that there would be no
sales
office larger than 35 characters. that didn't work.

please help. thanks in advance

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure what you mean by cell. I'm working with an Access query and I
am putting the formula on the criteria line in the query for the field "sales
office". I tried both of your formulas and both return one line with nothing
in every field. I don't get it.....I am wondering if the fact that there are
blanks in this field (along with the ones that look like my original sample)
is causing the trouble. I don't know......thanks for trying to help.
Click to expand...

That's what John was telling you was wrong.

The expression does NOT go in the Criteria line of the query design grid.
Instead you put it into a vacant space (at the end of the list of fields, or
insert a new column using the Insert menu option) in the Fields row of the
query grid, the top row.
 
aha!! THAT works! thanks so much.

John W. Vinson said:
That's what John was telling you was wrong.

The expression does NOT go in the Criteria line of the query design grid.
Instead you put it into a vacant space (at the end of the list of fields, or
insert a new column using the Insert menu option) in the Fields row of the
query grid, the top row.
Click to expand...
 
This is NOT criteria. It is a CALCULATED field. It goes in a field.

Note that I quoted "Cell". By that I mean one of the boxes (grid cells)
that appear in the lower section of the design view.

I assume you are working in the query design view. There is a row with the
label "Field:". In one of the empty boxes in that row enter the following
TheOffice: Mid([sales office],InStr([sales office] & "" ,"-")+1)

That should return what you are asking for.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

toby said:
I'm not sure what you mean by cell. I'm working with an Access query and
I
am putting the formula on the criteria line in the query for the field
"sales
office". I tried both of your formulas and both return one line with
nothing
in every field. I don't get it.....I am wondering if the fact that there
are
blanks in this field (along with the ones that look like my original
sample)
is causing the trouble. I don't know......thanks for trying to help.

John Spencer said:
Toby,

Are you putting the expression in the field "cell" (correct) or in a
criteria "cell" (incorrect)? I would use the following expression if
Sale
Office could ever be null (blank).

Field: TheOffice: Mid([sales office],InStr([sales office] & "" ,"-")+1)

Alternative expression to use
Field: TheOffice: IIF([Sales Office] is not Null, Mid([sales
office],InStr([sales office],"-")+1),Null)

Why test for null or eliminate it by appending a zero-length string: If
the
field is Null, then Instr will return null which will cause Mid to
generate
an error since it does not handle null as its second argument..
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

toby said:
ack! I typed "=" instead of the plus sign. no matter, I redid that
same
thing in the query and now I get: data type mismatch in criteria
expression.
hmmmm. any ideas? thanks again.

:

yes, that is correct. I want everything to the right of that dash. and
they
all have this dash. oh except for the ones that are blank, meaning
they
have
no dash. what happens to them? I did use your formula below and got
"invalid
procedure call". could the blanks in that field be the reason for
that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!



:

On Mon, 11 Feb 2008 14:53:01 -0800, toby wrote:

I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case
it's
Dallas.
I tried the =right([sales office],35) thinking that there would be
no
sales
office larger than 35 characters. that didn't work.

please help. thanks in advance

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
Click to expand...
Click to expand...
 
it did, yes. once I read John Vinson's note, I understood. so, thanks very
much!!

John Spencer said:
This is NOT criteria. It is a CALCULATED field. It goes in a field.

Note that I quoted "Cell". By that I mean one of the boxes (grid cells)
that appear in the lower section of the design view.

I assume you are working in the query design view. There is a row with the
label "Field:". In one of the empty boxes in that row enter the following
TheOffice: Mid([sales office],InStr([sales office] & "" ,"-")+1)

That should return what you are asking for.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

toby said:
I'm not sure what you mean by cell. I'm working with an Access query and
I
am putting the formula on the criteria line in the query for the field
"sales
office". I tried both of your formulas and both return one line with
nothing
in every field. I don't get it.....I am wondering if the fact that there
are
blanks in this field (along with the ones that look like my original
sample)
is causing the trouble. I don't know......thanks for trying to help.

John Spencer said:
Toby,

Are you putting the expression in the field "cell" (correct) or in a
criteria "cell" (incorrect)? I would use the following expression if
Sale
Office could ever be null (blank).

Field: TheOffice: Mid([sales office],InStr([sales office] & "" ,"-")+1)

Alternative expression to use
Field: TheOffice: IIF([Sales Office] is not Null, Mid([sales
office],InStr([sales office],"-")+1),Null)

Why test for null or eliminate it by appending a zero-length string: If
the
field is Null, then Instr will return null which will cause Mid to
generate
an error since it does not handle null as its second argument..
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

ack! I typed "=" instead of the plus sign. no matter, I redid that
same
thing in the query and now I get: data type mismatch in criteria
expression.
hmmmm. any ideas? thanks again.

:

yes, that is correct. I want everything to the right of that dash. and
they
all have this dash. oh except for the ones that are blank, meaning
they
have
no dash. what happens to them? I did use your formula below and got
"invalid
procedure call". could the blanks in that field be the reason for
that?
here's what I have for the qualifier now (that returns the error just
mentioned):

=Mid([sales office],InStr([sales office],"-")=1)

thanks much!



:

On Mon, 11 Feb 2008 14:53:01 -0800, toby wrote:

I have a field that looks like this: 12/TX-Dallas

I am hoping to extract out only the sales office, in this case
it's
Dallas.
I tried the =right([sales office],35) thinking that there would be
no
sales
office larger than 35 characters. that didn't work.

please help. thanks in advance

So you wish whatever text is to the right of the hyphen?

FieldOffice:Mid([SalesOffice],InStr([SalesOffice],"-")+1)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top