Update part of a field / Access 2003

[Abay]

Hello .. I have a field in a table called "Title" which holds the title of a
book or cd, which at the end of the title has the format of the title in
round brackets, followed by the release date of the title in round brackets.

e.g. The Snowball (CD) (September 2008)

I need to replace the release date with spaces i.e. blank it out. The
release date is always enclosed in round brackets and it always appears
after the title and format.

My guess is that I can use the Len function to determine the position of
last character in the field. I need to ensure that this is a closing ")",
then to count or get back somehow to the last opening "(", and update the
opening "(" and the space between it and the closing bracket, plus the
closing bracket to spaces.

Any help on how to do this would be most appreciated.

Abay

 

[Scott Lichtenberg]

Abay

Getting rid of the dates is easy. I think your big issue is how to
determine whether you really want to do it. Based on your question, it
sounds as if it is possible to have an entry like - The Snowball (CD) -
as well as the one in your example. What happens when you find that the
last character is a closing parenthesis? Do you want to delete the format?
Here are some scenarios:

1. You want to delete everything after the first opening parenthesis.

Dim intPosition as Integer
intPosition = InStr(1, strMyTitle, "(" )
If intPosition > 1 Then 'Parenthesis found
strMyTitle = Left(strMyTitle, intPosition - 1) 'New title
ends just before parenthesis
End If


2. You know that there will always be a format in parentheses. In this
case, you want to delete anything after the first closing parenthesis. Copy
the previous example, replacing the opening parenthesis with a closing
parenthesis in the InStr function. This will find the first ")".
Everything after it can be deleted with the Left function.

Dim intPosition as Integer
intPosition = InStr(1, strMyTitle, ")" )
If intPosition > 1 Then 'Parenthesis found
strMyTitle = Left(strMyTitle, intPosition ) ' *** New
title ends at parenthesis (don't use -1)
End If

One final issue - what happens when your title has parentheses?

Hope this helps.

 

[Abay]

Many thanks for your reply Scott and apologies as I inadvertently misled you
re: the format. There will always be a date present in round brackets, it
being always the last set of brackets. Sometimes there will be a format
identification within brackets e.g. (CD), it being the first set of
brackets. So I think I have to work back from the last ")" ...

 

[John Spencer]

Take a look at InstrRev

Left([Your Field],InstrRev([Your field],"(")-1)

That will error is the field does not have a "(" in it

IIF([YourField Like "*(*",Left([YourField], InstrRev([Yourfield],"(")-1),
[YourField])

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

 

[Abay]

Many thanks John .. I will check this out .. Abay

Take a look at InstrRev

Left([Your Field],InstrRev([Your field],"(")-1)

That will error is the field does not have a "(" in it

IIF([YourField Like "*(*",Left([YourField], InstrRev([Yourfield],"(")-1),
[YourField])

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

Many thanks for your reply Scott and apologies as I inadvertently misled
you re: the format. There will always be a date present in round
brackets, it being always the last set of brackets. Sometimes there will
be a format identification within brackets e.g. (CD), it being the first
set of brackets. So I think I have to work back from the last ")" ...