Postcodes 4

[Jazztpt]

Had a search of the forum but can't find exactly what I need.

I need to filter addresses for selected postcode areas
i.e. NE8 1TH , NE8 1DF , NE8 1SA , NE8 4HG , NE8 6GH etc and
NE10 1RT , NE10 1GR etc (for NE9, NE11 areas also)

however I only want to return the areas based on the first part of the code - NE8 1 , NE9 1 , NE10 1 etc.

Part of the problem is that some of the postcodes are entered in this format NE81 or NE101 - no space.

I usually port everything to Excel and sort it out there, however I need to look at a years data and it's too much for excel - time to learn how to do it in Crystal.
Any help with this would be much appreciated.
Jazztpt

 

[satinsilhouette]

use a mid or left or subscript function

{databasefield} [2]&" "& {databasefield}[3 to 5]

 

[synapsevampire]

Of course the obvious response is you need to fix the data acquisition process.

As for selecting the proper rows, you need to post specifics.

If you only want those that start with NE, that's simple, but putting that you want to select only rows that are like a list and then saying etc. doesn't define requirements.

left({table.field},2) = "NE" solves all NE's.

Post what is being returned, and the rules for those you need.

-k

 

[Jazztpt]

Thanks for the replies - I am at home now so can't try anything.

If the code is in either of these forms:

NE10 1DF or NE101DF or NE8 1SD or NE81SD I need to get rid of the last 2 digits which would give me say NE10 1 NE101. I then need to convert these to the same format - NE10 1, NE8 1 etc

How can I do that?
Cheers
Jazztpt

 

[synapsevampire]

Well you won't be able to select on this criteria unless you coorect the data first, but you can suppress the others.

Try including basic info in the post:

Crystal version
Database/connectivity

Also your requirements seem to have changed, you spoke of filtering data, and now it's about truncating data.

-k

 

[Jazztpt]

Crystal 8.5 - sql2000 - sorry if I have confused the issue,as I say I would normally just export to excel and sort it out there. The post codes in the addresses I need to extract are in either of 2 formats - with a space or without. I need to filter records using the first part of the code and the 1st digit of the second part. In excel I have a formula that removes the space (if there is one) and then I remove the last 2 digits of the code. I need a way of doing that in Crystal.
Thanks
Jazztpt

 

[campsys]

This formula should remove all blank spaces and remove the last two characters.

//Replace {Table.PostCode} with your field.

Mid(Replace({Table.PostCode}, " ", ""), 1, Length(Replace({Table.Postcode}, " ", ""))-2)

 

[Jazztpt]

Great - I'll give that a go first thing tomorrow.
Many thanks for your help.
Jazztpt

 

[Jazztpt]

campsys - your formula worked, just what I needed but then I got an error message 'String length is less than 0 or not an integer'

I am using this to filter:
{@Postcode} in ["NE100", "NE109", "NE110", "NE119", "NE81", "NE82", "NE83", "NE84", "NE95", "NE96"]

I presume it is stalling if there is a null value in the postcode field?
Jazztpt

 

[Madawc]

Try something like

if not isnull({Table.PostCode})
and Length({Table.PostCode}) > 0

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

 

[Jazztpt]

Cheers - Thats a fantastic help.
Jazztpt

 

[Jazztpt]

Just back off hols and trying this - still getting the
'String length is less than 0 or not an integer' message, tried a few variations but am stuck again. Any further help would be appreciated.
Jazztpt

 

[Madawc]

Please post your formula. Are you testing for null first?

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

 

[Jazztpt]

if not isnull({vwAddress.PostCode})and Length({vwAddress.PostCode}) > 0 then
Mid(Replace({vwAddress.PostCode}, " ", ""), 1, Length(Replace({vwAddress.PostCode}, " ", ""))-2)

I am looking at the postcodes of attendances for the last year. When I tested this for the last month it worked fine, I got the error message when I extended the date range.
The postcode field could be blank or have other rouge data in it, even sometimes tel numbers !

Thanks again.
Jazztpt

 

[Madawc]

You are subtracting 2 from a length that may be less than 2.

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

 

[lbass]

You need to be able to distinguish post codes from phone numbers in the same field--how can you tell this? If the post codes always start with "NE" then use:

if not isnull({vwAddress.PostCode})and
Length(replace({vwAddress.PostCode}," ","")) > 2 and
Replace({vwAddress.PostCode}, " ", "") startswith "NE" then
Left(Replace({vwAddress.PostCode}, " ", ""), Length(Replace({vwAddress.PostCode}, " ", ""))-2)

Otherwise you have to examine the variations in your data and share that here.

-LB

 

[Jazztpt]

That worked a treat and solved my problem.
Many thanks to everyone for the help, I really appreciate it.
Jazztpt