VBA filter for Access

[Tribble]

Hi,

I have created two filters for a form. The first one about the surnames works. The second says that records do not exist. What have I done wrong and what am I forgetting to do. My problem is in the ApplyProvinceFilter

Help please.

Code: Select all

Public Function ApplyFilter()
On Error GoTo myErr

     With CodeContextObject
          Select Case .SurnameFilters
          'I have deleted -  AND Channel =" & giFilter - from the lines below
          
               Case 1: DoCmd.ApplyFilter "", "[Surname] Like '[AÀÁÂÃÄ]*'"
               Case 2: DoCmd.ApplyFilter "", "[Surname] Like 'B**'"
               Case 3: DoCmd.ApplyFilter "", "[Surname] Like '[CÇ]*'"
               Case 4: DoCmd.ApplyFilter "", "[Surname] Like 'D*'"
               Case 5: DoCmd.ApplyFilter "", "[Surname] Like '[EÈÉÊË]*'"
               Case 6: DoCmd.ApplyFilter "", "[Surname] Like 'F*'"
               Case 7: DoCmd.ApplyFilter "", "[Surname] Like 'G*'"
               Case 8: DoCmd.ApplyFilter "", "[Surname] Like 'H*'"
               Case 9: DoCmd.ApplyFilter "", "[Surname] Like '[IÌÍÎÏ]*'"
               Case 10: DoCmd.ApplyFilter "", "[Surname] Like 'J*'"
               Case 11: DoCmd.ApplyFilter "", "[Surname] Like 'K*'"
               Case 12: DoCmd.ApplyFilter "", "[Surname] Like 'L*'"
               Case 13: DoCmd.ApplyFilter "", "[Surname] Like 'M*'"
               Case 14: DoCmd.ApplyFilter "", "[Surname] Like '[NÑ]*'"
               Case 15: DoCmd.ApplyFilter "", "[Surname] Like '[OÒÓÔÕÖ]*'"
               Case 16: DoCmd.ApplyFilter "", "[Surname] Like 'P*'"
               Case 17: DoCmd.ApplyFilter "", "[Surname] Like 'Q*'"
               Case 18: DoCmd.ApplyFilter "", "[Surname] Like 'R*'"
               Case 19: DoCmd.ApplyFilter "", "[Surname] Like '[SŠ]*'"
               Case 20: DoCmd.ApplyFilter "", "[Surname] Like 'T*'"
               Case 21: DoCmd.ApplyFilter "", "[Surname] Like '[UÙÚÛÜ]*'"
               Case 22: DoCmd.ApplyFilter "", "[Surname] Like 'V*'"
               Case 23: DoCmd.ApplyFilter "", "[Surname] Like 'W*'"
               Case 24: DoCmd.ApplyFilter "", "[Surname] Like 'X*'"
               Case 25: DoCmd.ApplyFilter "", "[Surname] Like '[YÝÿ]*'"
               Case 26: DoCmd.ApplyFilter "", "[Surname] Like '[ZÆØÅ]*'"
               Case 27: DoCmd.ApplyFilter "", "[Surname] Like '*'"
          End Select
          
          If (.CurrentRecord > 0) Then
               ' If records are returned for the selected letter, go to the CompanyName control.
               DoCmd.GoToControl "Surname"
               ' Stop the macro.
               Exit Function
          End If
          If (.CurrentRecord = 0) Then
               ' If no records are returned for the selected letter, display a message.
               Beep
               MsgBox "There are no records for that letter.", vbInformation, "No Records Returned"
               ' Show all records.
               DoCmd.ShowAllRecords
               ' Press in the All button.
               .SurnameFilters = 27
          End If
     End With


myEXIT:
     Exit Function

myErr:
     MsgBox Error$
     Resume myEXIT

End Function       

Code: Select all

Public Function ApplyProvinceFilter()
On Error GoTo myErr2

     With CodeContextObject
          Select Case .ProvinceFilters
          
               Case 1: DoCmd.ApplyFilter "", "[Province] Like '[CALL CENTRE]'"
               Case 2: DoCmd.ApplyFilter "", "[Province] Like '[ECOAST]'"
               Case 3: DoCmd.ApplyFilter "", "[Province] Like '[Gauteng]'"
               Case 4: DoCmd.ApplyFilter "", "[Province] Like '[Highv]'"
               Case 5: DoCmd.ApplyFilter "", "[Province] Like '[Misc]'"
               Case 6: DoCmd.ApplyFilter "", "[Province] Like '[WCape]'"
               Case 7: DoCmd.ApplyFilter "", "[Province] Like '[Head Office]'"
               Case 8: DoCmd.ApplyFilter "", "[Province] Like '*'"
               
               
          End Select
          
          If (.CurrentRecord > 0) Then
               ' If records are returned for the selected letter, go to the CompanyName control.
               DoCmd.GoToControl "Province"
               ' Stop the macro.
               Exit Function
          End If
          If (.CurrentRecord = 0) Then
               ' If no records are returned for the selected letter, display a message.
               Beep
               MsgBox "There are no records for that Province.", vbInformation, "No Records Returned"
               ' Show all records.
               DoCmd.ShowAllRecords
               ' Press in the All button.
               .ProvinceFilters = 8
          End If
     End With


myEXIT:
     Exit Function

myErr2:
     MsgBox Error$
     Resume myEXIT

End Function

[Ron2K]

Are you doing exact matches or partial matches? Use the equality operator if you're doing exact matches; use wildcards with the LIKE keyword if you're doing partial matches.

[Tribble]

I have the * in but took it out when it wasn't working. I shall remove the like and see if they work. There are only 8 of them so I can use exact.

[Tribble]

Sadly that doesn't work.

[Tribble]

I have a quick and nasty solution but I really want this to work out as it would be neater and much cooler than the way I used to have it.

[Ron2K]

It should have worked - check this out.

EDIT: Ah, I think I see it - try removing the square brackets from your string literals...

[Tribble]

Lol thanks - will try it.

[Tribble]

Oh you are a hunny!!!!!!! I owe you even more Foundrys now

Now - how do I get it to apply both filters at the same time?