Manipulating Field to show data differently that entered

[floyd33 via AccessMonster.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 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!

 

[Steve]

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
(e-mail address removed)

 

[fredg]

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.

 

[Rick Brandt]

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])

 

[John Spencer]

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]



--
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
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.

 

[Guest]

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.

 

[floyd33 via AccessMonster.com]

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:
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
(e-mail address removed)

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!