Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using functions in query criteria (XP)

    I need to check for appropriate values entered in a (text) field. The values are UK grid references, eg. SJ89950808, i.e.

    The string is 10 characters in length
    The first 2 characters are letters, not numbers
    The rest (8) should be numbers - but I've just being trying to test the last character.

    I've got a query and have been trying to use the following criteria:

    (Len([GridRef])=10) And (Not IsNumeric(Left([GridRef],2))) And (IsNumeric(Right([GridRef],1)))

    This returns no rows, with the problem seeming to be testing IsNumeric for the last character on the right as, if I remove it, I get records returned, albeit ones that don't meet the necessary criteria. If I try to use the IsNumeric test on its own I also get no records.

    I'm sure I'm being stupid but I just can't get this to work.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using functions in query criteria (XP)

    You could set an Input Mask on the field in the table or on a form:

    >LL00000000

    This means: two letters (required, automatically converted to upper case) followed by 8 digits (required)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using functions in query criteria (XP)

    If you want to check existing values, the criteria to return valid values would be

    Len([GridRef])=10 AND Asc(UCase(Left([GridRef],1))) Between 65 And 90 AND Asc(UCase(Mid([GridRef],2,1))) Between 65 And 90 AND IsNumeric(Mid([GridRef],3))=True

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using functions in query criteria (XP)

    I an ideal world.....

    Unfortunately, the data has already been entered, approximately 40,000 rows!

    We are now trying to match these records with some other information and have a very poor correlation - like about a 1000.

    The grid references were copied from paper sheets with the data entry users instructed to 'type exactly what you see' so, whilst I expect the odd typo, I think the majority or 'errors' were on the original sheets and there is nothing we can do about it. I am trying to get a handle on how many actually appear to be in the correct format.

    To clarify, the reference SJ8895065A has 10 characters, the first 2 are letters but so is the last, which is blatently wrong. This is something that cannot possibly be fixed so I need to weed out similar incorrect entries.

    Any ideas why my IsNumeric bit is failing?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using functions in query criteria (XP)

    Just realised we must have posted at the same time, I'll read your second reponse now

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using functions in query criteria (XP)

    Reading your second post I realised I'd omitted to specifiy IsNumeric = true so I tried it as follows:

    (Len([GridRef])=10) And (<>IsNumeric(Left([GridRef],2))) And (IsNumeric(Right([GridRef],1))=True)

    and it worked.

    I'll file away your suggestion with the asci codes if I need to do anything more robust,

    Cheers <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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