• Using functions in query criteria (XP)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Using functions in query criteria (XP)

    Author
    Topic
    #406006

    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.

    Viewing 3 reply threads
    Author
    Replies
    • #838514

      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)

      • #838520

        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?

      • #838521

        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?

        • #838524

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

          • #838528

            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 cheers

          • #838529

            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 cheers

        • #838525

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

    • #838515

      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)

    • #838518

      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

    • #838519

      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

    Viewing 3 reply threads
    Reply To: Using functions in query criteria (XP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: