Solved

access report with date criteria

Posted on 2013-06-10
7
623 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

708 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

15 Experts available now in Live!

Get 1:1 Help Now