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
Solved

.filter syntax

Posted on 2014-09-12
9
192 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

856 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