Solved

access report with date criteria

Posted on 2013-06-10
7
671 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
[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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 50

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 50

Expert Comment

by:Gustav Brock
ID: 39236866
You are welcome!

/gustav
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

752 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