Solved

.filter syntax

Posted on 2014-09-12
9
194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 40319911
You are welcome!

/gustav
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

732 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