Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DoCmd.OpenReport with Where Condition on Date

Posted on 2010-11-17
14
Medium Priority
?
2,134 Views
Last Modified: 2012-05-10
I'm working in VB2008, but using Microsoft.Office.Interop.Access.Application to print reports from Access.

I have a report with a query as the recordsource. I'm trying to fill my report based on a date field (RptDate) in the query. I have tried many different ways to pass the date parameter in the "Where condition" to the report, but without success. The report is printed either with all records in the query or no records.

For example, one of the attempts:

Dim Date1 As String
Date1 = dtp1.Value.ToShortDateString()
DoCmd.OpenReport "rptReport", AcView.acViewNormal, ,  "RptDate= #" & Date1 & "#"

This example results in a report showing all records in the query instead of only records with the date requested.

Can someone provide the correct method to use?
0
Comment
Question by:Tim313
[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
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1000 total points
ID: 34158180
Try using this format for date
Date1 = Format(dtp1.Value, "yyyy-mm-dd")
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 34158221
You must also sometimes use a Formatted field in the report's query to be able to fully compare dates. For example, instead of this:

SELECT CustID, CustName, OrderDate FROM SomeTable

You'd use

SELECT CustID, CustName, Format(OrderDate, 'mm/dd/yyyy') As MyOrderDate FROM SomeTable

And then you'd filter like this:

Dim Date1 As String
Date1 = Format(dtp1.Value.ToShortDateString(), "mm/dd/yyyy")
DoCmd.OpenReport "rptReport", AcView.acViewNormal, ,  "MyOrderDate= #" & Date1 & "#"

There's also DateSerial you can use to build your data correctly.


0
 
LVL 31

Expert Comment

by:hnasr
ID: 34158904
Set a break point on this line
DoCmd.OpenReport "rptReport", AcView.acViewNormal, ,  "RptDate= #" & Date1 & "#"

When the program stops at the line, type ? Date1 in immediate window.
Compare the format you get with that of SomeTable, by showing the table in datasheet view.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 48

Expert Comment

by:Dale Fye
ID: 34159226
What regional settings for dates are you using?  in your query, you need to make sure the date is formatted as "mm/dd/yyyy", as LSM has indicated in his examples.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34159321
fyed

Why not the first comment, which uses yyyy-mm-dd and will work regardless of regional setting?
0
 

Author Comment

by:Tim313
ID: 34159924
With dtp1.Value = 11/16/2010 and Dim Date1 As String, ? Date1 shows "11/16/2010"
With dtp1.Value = 11/16/2010 and Dim Date1 As Date, ? Date1 shows #11/16/2010#
The "RptDate" column in the table (datasheet view) shows mm/dd/yyyy
The format for the data table field "RptDate" and the "dtp1 are both set to ShortDate.
 
If I add the WHERE clause to the query (example: WHERE tblReport.RptDate=#11/16/2010#), the query returns only records with RptDate = 11/16/2010...

Any ideas?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34159990
> The format for the data table field "RptDate" and the "dtp1 are both set to ShortDate.

That doesn't matter one bit.  The format only applies to display when used in reports, or in Access datasheet.  In a WHERE clause, it works on the date "value", some internal numeric.

> If I add the WHERE clause to the query (example: WHERE tblReport.RptDate=#11/16/2010#), the query returns only records with RptDate = 11/16/2010...

What is the problem with that statement please?
0
 
LVL 85
ID: 34160150
"If I add the WHERE clause to the query (example: WHERE tblReport.RptDate=#11/16/2010#), the query returns only records with RptDate = 11/16/2010..."

Isn't that what you would expect?


0
 

Author Comment

by:Tim313
ID: 34160646
Yes, what I would expect... just to confirm the query works given the date parameter. It also works if I use: WHERE tblReport.RptDate = ? and 11/16/2010 is entered into the "Enter Parameter" box. Why doesn't the report return the same results using the DoCmd.OpenReport statement? My query returns all records for the fields I need in the report, I thought the report would then filter the applicable records based on the "Where Condition" of the DoCmd.OpenReport statement.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34160664
Tim313, based on the first comment, have you tried this?

Dim Date1 As String
'Date1 = dtp1.Value.ToShortDateString()
Date1 = Format(dtp1.Value, "yyyy-mm-dd")
DoCmd.OpenReport "rptReport", AcView.acViewNormal, ,  "RptDate= #" & Date1 & "#"

Yes, your expectation would be correct, the criteria makes up a WHERE clause for the recordset behind rptReport.
0
 

Author Comment

by:Tim313
ID: 34160863
Thanks to all for your response. I'm home now and won't return to work until 6AM EST. I'll try your suggestions first thing...
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 34160876
cyber,

my bad.  This is the second time in the last month that an assumption I had about dates proved to be incorrect.
0
 

Author Comment

by:Tim313
ID: 34163469
LSM - Regarding your suggestion:

>You'd use
>SELECT CustID, CustName, Format(OrderDate, 'mm/dd/yyyy') As MyOrderDate FROM SomeTable
>And then you'd filter like this:
>Dim Date1 As String
>Date1 = Format(dtp1.Value.ToShortDateString(), "mm/dd/yyyy")
>DoCmd.OpenReport "rptReport", AcView.acViewNormal, ,  "MyOrderDate= #" & Date1 & "#"

With dtp1.value = 11/16/2010
Returns Date1 = "mm/dd/yyyy" and causes error.
I made the following change:
from -  Date1 = Format(dtp1.Value.ToShortDateString(), "mm/dd/yyyy")
to -  Date1 = Format(dtp1.Value, "mm/dd/yyyy")
which returned Date1 = #29/16/2010#
also on subsequent attempts the "mm" would change (ie 54, 59)

cyber- Regarding your suggestion:

>Dim Date1 As String
>'Date1 = dtp1.Value.ToShortDateString()
>Date1 = Format(dtp1.Value, "yyyy-mm-dd")
>DoCmd.OpenReport "rptReport", AcView.acViewNormal, ,  "RptDate= #" & Date1 & "#"

With dtp1.value = 11/16/2010
Similar results were returned having the "mm" change with each try.

Each time I ran the code the resulting value of "mm" in "mm/dd/yyyy" or "yyyy-mm-dd" would coinside with the minutes shown on my system's clock...

0
 

Author Comment

by:Tim313
ID: 34165273
Considering the problems I was having with this report, I decided to load a backup of this program that was saved prior to the creation of the report in question. I re-created the query and report and the problem did not reappear. This is not the first time I've had to resort to a backup & rewrite for some strange problem. I suspect I need to reinstall the VS2008 application...

Although LSM Consulting & cyperkiwi did not supply a direct solution, trying your suggestions lead me to believe that the problem required a rewrite. I'm splitting the points.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

609 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