[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Max date in page header of an Access report

I have an Access report whose recordsource is a query. I'm trying to use the max date function on MyDateField in the page header section of the report.  When I do, I get an error. in the detail section of the report, it works fine but using it in a textbox in the page header section is producing an error. What am I doing wrong?

=Max([MyDateField])    <--- this is what the control source of my textbox looks like.
0
dbfromnewjersey
Asked:
dbfromnewjersey
  • 6
  • 4
1 Solution
 
Jeffrey CoachmanCommented:
You need to get this from the query itself.

=Dmax("MyDateField","YourQuery")
0
 
dbfromnewjerseyAuthor Commented:
I'm still getting an error.  Put both in quotes just as you indicated?
0
 
Jeffrey CoachmanCommented:
Or you could put this in a hidden control (txtReportHeaderMaxDate) in the *Report* header
=Max(YourDate)

Then reference the Report header control as the control source of a textbox (txtPageHeaderMaxDate) in the in the *Page* header
=txtReportHeaderMaxDate

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jeffrey CoachmanCommented:
<Put both in quotes just as you indicated?>
Yes, obviously using your unique names instead of my generic names

0
 
Jeffrey CoachmanCommented:
0
 
dbfromnewjerseyAuthor Commented:
I've attached a database.  When you open it, click on MyReport.  When you do, you'll be prompted to enter a starting date.  Put something like 6/1/10 in it.  When the report opens, you'll see errors in the two textboxes in the page header section. I attempted two different versions of getting max date in the page header section. Neither one is working.  I also put them in the detail section of the report. One version works in the there, the other doesn't. What am I doing wrong? I'm trying to be able to display the max date in a textbox in the page header section.
TestDataBase.mdb
0
 
dbfromnewjerseyAuthor Commented:
Sorry.  I uploaded the wrong database. This is the one you should look at.
TestDataBase.mdb
0
 
Jeffrey CoachmanCommented:
<click on MyReport.  When you do, you'll be prompted to enter a starting date.  Put something like 6/1/10 in it.>
Then this is the issue, ...you cannot do this with a parameter query as the source for the report.

You did not mention anything about this in your original post...

As you can see, this worked fine in the sample I posted for you...


At this point in your career you should really be seeking to move away from parameter prompts in your recordsource queries.

For one, they do not provide any validation for your parameters, so users can (and will) start trying to type in anything that even slightly resembles a date
(17/17/2011, 5/2010, 01/01/011, 2007, ...etc)
Parameter also do not allow the report to be simply opened on it's own (without any prompts) and display All of the records.

You should deleting the parameters from your queries and creating forms with textboxes/comboboxes to let users enter the criteria.
Then simply use these values to open the report filtered, by running code like this on a button on the form.
DoCmd.OpenReport "MyReport", acViewPreview, , "MyDateField>=" & "#" & Me.txtStartDate & "#"

This should then work fine for you using either technique to get the Max date.

;-)

JeffCoachman



0
 
dbfromnewjerseyAuthor Commented:
OK thanks.
0
 
Jeffrey CoachmanCommented:
;-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now