Share via

How to create a VBA code to search multiple fields in Access with one input box?

Anonymous
2025-03-28T20:43:38+00:00

Hello all,

I am having trouble making my search box work with all the fields. The PPNumYr field was originally a string and the numbers had leading zeros such 0197, 0297 and then I changed it to Numbers so the leading zero is now gone but there are numbers with zeros in the middle. I have posted my code below and I realize since I changed the field to numbers InStr does not work anymore but it did not work when the numbers were still a string. How can I fix this? Another field that I want to be searched is names of locations and I am not sure how to do that either, which is the second part of the code. Please help

If InStr(SearchText, "###") <> 0 Then

    DoCmd.OpenForm "PhotopointF", , , "PPNumYr="""  &   SearchText & """"

    Exit Sub

End If

If InStr(SearchText, "Out House", "East caves", "Bottom Hill") <> 0 Then

    DoCmd.OpenForm "PhotopointF", , , "LocationName="""  & SearchText & """"

    Exit Sub

End If
Microsoft 365 and Office | Access | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 39,986 Reputation points MVP Volunteer Moderator
    2025-03-28T21:00:33+00:00

    A number should not be wrapped in double-quotes, unlike a string, so:

    DoCmd.OpenForm "PhotopointF", , , "PPNumYr=" & SearchText

    Read up on proper use of the InStr function. Your second use of it is incorrect. This should not even compile. Did you try compiling this code? VBA window > Debug > Compile.

    0 comments
  2. DBG 11,381 Reputation points Volunteer Moderator
    2025-03-28T21:07:01+00:00

    For strings or text data, you should be able to use wildcards with the Like operator.

    0 comments
  3. Anonymous
    2025-03-28T21:17:56+00:00

    Use a combo box instead of a text box, with a RowSource property of:

    SELECT LocationName

FROM Locations
WHERE LocationName IN ("Out House", "East caves", "Bottom Hill")

ORDER BY LocationName;

    Ensure its LimitToList property is False (No) so that a number can be entered.  The code in its AfterUpdate event procedure would be like this:

    Dim strCriteria As String

If IsNumeric(Me.cboSearch) Then

    strCriteria = "PPNumYr = " & Me.cboSearch

Else

    strCriteria = "LocationName = """ & Me.cboSearch & """"

End If

DoCmd.OpenForm "PhotopointF", WhereCondition:=strCriteria
    0 comments
  4. Anonymous
    2025-03-28T21:20:33+00:00

    I removed the quotes but it still does not work.

    And yes I did debug it and gave me an error. I am not sure where to do from here.

    0 comments
  5. Anonymous
    2025-03-28T21:21:36+00:00

    Can you elaborate a bit more, I am fairly new to VBA?

    0 comments