[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Problem with date filter in MS Access 2010

Posted on 2014-03-10
9
Medium Priority
?
2,116 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 53

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

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 53

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 39

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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