Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

access report with date criteria

Hi

I am using Access 2010.   I am trying to get my report
The string varaible "ReportFilter" evaulates as follows.

"[Date of Sale] between #01/05/2013# and #31/05/2013#"

Dim ReportFilter As String
ReportFilter = "[Date of Sale] between #" & TheDates(0) & "# and #" & TheDates(1) & "#"
DoCmd.OpenReport "rpt PDC Sales by Date", acViewPreview, , ReportFilter, OpenArgs:=strInfo

Open in new window


I am trying to control the beginning and end dates for this report (as you can see).  The date criteria seem to have no effect on my report output.  

I would appreciate any help.
0
peispud
Asked:
peispud
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
pdebaetsCommented:
Do you get an error message or a parameter input box when you run the report?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is the report grouped, or in some way otherwise filtered? I don't see a problem with your code (for UK dates, that is).

Do you have code in the Report Open event?
The date criteria seem to have no effect on my report output.  
If you apply the exact same filter to the query driving your report, does it then show the expected output?
0
 
peispudAuthor Commented:
I just tried the following.

  ReportFilter = "[Date of Sale] between #" & Format(TheDates(0), "mm/dd/yyyy") & "# and #" & Format(TheDates(1), "mm/dd/yyyy") & "#"

I am analyzing the results,  but so far this seems to work.

Was my date format the problem in my initial post?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're formatting the dates for US-based dates with that Format string. Is the code running on a machine with US dates, or with UK dates?

Either way, you should always format your dates to insure accuracy.
0
 
Gustav BrockCIOCommented:
> Was my date format the problem in my initial post?

Yes. You could turn into a habit using the ISO sequence as that works for ADO FindFirst as well and never fails. And use the escape character to force a forward slash as it otherwise will be replaced with the localized date separator (which, of course, may happen to be a forward slash but you never know):

ReportFilter = "[Date of Sale] between #" & Format(TheDates(0), "yyyy\/mm\/dd") & "# and #" & Format(TheDates(1), "yyyy\/mm\/dd") & "#"

/gustav
0
 
peispudAuthor Commented:
Point taken.  I've never liked working with dates due to this type of problem.   Now this issue is behind me.  In the future,  I will always format my dates in the ISO sequence with escape characters.

Thank you.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now