filter form based on text box


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
 
Ad

Advertisements

J

John Vinson

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
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]
 
P

papa jonah

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?
 
J

John Vinson

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?
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]
 
P

papa jonah

Thank you John for the explanation. It helps me to understand for next
time.
 
Ad

Advertisements

Joined
Jun 2, 2014
Messages
1
Reaction score
0
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top