Parse a field into 2 fields

[poof]

I have a field that could be split into 2, one being name and the second
being a number. Due to data quality beyond my control and which won't be
fixed I sometimes have a # sign to key on. If not I just want to return the
original text in the field.

Some examples

K & W Cafeteria - I want this to be what is used but does not have a # so
using this formula =Trim(Left([Mark For Name],InStr([Mark For Name],"#")-1))
I get #error for name (first field) and #error for number (second field)
using this formula
=Trim(Mid([Mark For Name],InStr([Mark For Name],"#"))). I have attempted
many of the suggestions in the forums but have not found the correct syntax
to work. I have tried using Nz in the formula within an if statement.

The formulas above work for this data ABC #A12345 and breaks into 2 fields.

Any suggestions would be helpful

 

[Steve Schapel]

Poof,

I am not sure whether there is a more elegant way, but this should work
for you:

=IIf(InStr([Mark For Name],"#"),Trim(Left([Mark For Name],InStr([Mark
For Name],"#")-1)),[Mark For Name])

 

[poof]

Steve

Putting that formula in I get the message: "The expression you entered
contains invalid syntax"

I tried copying and typing in as a new formula. Thoughts?
--
poof

Poof,

I am not sure whether there is a more elegant way, but this should work
for you:

=IIf(InStr([Mark For Name],"#"),Trim(Left([Mark For Name],InStr([Mark
For Name],"#")-1)),[Mark For Name])

--
Steve Schapel, Microsoft Access MVP

I have a field that could be split into 2, one being name and the second
being a number. Due to data quality beyond my control and which won't be
fixed I sometimes have a # sign to key on. If not I just want to return the
original text in the field.

Some examples

K & W Cafeteria - I want this to be what is used but does not have a # so
using this formula =Trim(Left([Mark For Name],InStr([Mark For Name],"#")-1))
I get #error for name (first field) and #error for number (second field)
using this formula
=Trim(Mid([Mark For Name],InStr([Mark For Name],"#"))). I have attempted
many of the suggestions in the forums but have not found the correct syntax
to work. I have tried using Nz in the formula within an if statement.

The formulas above work for this data ABC #A12345 and breaks into 2 fields.

Any suggestions would be helpful

 

[poof]

I resolved this, typed in wrong, apologies on the last post.

Thanks for your help.
--
poof

Steve

Putting that formula in I get the message: "The expression you entered
contains invalid syntax"

I tried copying and typing in as a new formula. Thoughts?
--
poof

Poof,

I am not sure whether there is a more elegant way, but this should work
for you:

=IIf(InStr([Mark For Name],"#"),Trim(Left([Mark For Name],InStr([Mark
For Name],"#")-1)),[Mark For Name])

--
Steve Schapel, Microsoft Access MVP

I have a field that could be split into 2, one being name and the second
being a number. Due to data quality beyond my control and which won't be
fixed I sometimes have a # sign to key on. If not I just want to return the
original text in the field.

Some examples

K & W Cafeteria - I want this to be what is used but does not have a # so
using this formula =Trim(Left([Mark For Name],InStr([Mark For Name],"#")-1))
I get #error for name (first field) and #error for number (second field)
using this formula
=Trim(Mid([Mark For Name],InStr([Mark For Name],"#"))). I have attempted
many of the suggestions in the forums but have not found the correct syntax
to work. I have tried using Nz in the formula within an if statement.

The formulas above work for this data ABC #A12345 and breaks into 2 fields.

Any suggestions would be helpful

 

[John Spencer]

How about trying these formulas?
Left([Mark For Name], Instr(1,[Mark for Name] & "#","#")-1)
Mid([Mark For Name], Instr(1,[Mark for Name] & "#","#")+1)

Concatenating a # onto the string in the INSTR Function should work for you.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================