Uppercase last line of an address field ?

[AussieLad]

Our Creditors system allows for free-format entry of data into our "Address" field... eg,
8 Hart St
Newtown

What I am trying to do is to create a formula to display the last line of each Address as Uppercase, (as our data has a mixture of Uppercase and Propercase).
ie, in short, everything after the last Carriage Return of each field to be Uppercase. In the example above, I want...
8 Hart St
NEWTOWN

I know it's probably simple, but help !!

 

[Ngolem]

Yep it is simple

uppercase({Table.lastline})

Jim Broadbent

 

[AussieLad]

Thanks, but wouldn't this make the whole field uppercase ?
If this is the only solution, I'll have to advise my management of same... but their spec was for only the last line to be "upped", eg "NEWTOWN", and the "8 Hart Street" to remain Proper Case.
(Note that there could potentially be more than two lines in the address, so I'm thinking we'd want to somehow convert the data AFTER the final Carriage Return as Uppercase).
Thanks again !

 

[dgillz]

So is your date this:
8 Hart St
Newtown

or this:
8 Hart St Newtown

Please advise.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Madawc]

If your address data includes entries like "8 Hart St Newtown", as ours dones, then I figure you'll need to read the address into a table, as separate words. Then feed it back, with the last table entry turned to upper case.

I can't advise in detail , since I've not so far tried the Crystal table functions.

You'll also run into problems when the town name is more than a single name, e.g. Alice SPRINGS.

How you'd get round that, I have no idea.

Madawc Williams
East Anglia
Great Britain

 

[Madawc]

P.S. you might have to manually adjust the database, turning names like Alice Springs into Alice_Springs, and then stripping out the underscore at the same time as you turn it into uppercase

Madawc Williams
East Anglia
Great Britain

 

[Naith]

If your entire address is contained in one field, there are a few ways you can do this.

This is one:

WhilePrintingRecords;

StringVar Address := {YourAddress};
StringVar LastLine := StrReverse((Mid(StrReverse(Address),1,InStr(StrReverse(Address),chr(13)))));

Mid(Address,1,Length(Address)-Length(LastLine)) + UCase(LastLine);

You could also use a loop to cycle through the letters after the last Chr(13) and capitalise each one, if you didn't want to use the StrReverse approach.

Naith

 

[Ngolem]

you didn't say the address was complete in one field.

Show us how the address is saved...BTW you should have done that from the start.

Jim Broadbent

 

[AussieLad]

Thanks to all who responded - "Naith" has given me my solution with his "WhilePrintingRecords..." formula !

Sorry if my initial query was ambiguous, but for those who are interested, the address is in one field as per my original example, ie...
8 Hart St <CR>
Newtown

Appreciate the expert tips, (all the way down here in sunny Queensland, Australia)

 

[AussieLad]

I thought I'd brought closure to my query, but not so just yet.

Naith's &quot;WhilePrintingRecords...&quot; formula works fine when my test range of records has data in the address field.
However, (and don't ask me why), there are instances where the address field is blank/null.
When I run the report in these cases, I get a dialog box appearing as follows...
&quot;String length is less than 0 or not an integer&quot;... and no results display for any records, (data or not).
I've tried playing with &quot;If(IsNull({field})) then &quot;&quot; else {field}&quot;, but am not winning.
Any further suggestions would be welcomed.

 

[Naith]

The formula does assume there's always data there. This is the same approach with null assumptions:

WhilePrintingRecords;
StringVar NewAddress;
StringVar Address := {YourAddress};
StringVar LastLine := '';
BooleanVar NullCheck := False;

If IsNull({YourAddress})
Then NullCheck := True;

If NullCheck = False
Then
LastLine := StrReverse((Mid(StrReverse(Address),1,InStr(StrReverse(Address),chr(13)))))
Else LastLine := '';

If NullCheck = False
Then
NewAddress := Mid(Address,1,Length(Address)-Length(LastLine)) + UCase(LastLine)
Else NewAddress := '';

NewAddress;

Naith

 

[Ngolem]

Naith - I am not sure if the formula will choke when it encounters a null in the assignment of Address if {YourAddress} is null...but this assignment could be combined with the checking of NULLS

WhilePrintingRecords;
StringVar NewAddress;
StringVar Address;
StringVar LastLine := '';
BooleanVar NullCheck ;

If IsNull({YourAddress}) Then
NullCheck := True;
else
(
Address := {YourAddress};
NullCheck := False;
);

Then you are sure not to have a NULL problem

Jim Broadbent

 

[Ngolem]

Arrggh

Take the Semi colin out of this part...sorry

WhilePrintingRecords;
StringVar NewAddress;
StringVar Address;
StringVar LastLine := '';
BooleanVar NullCheck ;

If IsNull({YourAddress}) Then
NullCheck := True //removed semicolin
else
(
Address := {YourAddress};
NullCheck := False;
);





Jim Broadbent

 

[AussieLad]

Thanks, gurus... you've been a great help !

 

[AussieLad]

Me again... sorry to be a pain, but I've struck another scenario with my data.

There are instances where there is only one line in the address field, and therefore no Chr(13)'s. (Don't ask me why this is so... I'm not in the Finance Dep't)

The latest incarnation of the formula now handles Nulls OK, but seems to tripping over these one-liners.

(I'm sure a fix for this should then cover all scenarios)

Thanks !

 

[dgillz]

Test for the existence of the chr(13) with an if-then statement:

If instr(Chr(13), {YourAddressField})>0 then <<one set of logic>> else <<a different set of logic>>

Integrate all this into the previous posts and you should be fine.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Naith]

What's the rule you should apply when there is only one line?

If there are always commas seperating each address component, you could capitalise everything after the last comma. However, if commas are not guaranteed or reliable, I imagine you're thinking about capitalising the last word. This isn't going to be such an attractive solution for people living in places like New York:

Hugh Janus, 44 Broadway New YORK.

See what I mean?

Naith

 

[Madawc]

Problems names like &quot;New YORK&quot; or &quot;Alice SPRINGS&quot; depend on human knowledge. If the data has no formal indication of which words belong together, it cannot possibly be solved by any algorithmic method, no formula that a computer can be instructed to use.

An address like &quot;5 Mile Hop Canyon Turnaround Maybridge&quot; could mean several different things, each of them equally valid. AussieLad needs to convince his employers that the data needs a certain amount of 'cleaning' to deal with such cases.

Madawc Williams
East Anglia
Great Britain

 

[Madawc]

Better, &quot;Duncoding 5 Mile Hop Canyon Turnaround Maybridge&quot;,
it could be
Duncoding 5 Mile Hop
Canyon Turnaround
Maybridge
or
Duncoding
5 Mile Hop Canyon
Turnaround Maybridge
etc.

Madawc Williams
East Anglia
Great Britain

 

[Madawc]

An old book about computers claims that an early system broke down when someone gave their address as &quot;A few miles up Turkey Creek&quot;

Madawc Williams
East Anglia
Great Britain