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

Kamis, 24 Mei 2012

RE: [MS_AccessPros] Filter subform between Two Dates

Abdul-

You have to look at strWhere AFTER you assign the filter to it.

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:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Filter subform between Two Dates

Dear John

Still the same problem, in debug mode when I select StrWhere it Shows

strWhere=""
InvoiceDate=05/23/12
txtDateFrom=05/01/12 (I have enter this date)
txtDateTo=05/31/12

On Thu, May 24, 2012 at 12:40 PM, John Viescas <JohnV@msn.com> wrote:

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

--
Kind Regards,
Abdul Shakeel

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

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

Yahoo! Groups Links