Solved Filter form by Date using a text box, command button and macro (1 Viewer)

kengooch

Member
Local time
Yesterday, 17:27
Joined
Feb 29, 2012
Messages
137
I want to filter a form by a specific date.

I have created an unbound text box that filters records by several different fields by whatever text string I type in to a text box. I then execute the filter with a Command Button. I have tried to use this same concept with a date field but it doesn't work.

Here is the info on the filter that works.
Form = fSendLogLst
Unbound Text Box = luSrchAll
Command button = bSearchAll
On Click / Macro / Where Condition = [tSOPatientNm] Like "*" & [Forms]![fSendLogLst]![luSrchAll] & "*" Or [tSOPatSSN] Like "*" & [Forms]![fSendLogLst]![luSrchAll] & "*" Or [tSOAccNo] Like "*" & [Forms]![fSendLogLst]![luSrchAll] & "*"

This works just fine. So I tried to modify it for use with the date.
Form = fSendLogLst
Unbound Text Box = luDate
Command button = bSrchDate
On Click / Macro / Where Condition = [tSODateSent]="#" & [Forms]![fSendLogLst]![luDate] & "#"

Obviously my syntax is wrong. Any thoughts?
Should I try to write this in VBA or can the macro be corrected?

Thanks so much for any help.
Ken
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:27
Joined
Oct 29, 2018
Messages
21,358
Hi Ken,

Just curious, what is your Regional Settings? What would you enter in the Textbox for today's date?
 

Micron

AWF VIP
Local time
Yesterday, 20:27
Joined
Oct 20, 2018
Messages
3,476
Also could make sure there is a date value by adding a message box. I don't use macros so I'm guessing that you're defaulting to .Value here whereas an unbound textbox that still has the focus may only have a .Text property value and not a .Value property value. You might have to move off of the control first.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:27
Joined
Oct 29, 2018
Messages
21,358
7/14/2020 is the format for a simple date. I will check it.
Then I would expect it to work. However, sometimes, you may have to force it to a date value by using CDate().
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:27
Joined
Sep 21, 2011
Messages
14,048
This worked for me in 2007 with UK dates in both form and table.
Where condition is
Code:
[Transactions]![TransactionDate]=[Forms]![frmTransactions]![txtfilter]
I did have to make sure I did not have any filter left over in the properties of the form?
I also used the Builder to construct it.

HTH
 

kengooch

Member
Local time
Yesterday, 17:27
Joined
Feb 29, 2012
Messages
137
Here is the solution to the Date issue.
Code:
Private Sub luDate_AfterUpdate()
'= = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' By Ken Gooch last updated 02/15/2020
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Filter by Date
    [Forms]![fAccList].Filter = "tAccLog.[tDateSF515] = #" & luDate & "#"
    [Forms]![fAccList].FilterOn = True
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:27
Joined
Oct 29, 2018
Messages
21,358
Here is the solution to the Date issue.
Code:
Private Sub luDate_AfterUpdate()
'= = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' By Ken Gooch last updated 02/15/2020
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Filter by Date
    [Forms]![fAccList].Filter = "tAccLog.[tDateSF515] = #" & luDate & "#"
    [Forms]![fAccList].FilterOn = True
End Sub
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom