Filter records on my form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My form "frmInput" fields: sheetdate (date), lname (text), fname(text)
Here is the question. How can I filter only data that is equal to or 1 day
less that the current date.

Example: of what I used in the forms filter property (that doesn't
work)sheetdate=Date()-1

How do I make this work? Thanks
 
For the filter to be applied, the form's FilterOn must be set to True.

Your expression will return only yesterday's records. If you want them to
also return today's (and presuming there are no records with a date AFTER
today), the expression is:

Sheetdate >= Date()-1

Sprinks
 
Hi.

Try:

DoCmd.OpenForm Me.Name, acNormal, , "sheetdate BETWEEN Date() and
(Date() - 1)"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Do I put this on the onload event?

'69 Camaro said:
Hi.

Try:

DoCmd.OpenForm Me.Name, acNormal, , "sheetdate BETWEEN Date() and
(Date() - 1)"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
Click to expand...
 
Where is the "FilterOn" property on the form?

Sprinks said:
For the filter to be applied, the form's FilterOn must be set to True.

Your expression will return only yesterday's records. If you want them to
also return today's (and presuming there are no records with a date AFTER
today), the expression is:

Sheetdate >= Date()-1

Sprinks
Click to expand...
 
Hi.
Do I put this on the onload event?
Click to expand...

No. It may be placed in the procedure used to open the form, or it may be
placed in the frmInput form, such as in the OnClick( ) event of a button to
apply the filter. In the following example, ApplyFilterBtn is the name of
the button on the frmInput form to apply the filter, and RemFilterBtn is the
name of the button to turn off the filter:

Private Sub ApplyFilterBtn_Click()

On Error GoTo ErrHandler

DoCmd.OpenForm Me.Name, acNormal, , "sheetdate BETWEEN Date() and
(Date() - 1)"

Exit Sub

ErrHandler:

MsgBox "Error in ApplyFilterBtn_Click( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub

Private Sub RemFilterBtn_Click()

On Error GoTo ErrHandler

Me.FilterOn = False

Exit Sub

ErrHandler:

MsgBox "Error in RemFilterBtn_Click( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
End If
Err.Clear
End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Sorry. Got an extra line pasted in there. The RemFilterBtn_Click( )
procedure should be:

Private Sub RemFilterBtn_Click()

On Error GoTo ErrHandler

Me.FilterOn = False

Exit Sub

ErrHandler:

MsgBox "Error in RemFilterBtn_Click( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
Thanks for your help, last question:
Another form opens the "frmInput" form. I really need to have the frmInput
filtered when it opens. I only want users seeing today's and yesterdays
inputted records. How do I filter it without and command buttons? Thanks again
 
Hi.

Assuming your other form has a button that the user selects to open the
frmInput form, then try the following example, where OpenInputFormBtn is the
name of the button to open the frmInput form:

Private Sub OpenInputFormBtn_Click()

On Error GoTo ErrHandler

DoCmd.OpenForm "frmInput", acNormal, , "sheetdate BETWEEN Date() and
(Date() - 1)"

Exit Sub

ErrHandler:

MsgBox "Error in OpenInputFormBtn_Click( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top