Manipulating Field to show data differently that entered

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

floyd33 - 29 Jun 2007 16:29 GMT

I am working in a venue tracker database and I have a look up box in the
header, searching for a venue name in alpha order. The problem is, we have
many venues that start with "THE," so searching is not as easy if you don't
remember that "the." How do a break apart the field to show like so:
"Restaurant One, The"? Thank you for any help!

Reply to this Message

Steve - 29 Jun 2007 16:45 GMT

Create a query based on your Venue table. The first field will be VenueID.
In the second field, put the following expression:
RevisedVenueName:IIF(Left([VenueName],4) <> "The
",[VenueName],Mid([VenueName],5,Length([VenueName]) - 4) & ", The")
Set Sort ascending on RevisedVenueName.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdatasheet.com

>I am working in a venue tracker database and I have a look up box in the
> header, searching for a venue name in alpha order. The problem is, we
[quoted text clipped - 3 lines]
> remember that "the." How do a break apart the field to show like so:
> "Restaurant One, The"? Thank you for any help!

Reply to this Message

floyd33 - 29 Jun 2007 19:21 GMT

Much appreciated, Steve.

>Create a query based on your Venue table. The first field will be VenueID.
>In the second field, put the following expression:
[quoted text clipped - 12 lines]
>> remember that "the." How do a break apart the field to show like so:
>> "Restaurant One, The"? Thank you for any help!

Reply to this Message

fredg - 29 Jun 2007 16:46 GMT

> I am working in a venue tracker database and I have a look up box in the
> header, searching for a venue name in alpha order. The problem is, we have
> many venues that start with "THE," so searching is not as easy if you don't
> remember that "the." How do a break apart the field to show like so:
> "Restaurant One, The"? Thank you for any help!

SearchField:IIf(Left([FieldName],4) = "The ", Mid([FieldName],5) & ",
The", [FieldName])

Use this column to search on.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

John Spencer - 29 Jun 2007 16:54 GMT

Or if you only want to search on the name, you could use a parameter prompt
that looks like

Field: Venue
Criteria: [Enter Venue] Or "The " & [Enter Venue]

Signature

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

>> I am working in a venue tracker database and I have a look up box in the
>> header, searching for a venue name in alpha order. The problem is, we
[quoted text clipped - 8 lines]
>
> Use this column to search on.

Reply to this Message

Rick Brandt - 29 Jun 2007 16:48 GMT

> I am working in a venue tracker database and I have a look up box in
> the header, searching for a venue name in alpha order. The problem
> is, we have many venues that start with "THE," so searching is not as
> easy if you don't remember that "the." How do a break apart the
> field to show like so: "Restaurant One, The"? Thank you for any help!

In the query for your list's RowSource replace the VenueName field with an
expression like...

Venue: IIf(Left([VenueName],4)="The ", Mid([VenueName],5) & ", " & "The",
[VenueName])

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Jerry Whittle - 29 Jun 2007 16:56 GMT

Great question! I've just ignored similar situations.

Debug.Print Replace("The Restaurant One","The ","")
Restaurant One

Of course the above is a partial solution as it doesn't put the "The" at the
end. It also doesn't handle things like "A Walk In The Park" where "A" might
mess up the sort order.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Reply to this Message