ACCESS FEED: RE: [MS_AccessPros] Filter subform between Two Dates

Kamis, 24 Mei 2012

RE: [MS_AccessPros] Filter subform between Two Dates

Abdul-

I hope you didn't copy and paste what showed up in my reply (bad line breaks).
Try this:

Dim strWhere As String

If IsDate(Me.txtDateTo) = False Then Exit Sub
If IsDate(Me.txtDateFrom) = False Then Exit Sub
If Me.txtDateFrom > Me.txtDateTo Then Exit Sub

strWhere = "[InvoiceDate] Between #" & _
Format(CDate([Me.txtDatefrom]), "mm/dd/yyy") & _
"# and #" & _
Format(CDate([Me.txtDateTo]), "mm/dd/yyyy") & "#"

Debug.Print strWhere

Me.qryOrdersforSearchSubform_subform.Form.Filter = strWhere
Me.qryOrdersforSearchSubform_subform.Form.FilterOn = True

Take a look at what's in the Immediate Window after you run your code.

And you don't need these two lines:

Dim InvoiceDate As Date
InvoiceDate = Me.qryOrdersforSearchSubform_subform.Form!InvoiceDate

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Abdul Shakeel
Sent: Thursday, May 24, 2012 9:24 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Finding "This Fiscal Year"

Dear John

Thanks for the advise

But the form is still not filtered I am getting this error "Can't find the
field "|" referred to in your expression."

---------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Thursday, May 24, 2012 9:10 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Filter subform between Two Dates

Abdul-

You must use octothorpes (#) around date/time literals, not quotes:

Me.qryOrdersforSearchSubform_subform.Form.Filter = "[InvoiceDate]
Between #" & Format(CDate([Me.txtDatefrom]), "mm/dd/yyy") & "# and #" &
Format(CDate([Me.txtDateTo]), "mm/dd/yyyy") & "#"

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Abdul Shakeel
Sent: Thursday, May 24, 2012 7:53 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Filter subform between Two Dates

Dear All,

I am using this code to filter subfrom between two dates using text boxes
on the main form, but it is not working, what I am doing wrong

Private Sub cmdFilterByDate_Click()
On Error GoTo Err_cmdFilterByDate_Click
Dim InvoiceDate As Date
InvoiceDate = Me.qryOrdersforSearchSubform_subform.Form!InvoiceDate

If IsDate(Me.txtDateTo) = False Then Exit Sub
If IsDate(Me.txtDateFrom) = False Then Exit Sub
If Me.txtDateFrom > Me.txtDateTo Then Exit Sub

Me.qryOrdersforSearchSubform_subform.Form.Filter = "[InvoiceDate]
Between '" & Format(CDate([Me.txtDatefrom]), "mm/dd/yyy") & "' and '" &
Format(CDate([Me.txtDateTo]), "mm/dd/yyyy") & "'"
Me.qryOrdersforSearchSubform_subform.Form.FilterOn = True

Exit_cmdFilterByDate_Click:
Exit Sub

Err_cmdFilterByDate_Click:
MsgBox Err.Description
Resume Exit_cmdFilterByDate_Click

End Sub

--
Kind Regards,
Abdul Shakeel

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]