Using functions in query criteria (XP)

Topic

New Reply

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.

Reply | Quote

Replies

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)

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote