"Julian" <x@x.xwrote in message news:4j******** ****@individual .net...
>I use Access. See my reply above.
ok...just not clear what the reference and information about oo was all
about...it just serves to confuse the issue, or introduce extra complexities
here. (what does oo have to do this problem then????).
Anyway....
The first problem you need to tackle is that of stripping out the postal
codes. You don't mention if your files are large, or small.
If your data file is only 5000-8000 records, then you likely can process the
records in place. If you have more records, then I would introduce two new
fields into the database.
Region
This field would hold the region
eg: E19 4PR, we set region = E
NW5U 4RT. we set region = NW
etc. etc. etc.
I would then create a another field called RegionN
RegionN
This field would hold the number, such as
eg: E19 4PR, we set RegionN = 19
NW5U 4RT. we set RegionN = 5
etc. etc. etc.
Once we do the above, then we would be able to obtain decent performance.
the above type of string processing could be done for each query, but that
would make for difficult queries.
So, I would add the two fields, and then run a update query to pull/put in
the region values into that new region field in the table....
I would then run another update query to put in the regionNUM vales into
that
Once the above is done, then the query you ask for would be easier to
write....eg
E(1-18), SE(1-29), SW(1-29)
(Region = "E" and (RegionN between 1 and 18)
or
(Region = "SE" and (RegionN between 1 and 29)
OR
(Region = "SW" and (RegionN between 1 and 29)
you can well see how much more easy our problem becomes when we split out
the post codes as above....
It is not clear if this data file of yours is being constantly updated.
further, are the post codes ALWAYS consistent in how they are entered (if
they are not..then trying to do processing on data that is not in your
example format is not going to work).
Anyway, the above is the general idea here. It is not known how large your
files are, but adding those two extra fields, and processing out the data
into those two fields would make this problem DRAMATICALLY easier. And, this
approach would also ensure good performance when building a query.
to do this type of data processing you are going to need some software
skills here. Lets take a crack at this...
The first routine we need is one that grabs the region value up to the first
number
The 2nd routine we need is one that grabs the region "number"
Here is the 3 routines needed. You can paste them into a standard code
module..and save it...
Public Function regionchar(s As Variant) As Variant
If IsNull(s) Then Exit Function
If s Like "[A-Z][A-Z]*" = True Then
regionchar = Left(s, 2)
Else
If s Like "[A-Z]*" = True Then
regionchar = Left(s, 1)
End If
End If
End Function
Public Function regioncharN(s As Variant) As Variant
If IsNull(s) Then Exit Function
If s Like "[A-Z][A-Z]*" = True Then
regioncharN = OnlyNumbers(Mid (s, 3))
Else
If s Like "[A-Z]*" = True Then
regioncharN = OnlyNumbers(Mid (s, 1))
End If
End If
End Function
Public Function OnlyNumbers(s As Variant) As String
Dim i As Integer
Dim mych As String
OnlyNumbers = ""
If IsNull(s) = False Then
i = 1
For i = 1 To Len(s)
mych = Mid$(s, i, 1)
If InStr("01234567 89", mych) 0 Then
OnlyNumbers = OnlyNumbers & mych
Else
Exit For
End If
Next i
End If
End Function
Now, it is just a simple matter to run two udpates on the file to set our
two new fields we created
Region (text)
RegionNum (number - integer)
update tblCustomers set Region = RegionChar([PostalCode]
Run the above in the query bulder...
and, also
update tblCustomers set RegionNum = RegionCharN([PostalCode]
Once we done the above, we can now build a query based on our conditions
quite easy.
Send this query results to a report, or even select, and cut/paste into your
word document....
and, you might find the formating in word works better if you export the
query to excel first...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com 
