search records regardless of capitalization

[Lauren B]

I have built a search page into my database that allows user to search
entries based on a variety of criteria (Customer name, address, city, state,
etc.) I have used the following format:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "USER_SEARCH_FORM"
stLinkCriteria = ""

If Len(Trim(Me.USER_ID)) > 0 Then
stLinkCriteria = stLinkCriteria & "[USER_ID] Like '*" & Me.USER_ID & "*' AND
"
End If

If Len(Trim(stLinkCriteria)) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)
End If

If DCount("*", "USERS_TABLE", stLinkCriteria) = 0 Then
MsgBox "No records found."

Else
DoCmd.OpenForm "USERS_FORM", , , stLinkCriteria
stDocName = "USER_SEARCH_FORM"
stLinkCriteria = ""
End If

End Sub

When I use this search, it works fine; however, results only return if the
user uses the correct capitalization (ie. if a user is searching in the
state field and they put "PA" they will get all customers in PA but, if the
user puts "pa", they will get no results. Is there a way to add to the
coding above to have capitalization disregarded?

Thank you in advance for any assistance.

LB

 

[Rick Brandt]

I have built a search page into my database that allows user to search
entries based on a variety of criteria (Customer name, address, city,
state, etc.) I have used the following format: [snip]

When I use this search, it works fine; however, results only return
if the user uses the correct capitalization [snip]

What is your back end database? Jet (the default database engine for
Access) is not case sensitive and in fact is pretty difficult to get to act
case-sensitive. Many server database engines can be either case sensitive
or not depending on how they are set up.

The usual work-around (not very efficient) is to use something like...

SELECT *
FROM TableName
WHERE UCase(SomeField) = UCase([Some Criteria])