Need help coding a search box (1 Viewer)

[gojets1721]

I have a search command button that I'm hoping will allow the user to input a name and it filters all the entries down to that name. I also want it to automatically filter to only the last 18 months.

I came up with this rudimentary code and it doesn't work. I'm very green to VBA. Any suggestions?

Private Sub cmdSearch_Load()
  
    Dim S As String
    
    S = InputBox("Enter Customer Name", "Customer Name Search")
    If S = "" Then Exit Sub
    
    Me.Filter = "CustomerName LIKE ""*" & S & "*""" And ComplaintDate = DateAdd("m", -18, Date)
    Me.FilterOn = True
  
End Sub

 

[theDBguy]

Maybe?

Me.Filter = "CustomerName Like ""*" & S & "*"" AND ComplainDate >= DateAdd(""m"", -18, Date())"

 

[arnelgp]

you may also try:

Me.Filter = "CustomerName LIKE '*" & S & "*' And ComplaintDate = " & Format(DateAdd("m", -18, Date), "\#mm\/dd\/yyyy\#")

 

[gojets1721]

Both worked! Thanks so much!!

 

[theDBguy]

Both worked! Thanks so much!!

Glad to hear you got it to work. @arnelgp and I were happy to assist. Good luck with your project.