filter form based on text box

  • Thread starter Thread starter papa jonah
  • Start date Start date
P

papa jonah

I have a text box on a form that I want to use to enter a date, which
will then be used as the date to filter by.
For example, if I enter 9/1/2003, I want the form to filter to
everything greater than that date.

This is what I have so far, but my doCmd line is buggered somehow.

Private Sub txtFilterDate_AfterUpdate()
DoCmd.ApplyFilter , "discovery_date = '" & TxtFilterDate & "'"
Me.FilterOn = True
End Sub
 
I have a text box on a form that I want to use to enter a date, which
will then be used as the date to filter by.
For example, if I enter 9/1/2003, I want the form to filter to
everything greater than that date.

This is what I have so far, but my doCmd line is buggered somehow.

Private Sub txtFilterDate_AfterUpdate()
DoCmd.ApplyFilter , "discovery_date = '" & TxtFilterDate & "'"
Me.FilterOn = True
End Sub
Click to expand...

Change it to

Me.Filter = "discovery_date >= #" & TxtFilterDate & "#"

Use the Form's Filter property (rather than the ApplyFilter method);
use >= ( or > ) rather than the = operator if you want to see later
dates; and delimit the Date/Time criterion with the Date/Time
delimiter # rather than the Text delimiter '.

John W. Vinson[MVP]
 
That works good. Now I want to add a default date. I have entered
09/01/1994 as the default value for the txtbox (which is formatted as a
short date). Except the date that appears is 12/30/1899 and if I try
to highlight the date 12:06:30 AM shows up.
My input mask is 99/99/0000.

Why isn't my chosed default value appearing?
 
That works good. Now I want to add a default date. I have entered
09/01/1994 as the default value for the txtbox (which is formatted as a
short date). Except the date that appears is 12/30/1899 and if I try
to highlight the date 12:06:30 AM shows up.
My input mask is 99/99/0000.

Why isn't my chosed default value appearing?
Click to expand...

Set the Default to #9/1/1994# - with the date delimiters. What's
happening is that it's dividing 9 by 1 by 1994, giving you
0.00451354062186559, and converting that to a date.

John W. Vinson[MVP]
 
Thanks very much for posting this here. It really helped me out. I kept finding overly complicated solutions online. This was right up my alley.
 
Back
Top