Solved

.filter syntax

Posted on 2014-09-12
9
190 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now