Returning Uppercase entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a query on a table containing data exported from a payroll system
and imported into Access. The table contains names. eg. Julie Anne

Some of the data has been entered incorrectly as only initials. eg. J or
JA or J A or J Anne etc

I have been able to get the records where there is a single character or a
space between the first character and the second eg. J A or J Anne by using
Like '[A-Z]' Or Like '[A-Z] *'

To return the fields with no space between the characters I have used the
expression Like '[A-Z][A-Z]' but this also returns records where the persons
first name contains only two letters eg. Bo - how do I return records where
the data is in uppercase only?
 
Access is case insensitive by design. The only way I'm aware of to check
would be to use the StrComp function to compare the given string with itself
converted to upper case (using the UCase function) with a compare value of
3:

WHERE StrComp([MyField], UCase([MyField], 0) = 0

Warning: it'll be slow.
 
Thanks Douglas I have tested your suggestion and it works.

Cheers
Pauline

Douglas J. Steele said:
Access is case insensitive by design. The only way I'm aware of to check
would be to use the StrComp function to compare the given string with itself
converted to upper case (using the UCase function) with a compare value of
3:

WHERE StrComp([MyField], UCase([MyField], 0) = 0

Warning: it'll be slow.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sherre said:
I am doing a query on a table containing data exported from a payroll
system
and imported into Access. The table contains names. eg. Julie Anne

Some of the data has been entered incorrectly as only initials. eg. J or
JA or J A or J Anne etc

I have been able to get the records where there is a single character or a
space between the first character and the second eg. J A or J Anne by
using
Like '[A-Z]' Or Like '[A-Z] *'

To return the fields with no space between the characters I have used the
expression Like '[A-Z][A-Z]' but this also returns records where the
persons
first name contains only two letters eg. Bo - how do I return records
where
the data is in uppercase only?
Click to expand...
Click to expand...
 
Back
Top