Solved

Problem with date filter in MS Access 2010

Posted on 2014-03-10
9
1,804 Views
Last Modified: 2014-03-13
I am having a nightmare trying to get a date filter to work. I have a date field with a date picker that sets a value I then use the following to try and filter records for the specified date

strMailDate = Format(Me.MailDate, "dd/mm/yyyy")

Me.Filter = "[DateSent] = " & strMailDate
Me.FilterOn = True

Open in new window


Can anyone tell me what I am missing. If I put a MsgBox in to check the value of strMailDate it appears to show the correct value. However, the filter does not work. If however I use

Me.Filter = "[DateSent] = Date()"
Me.FilterOn = True

Then it does return the correct records. I suspect this is some sort of formatting issue. I need to pass a value from a date field set by a date picker to the filter.
0
Comment
Question by:martmac
9 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39917187
Hi,

pls try

Me.Filter = "[DateSent] = " & "#" & strMailDate & "#"

Open in new window

Regards
0
 

Author Comment

by:martmac
ID: 39917210
Sadly no joy

strMailDate = Me.MailDate
MsgBox strMailDate

Me.Filter = "[DateSent] = " & "#" & strMailDate & "#"
Me.FilterOn = True

Open in new window


The MsgBox is giving me the correct value which appears also to be formatted correctly.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39917217
try the mm/dd/yyyy format in your filter
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39917224
Hi,
pls try

strMailDate = Format(Me.MailDate, "\#mm\/dd\/yyyy\#" )

Me.Filter = "[DateSent] = " & strMailDate
Me.FilterOn = True 

Open in new window

Regards
0
 

Author Comment

by:martmac
ID: 39917235
Type mismatch in line 1
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39917246
then try

strMailDate = Format(Me.MailDate, "mm/dd/yyyy" )

Me.Filter = "[DateSent] = " & "#" & strMailDate & "#"
Me.FilterOn = True 

Open in new window

Regards
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39917292
I'd always convert to UTC format to avoid an ambiguity in date formats:
    Dim filter As String
    filter = Format(Me.MailDate, "yyyy-MM-dd")

    Me.Filter = "[DateSent]=#" & filter & "#"
    Me.FilterOn = True

Open in new window

0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39917630
When you Format() a date, you convert it to a string.  That makes it NOT a date.  Formatting is for humans, not computers.  Never Format() a date when you are using it for sorting or to compare to another date.

The reason that the filter is not working is probably because the MailDate includes time.  If you used Now() to set the value for MailDate, you may want to switch to using Date() which is only the date part without time.

You can use DateValue(MailDate) to extract only the date portion or you can use a range selection -- MailDate >= SomeDate And MailDate < SomeDate + 1

Check the table definition to ensure that MailDate is in fact defined as a date.
0
 

Author Closing Comment

by:martmac
ID: 39927057
Very helpful as it goes to the root of the problem. Back end is SQL server and the problem is coming from the date format at the back end.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Programmer 14 49
Any Way to Print an Import Spec? 3 30
Access 2007 - Copy One Row From Multicolumn Listbox to Another Listbox 3 32
DCount Type Mismatch 2 22
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

773 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