Solved

Problem with date filter in MS Access 2010

Posted on 2014-03-10
9
1,758 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
 
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
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.

 

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 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

920 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

14 Experts available now in Live!

Get 1:1 Help Now