Solved Filter on two different inputs in form

[why]

Fist let me say that I am new to access database. I have been working on a project for the last several weeks. I do not have much experience with programing but do understand the basics of rational databases.

I am trying to filter by a combo box then a textbox. If I filter by each of the separately the code works. If I try and put it together it breaks with a mismatch error.

The combo box code is

Private Sub CMBUser_AfterUpdate()

Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34)
Me.FilterOn = True
Debug.Print Me.Filter
Me.Requery
End Sub

The Text box (Ruturn a count using Dcount of the number of records I want them to click it to filter)

Private Sub txtPPDateBefore_Click()
Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34) And ([ProposedDestructionD] < Date & "()")
Debug.Print Me.Filter
Me.FilterOn = True
Me.Requery
End Sub

When I try to run the click on textbox I get a mismatch runtime error 13

Is the me.filter the best way to do this? Or should I be looking at another type of filter method. I plan on adding several more textboxes. Each for the different record types with a dcount but all of them will need to update based on the User combo box.

 

[bob fitz]

Try:
Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34) & " And ([ProposedDestructionD] <#" & Date() & "#")

 

[why]

Try:
Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34) & " And ([ProposedDestructionD] <#" & Date() & "#")

The above code is giving me a Syntax error. Any other ideas? Thanks for your help.

I have tried this and it also gives an compile syntax error

today = ([ProposedDestructionD] <#" & Date() & "#")

 

[theDBguy]

Hi. Welcome to AWF!

Could you try this please?

Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34) & " And ([ProposedDestructionD] < Date()"

 

[why]

Hi. Welcome to AWF!

Could you try this please?

Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34) & " And ([ProposedDestructionD] < Date()"

Thanks I am getting a runtime error 3075
Missing),], or Item in query expresion '[User] Like "ABC*" And ([ProposedDestructionD <Date()'.

I changed your code to this and it started working

Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34) & " And [ProposedDestructionD] < Date()"

Thanks for the help. I am sure I have more issues as I work through the code.

 

[theDBguy]

Thanks I am getting a runtime error 3075
Missing),], or Item in query expresion '[User] Like "ABC*" And ([ProposedDestructionD <Date()'.

I changed your code to this and it started working

Me.Filter = "[User] Like " & Chr(34) & Me.CMBUser & "*" & Chr(34) & " And [ProposedDestructionD] < Date()"

Thanks for the help. I am sure I have more issues as I work through the code.

Hi. Glad to hear you got it sorted out. Sorry for the typo. Good luck with your project.