Solved

access report with date criteria

Posted on 2013-06-10
7
638 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now