Please Help.

  • Thread starter Thread starter JP
  • Start date Start date
J

JP

Hi All,

I have an "Itinerary" Field in my table, Data type "Text"
and with field Size 254. It contain Values like

"Melbourne, Sydney, Brisbane, Auckland, Napier, Port
Chalmers, Panama Canal, Manzanillo, Savannah,
Philadelphia, Zeebrugge, Tilbury"

I want to display first 3 letter of first word and first 3
letter of the last word. i.e for the above Itinerary I
want to display as "Mel/Til" on my report. How do I do
this. Please Advise.

Thanks,
JP
 
JP said:
Hi All,

I have an "Itinerary" Field in my table, Data type "Text"
and with field Size 254. It contain Values like

"Melbourne, Sydney, Brisbane, Auckland, Napier, Port
Chalmers, Panama Canal, Manzanillo, Savannah,
Philadelphia, Zeebrugge, Tilbury"

I want to display first 3 letter of first word and first 3
letter of the last word. i.e for the above Itinerary I
want to display as "Mel/Til" on my report. How do I do
this. Please Advise.
Click to expand...

The first part is dead-easy. Left([Itinerary], 3) & "/"

The latter will likely require a custom function to find the last occurrence of a
comma and then grab characters from there.

What would solve the problem better is if your data were properly normalized. You
should have a separate Itinerary *table* that is linked to your current one. Then
"Melbourne", "Sydney", etc., each are separate rows in the new table. With that
setup what you're trying to do becomes much easier.
 
Back
Top