Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too much criteria (97/SR2)

    I have a query which selects certain addresses. I am trying to select a specific set of postcodes (UK Postcodes i.e. ME9 5DK), however, the person who wrote the spec for me to do, as well as giving postcodes like ME*, also gave sets like IG1-11. The only way i've found to do this is by entering them seperately i.e. IG1* or IG2* etc. This increases the criteria and therefore will not work. Is there a way to select them using i.e. IG>1 & <11 sort of thing? I tried a few ways with the syntax but didn't get any to work.

    Any help would be much appreciated as the only other way I can think of is to have lots of queries to select the data and some kind of crosstab query to append them together.

  2. #2
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    I don't know how to do exactly what you're asking, but I've found that when I have long/ complex criteria in a query, a sub-query often does the trick.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Too much criteria (97/SR2)

    I'm not sure exactly what you're trying to do. Do you want to exclude the IG codes, or do you want both ME and IG codes?

    If the former, try this:

    Like "ME*"

    If the latter, try:

    Like "ME*" or Like "IG*"

    Does that help?
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    Thanks for the replies.

    The ME* is not a problem. The problem lies with the IG codes, as I don't need to select them all. If for example there are IG postcodes from 1 to 20 i.e. IG1 *, IG2 *.....IG19*,IG20*, I only want those from IG1 to IG11 therefore I can't use IG*. However, using the seperate selections doesn't work as there is then too much criteria. Hope that explains is a better explanation.

    Additional: An alternative I've just thought of is to deal with the problem in 2 columns, the first dealing with the letters i.e. IG and the 2nd deal with the numbers i.e. >=1 and <=11, however, I do not know of a way of searching for the first occurance of a number and counting until the occurance of a space. I am trying various experiments with instr but no luck so far.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    its messy but try a criteria something like:
    LEFT(postcodefield,2) = "IG" AND VAL(LEFT(postcodefield,4)<=11

    hopefully the left(postcode,4) will select the left four characters being something like IG11 or IG3 , the latter having a space at the end. The VAL function will extract the numerical value of the first contiguous (sp?) number characters in a string.

    not tested but i think this will be close to the mark.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    Thank you for your reply Father Jack (Drink!! (sorry, couldn't resist <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)).
    That didn't seem to work, and I checked the help file and Val only seems to extract numbers until it finds a letter and then stops. However, that gave me the idea to use Cint to convert the 3rd & 4th characters to integer, but because the field postcode is string the <= statement won't accept <=11 and changes it to <="11" so this didn't work either.

    I am currently attempting to either resolve this in the criteria for postcode or have an expression column with just the numbers extracted and put the <=11 part in the criteria for that - which would be easy except for the fact that some postcodes have 1 letter at the front and some have 2!!! Therefore I am trying to count how many letters are before the 1st number - I tried instr(1,[postcode],#,1) but this didn't work.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    Solved it.
    The inspiration Father Jack gave me to use Cint now works, I just needed to add brackets so the <=11 doesn't add quotes.
    Left([postcode],2)="ig" And (CInt(Mid([postcode],3,2))<=11)

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    i am pretty sure that uk postcodes all have two alpha characters as the first characters so you could monkey about with VAL(mid(postcodefield,3,2)) <= VAL("11") to see if that gets you the right results.

    Sorry about the red herring with VAL - oops

    i hate postcodes !

    what is the overall effect that you are trying to create ?
    i am thinking it may be better to create a lookup table of your postcodes and gain a grouping capability accordingly.
    or
    have you tried the IN sql command ?
    select the column as
    columnname:left(postcodefield,4)
    create a criteria like :
    in("IG1 ","IG2 ","IG3 ",........"IG10","IG11")
    but make sure you allow for the spaces that the left(....,4) will return

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    There are a few postcodes with 1 letter at the front, namely N, S, E, W. I have just found out that there are also some such as EC2Y which means my previous post saying it was solved is untrue - I've also just noticed that if there is no criteria it returns a data type mismatch.

    I haven't tried the IN command as it didn't occur to me to trim the postcode so I didn't have to use *. Will this extend the amount of criteria able to be entered, as there are 190 criteria options needed?

    The initial problem is that I have a report to build based on a select number of postcodes which exceeds the criteria limits in access - if I could import the table into SQL it wouln't be a problem, but that isn't possible in this case. Therefore where I have to select i.e. E1-18, IG1-11, PE30-38, I thought there might be a way to select them without typing every single entry in, therefore avoiding the "query too complex" problem. So far your idea of making lookup tables seems the most straightforward to acheive - thanks for the tip i'll give it a go.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    doh, forgot about london postcodes !!!

    The way that i would go about this is to get a list of all postcodes and import them into a table. As another field in this table i would have either a yes/no field (ie those that you want to report on and those you dont)
    OR
    a numeric field that referenced another table (this table could then have a coded reference that you could use for other search/report purposes - you know that will come when the user gets this first solution dont you !!!)

    once all the postcodes are in then get a user to go through the list and mark the ones that you need in the report ( hey , it's their data isnt it ? you just look after the database structure DONT YOU <img src=/S/wink.gif border=0 alt=wink width=15 height=15>)

    once this is done robert is your dads brother.

    create a query that selects the postocdes that are or are not ticked as you require and then create the main query that outputs to the report but link the table postcode field to the new 'postcode table' postcode field, thereby letting the join (Left Outer or what ever) filter the recordset.

    job done - if only !!!

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much criteria (97/SR2)

    Excellent - the perfect solution! And your right in that I just look after the database structure - you know how it is - I do all the hard work <img src=/S/clever.gif border=0 alt=clever width=15 height=15> (with a little help from my friends at the Lounge!).

    Thanks very much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •