Manipulating Field to show data differently that entered

  • Thread starter Thread starter floyd33 via AccessMonster.com
  • Start date Start date
F

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!
 
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 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!
Click to expand...

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

Use this column to search on.
 
floyd33 said:
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!
Click to expand...

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

fredg said:
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!
Click to expand...

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

Use this column to search on.
Click to expand...
 
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.
 
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
Click to expand...
[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!
Click to expand...
Click to expand...
 
Back
Top