POSTCODE selection and linkage

[MartUK]

I am using CR XI. I have postcodes in table in following format :

XXX YYYY
XXXX YYYY etc There is no fixed numbers of characters but there is a space. What I need to do is extract data before the space and as you can see the space can be at a different position

Can I then link the extracted information to a table? This table contains a list of XXX and the person resonsible for that general area?

 

[dgillz]

This hould work:

left({YourField},instr({YourField}," ")-1)

The key in understanding the instr() function. Instrrev() is also handy at times.

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

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[MartUK]

Thanks this works fine, think I used this about 3 yrs ago!

The 2nd part of my question - can I now link the result of this formula to a table to look up the person resonsible for this general postcode area

eg for postcodes abc 1234, abc 1244 etc

result lookup table

code code name

abc abc fred bloggs

 

[Madawc]

If you create a Formula Field using dgillz's code, you should be able to group using the Formula Field. Show 'person resonsible' at group level. You can then get the 'person resonsible' using a subreport in the group header, passing the value from the group to the subreport.

There may also be some better way using 'Join', not something I can advise on.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[MartUK]

YES I thought about subreporting - not great but if needs must I will go down this route.

I did a search and noticed thread 767-1046832 whereby it appears you can do this using the from clause? I may thereore try this approach first

Any other ideas out there??? I would think that this may be a common general requirement actually for lots of reporting issues dont you think?

cheers

 

[lbass]

Are you always using the first three digits? Yes, you can use this in a command where your from clause looks like this:

From table1 inner join table2 on
left(table1.zip,3) = table2.zip

The punctuation and use of left() or {fn LEFT()} depends upon your datasource/connectivity. Note that you might want to use a left outer join if table2.zip might be null.

-LB

 

[MartUK]

no unfortunateley not always the first three digits. A UK postcode is a mess it can be XXX, It can be XX YYYY, XXX YYYY, XXXX YYYY. I need to link on the XX first part and sometimes there is no YY second part

 

[lbass]

What kind of database are your reporting on? There may be a function comparable to instr that you can use in your join.

-LB

 

[MartUK]

I beleive it is Oracle plus Access. The Oracle database contains the postcode but nowhere to assign postcode districts to business hierachy. So I create the hierarchy in Access by assigning Engineer ID to a postal district (If I know engineer ID rest of hierarchy is easy). So at the moment my crystal report etracts the postal district from the psotcode. I then export the results to Excel, and upload to Access. From there I do the crystal report ! A mess but these are all the tools I have!! I would rather do without the access upload and hence link the postal district formula result to the access lookup table you see.
Incidently my postcode district formula is now:

global stringvar postcode:="";
local numbervar n:=1;

do
(
postcode:= postcode + {FSLOCA.FSLOCA_POSTCODE}[n];
n:=n+1

while {FSLOCA.FSLOCA_POSTCODE}[n] <> " " or n = length({FSLOCA.FSLOCA_POSTCODE});

trim(postcode);

This works fine and covers all the messey eventualities (I also supress null postcodes as this oracle database allows no entry of a postcode!!)

Any ideas welcome