Solved

Problem with date filter in MS Access 2010

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

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 48

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

744 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

11 Experts available now in Live!

Get 1:1 Help Now