Please Help.

[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

 

[Rick Brandt]

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.

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.