Splitting up a field for an update query

[Jeff]

I received a database that has a single field which has two pieces of
information in it -- type of writing(publisher) so it could be Book
(Morrow and Co) or Article(Reader's Digest), etc.

Is there a way to pull out the text inside the parentheses and make
that its own field, so I would have one field being type of writing
and a second field being just the publisher? I'm guessing it's an
update query, but don't know how to proceed.

Thanks

Jeff

 

[Stefan Hoffmann]

hi Jeff,

Is there a way to pull out the text inside the parentheses and make
that its own field, so I would have one field being type of writing
and a second field being just the publisher? I'm guessing it's an
update query, but don't know how to proceed.

You can use Split() to extract the values, e.g.

Left(Split([evilField],"(")(1), Len(Split([evilField],"(")(1))-1)


As an update statement, e.g.

UPDATE yourTable
SET
Writing = Split([evilField], "(")(0)),
Publisher = Left(
Split([evilField],"(")(1),
Len(Split([evilField],"(")(1))-1)
WHERE [evilField] LIKE "*(*)"


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Stefan,

UPDATE yourTable
SET
Writing = Split([evilField], "(")(0)),

The closing parenthesis at the end is not necessary and must be removed
in order to avoid a syntax error.


mfG
--> stefan <--

 

[John Spencer MVP]

I doubt that this will work because the VBA split function returns an array of
values. You would need to use a custom VBA function to return one of the
values. If you don't want to use a custom vba function you could use the
following expressions

PublicationType= LEFT([YourField],Instr(1,[YourField],"(")-1)

and

Publisher =
LEFT(MID([YourField],Instr(1,[YourField],"(")+1),Len(MID([YourField],Instr(1,[YourField],"(")+1))-1)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

hi Stefan,

UPDATE yourTable
SET
Writing = Split([evilField], "(")(0)),

The closing parenthesis at the end is not necessary and must be removed
in order to avoid a syntax error.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi John,

I doubt that this will work because the VBA split function returns an
array of values.

It will

The corrected Split() usage:

Writing = Split([evilField], "(")(0)
Publisher = Left(
Split([evilField], "(")(1),
Len(Split([evilField], "(")(1)) - 1)

The (0) after the Split() call accesses the correct value in the array.


mfG
--> stefan <--

 

[John Spencer]

Ok, every time that I have tried to use that in a query it has failed.

I just tried your code in a query and in design view I get an error and
cannot exit the "cell" I am using to enter the expression.

In SQL view, I can enter the expression, BUT if I try to run the query I
get an invalid use error.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

hi John,

I doubt that this will work because the VBA split function returns an
array of values.

It will

The corrected Split() usage:

Writing = Split([evilField], "(")(0)
Publisher = Left(
Split([evilField], "(")(1),
Len(Split([evilField], "(")(1)) - 1)

The (0) after the Split() call accesses the correct value in the array.


mfG
--> stefan <--

 

[Jeff]

Thanks -- it worked for me, and now I have the 2 fields.

Jeff