How to make Interval filter in a FORM? (VBA)

[Martin]

< It's a bit complicate and sorry for my poor English expression >


I have a FORM ( which have been filtered by some field, when opened), I want
to set two TEXT BOXES in the FORM, one text box is for the user to enter
BEGINNING DATE , the other box is for the user to enter ENDING DATE. And
then, I would set a BUTTON, when click the button, the FORM's records will
filtered into the interval datas of the BEGINNING DATE and the ENDING DATE.

HOW SHOULD THIS BUTTON'S VBA BE?

Thanks!

Martin Lee

 

[Guest]

Place the following code in your buttons click event

Me.FilterOn = True
Me.Filter = "[myDate] Between #" & Me.txtStart & "# AND #" & Me.txtEnd &
"#"

where myDate is the name of the date field you want to based the filter on
and txtStart and txtEnd are the names of the start and end text boxes.

Delordson Kallon
http://www.instantsoftwaretraining.com/

 

[Douglas J Steele]

One thing to remember is that Access is always going to treat dates written
as #nn/nn/nnnn# as being in mm/dd/yyyy format, regardless of what the Short
Date format may have been set to through Regional Settings. That means that
it's safer to use:

Me.Filter = "[myDate] Between " & Format(Me.txtStart, "\#mm\/dd\/yyyy\#")
& " AND " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#")


(Okay, what I said isn't strictly true. Access will treat 25/12/2006 as 25
Dec, 2006, because it's smart enough to know that there isn't a 25th month.
However, 01/12/2006 will always be treated as 12 Jan, 2006 and never as 01
Dec, 2006. And you can actually use any unambiguous format, such as dd mmm
yyyy or yyyy-mm-dd. A problem with using dd mmm yyyy, though, is that if the
language isn't set up properly, your user's input might not be accepted.
This only occurs, of course, if they actually type in 10 Fev 2006, meaning
10 Feb 2006, and the system on which they're working isn't set up for
French.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Place the following code in your buttons click event

Me.FilterOn = True
Me.Filter = "[myDate] Between #" & Me.txtStart & "# AND #" & Me.txtEnd &
"#"

where myDate is the name of the date field you want to based the filter on
and txtStart and txtEnd are the names of the start and end text boxes.

Delordson Kallon
http://www.instantsoftwaretraining.com/

< It's a bit complicate and sorry for my poor English expression >


I have a FORM ( which have been filtered by some field, when opened), I want
to set two TEXT BOXES in the FORM, one text box is for the user to enter
BEGINNING DATE , the other box is for the user to enter ENDING DATE. And
then, I would set a BUTTON, when click the button, the FORM's records will
filtered into the interval datas of the BEGINNING DATE and the ENDING DATE.

HOW SHOULD THIS BUTTON'S VBA BE?

Thanks!

Martin Lee

 

[Martin]

Mr. Kallon and Mr. Steele:

First, thank you for your answering.

Second, the problem is not so simple, because the FORM is opened by a
BUTTON's click, this BUTTON click already placed another FILTER! If I
place the date interval filter as you said, I am afraid your filter will
affect the former filter, will it ?!


Martin Lee

 

[Douglas J. Steele]

Are you saying that you already have a filter, or did you use a Where clause
when opening the report?

If you already have a filter, I believe you can simply append the new clause
to that filter:

If Len(Me.Filter) > 0 Then
Me.Filter = Me.Filter & " AND [myDate] Between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & " AND " & _
Format(Me.txtEnd, "\#mm\/dd\/yyyy\#")
Else
Me.Filter = "[myDate] Between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & " AND " & _
Format(Me.txtEnd, "\#mm\/dd\/yyyy\#")
End If

If you used a Where clause, I'm not sure...

 

[Martin]

Wow! That's what I want!

Thank you very much!

Martin Lee

Are you saying that you already have a filter, or did you use a Where
clause when opening the report?

If you already have a filter, I believe you can simply append the new
clause to that filter:

If Len(Me.Filter) > 0 Then
Me.Filter = Me.Filter & " AND [myDate] Between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & " AND " & _
Format(Me.txtEnd, "\#mm\/dd\/yyyy\#")
Else
Me.Filter = "[myDate] Between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & " AND " & _
Format(Me.txtEnd, "\#mm\/dd\/yyyy\#")
End If

If you used a Where clause, I'm not sure...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Mr. Kallon and Mr. Steele:

First, thank you for your answering.

Second, the problem is not so simple, because the FORM is opened by a
BUTTON's click, this BUTTON click already placed another FILTER! If I
place the date interval filter as you said, I am afraid your filter will
affect the former filter, will it ?!


Martin Lee

 

[Martin]

That's what I want.
Thank you very much

Martin Lee

Are you saying that you already have a filter, or did you use a Where
clause when opening the report?

If you already have a filter, I believe you can simply append the new
clause to that filter:

If Len(Me.Filter) > 0 Then
Me.Filter = Me.Filter & " AND [myDate] Between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & " AND " & _
Format(Me.txtEnd, "\#mm\/dd\/yyyy\#")
Else
Me.Filter = "[myDate] Between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & " AND " & _
Format(Me.txtEnd, "\#mm\/dd\/yyyy\#")
End If

If you used a Where clause, I'm not sure...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Mr. Kallon and Mr. Steele:

First, thank you for your answering.

Second, the problem is not so simple, because the FORM is opened by a
BUTTON's click, this BUTTON click already placed another FILTER! If I
place the date interval filter as you said, I am afraid your filter will
affect the former filter, will it ?!


Martin Lee