?
Solved

Problem with date filter in MS Access 2010

Posted on 2014-03-10
9
Medium Priority
?
2,030 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
[X]
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
9 Comments
 
LVL 52

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 46

Expert Comment

by:aikimark
ID: 39917217
try the mm/dd/yyyy format in your filter
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 52

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 52

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 38

Accepted Solution

by:
PatHartman earned 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses

764 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