Solved

.filter syntax

Posted on 2014-09-12
9
191 Views
Last Modified: 2014-09-12
I am trying to apply a filter to a date field [ContactDate] on a datasheet subform based on a date field in the main form tmpDateFilter.

Both fields are date fields and there are definitely records that match the date entered on the main form.  I am trying the following but although the datasheet subform does filter - it returns an empty list.  Can anyone see what I have got wrong here.

    With Forms!CC_ContactRecords_MainForm!CC_ContactRecordsList.Form
        .Filter = "[ContactDate] = #" & Me.TmpDateFilter & "#"
        .FilterOn = True
    End With
0
Comment
Question by:PerksP
  • 4
  • 3
  • 2
9 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40319615
Try:

 .Filter = "[ContactDate] >= #" & Me.TmpDateFilter & "# AND " _
            & "[ContactDate] < #" & DateAdd("d", 1, cdate(me.TmpDateFilter)) & "#"

or:

.Filter = "DateValue([ContactDate]) = #" & me.TmpDateFilter & "#"
0
 

Author Comment

by:PerksP
ID: 40319639
Thank you

The first option still gives an empty list and I'm afraid that the second is giving a Data Type mismatch error.

The table that runs the main form is a local table, the table that runs the subform is a linked table.  Would this throw anything out?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40319673
Try building the criteria string separately, then printing it to the immediate window.

strCriteria = "DateValue([ContactDate]) = #" & me.TmpDateFilter & "#"
debug.print strCriteria
.Filter = strCriteria
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:PerksP
ID: 40319695
Thank you

However, that is also returning a data type mismatch error.  Did I put it in the right place?

Dim strcriteria As String
strcriteria = "DateValue([ContactDate]) = #" & Me.TmpDateFilter & "#"
Debug.Print strcriteria

    With Forms!CC_ContactRecords_MainForm!CC_ContactRecordsList.Form
        .Filter = strcriteria
        .FilterOn = True
    End With
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40319720
put a breakpoint on the line that starts:

With Forms!

Then you should be able to see what strCriteria looks like and determine what is happening.  

Are you certain that TmpDateFilter is a valid control name?
0
 

Author Comment

by:PerksP
ID: 40319752
Yes, I have checked and double checked that as I thought it must be where I was going wrong.

[ContactDate] = #12/09/2014#

this is what is showingi n the immediate window.  That is the same date as is in the tmpDateFilter field so it looks as though it is picking that up.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40319857
It's the format of the string where month and day gets mixed up.
This will Work:

    With Forms!CC_ContactRecords_MainForm!CC_ContactRecordsList.Form
         .Filter = "[ContactDate] = #" & Format(Me.TmpDateFilter, "yyyy\/mm\/dd") & "#"
         .FilterOn = True
    End With

or:

    With Forms!CC_ContactRecords_MainForm!CC_ContactRecordsList.Form
         .Filter = "[ContactDate] = DateValue(" & Me.TmpDateFilter & ")"
         .FilterOn = True
    End With

/gustav
0
 

Author Comment

by:PerksP
ID: 40319884
That is fantastic - thank you so much!

I would never have worked that out however many hours I had spent on it.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40319911
You are welcome!

/gustav
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question