Solved

access report with date criteria

Posted on 2013-06-10
7
644 Views
Last Modified: 2013-06-10
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
Comment
Question by:peispud
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39236468
Do you get an error message or a parameter input box when you run the report?
0
 
LVL 84
ID: 39236473
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
 

Author Comment

by:peispud
ID: 39236476
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39236490
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 39236820
> 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
 

Author Closing Comment

by:peispud
ID: 39236855
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39236866
You are welcome!

/gustav
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…

778 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