Case insensitive filter in MS Access by VBA code

Question

I have an MS Access front end connected via ODBC to a MySQL database. I created a short VBA code to apply a filter on a continuous form based on a user's input. (ie. user can search for any part of a name on the list typing some letters and apply the filter by "Enter".)

I would like to make the process to be case insensitive but I can't.

My code is:

Option Compare Text    
Private Sub txbNameSearch_AfterUpdate()
 Me.Filter = "GuestName LIKE """ & "*" & TempVars!tvGuestName & "*" & """"
 Me.FilterOn = True
End Sub

I thought that "Option Compare Text" should do the trick but it doesn't. How can I make the filtering to be case insensitive?

Answer 1

You can convert it all to UCase to do this.

Me.Filter = "UCase([GuestName]) LIKE """ & "*" & UCase(TempVars!tvGuestName) & "*""""