DMax formula in Report Header 1

[awl]

I created a report listing all completed and uncompleted courses for an employee; derived from a query. The query and the report when is first opened, has an input dialog box, “Enter Parameter Value”, which is the employee ID#. All completed courses have a completion date. The report is sorted first with completed courses, with the subjects in alphabetical order with their corresponding completion dates, and then the uncompleted courses (no dates) sorted alphabetically. In my Page Header of the report I have a text box labeled [Updated:]. The calculated control result I want displayed is the most current date of the completed dates listed in the report, which is the last date a course was taken. I have used a Dmax formula below, however, I always get an “#Error” displayed. The formula used: =DMax("[DateCompleted]","qryIndEmployeeCompDelReq”, “[DateCompleted]=[Updated:]”). The first part is the expression which I want to display the date completed; the second is the query that the data is from; the third is the criteria which is the most current date listed in the query. Thanks for anyone whom could help. (If I may address this issue to Mr. Joe Miller, whom responded to a "DMax" question from another user on June 27, 2001.) Curtis....

 

[CosmoKramer]

Did you try moving this control to the Report Footer? And did you try enclosing the date field with #s?

=DMax("[DateCompleted]","qryIndEmployeeCompDelReq”, “[DateCompleted]=#" & [Updated:] & "#”)

 

[awl]

Mr. Cosmo Kramer: Thank you for your quick response. If you don't mind, I will have to wait until Monday, August 6th for an honest response, for the database is located at my office; I won't be going into the office until Monday. I will try your 2 recommendations and respond back to you. Thank you. curtis....

 

[awl]

Mr. CosmoKramer: I have tried both of your recommendations, however, the #Error message is yet displayed. If you would prefer, I could send a "Print Screen Shot" of the Expression Builder and the property sheet for this calculated control. Thanks for your help.... curtis....

 

[JoeMiller]

The problem is because the query you are trying to pull a DMax from is a parameter query. When the DMax is evaluated, it is as if you are just opening the query in the db window, and since the computer can't "answer" the [Updated:] question, it returns no results (or #Error). In order to successfully do this, i would suggest that you make a form where the user can call the report and have them enter the Updated date on the form and make the query look at the form for it's parameter.

So let's say you have a form called frmReports, with a textbox called txtUpdated as the date for the query. In the query replace [Updated:] with:

[Forms]![frmReports]![txtUpdated]

Then you can run the DMax in your header, and since the [Updated:] value restricts the form automatically you can change the control source to this:

=DMax("[DateCompleted]","qryIndEmployeeCompDelReq”)

HTH Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

The problem is because the query you are trying to pull a DMax from is a parameter query. When the DMax is evaluated, it is as if you are just opening the query in the db window, and since the computer can't "answer" the [Updated:] question, it returns no results (or #Error). In order to successfully do this, i would suggest that you make a form where the user can call the report and have them enter the Updated date on the form and make the query look at the form for it's parameter.

So let's say you have a form called frmReports, with a textbox called txtUpdated as the date for the query. In the query replace [Updated:] with:

[Forms]![frmReports]![txtUpdated]

Then you can run the DMax in your header, and since the [Updated:] value in txtUpdated on your form restricts the query automatically you can change the control source in your report to this:

=DMax("[DateCompleted]","qryIndEmployeeCompDelReq”)

HTH Joe Miller
joe.miller@flotech.net

 

[awl]

Mr Joe Miller: Thank you for your response. Just to clarify your recommendation, if I may: 1) what would be the ControlSource for the frmReports? 2) Are the contents of the frmReports the same as the contents in the rptIndEmployeeCompDelReq? 3) Or what is the ControlSource for the textbox called txtUpdated in the frmReports if that is the only item on the form? Something I had not mentioned, in my qryIndEmployeeCompDelReq, the field I have for the dates is titled [DateCompleted] instead of [Updated:]. I have only used the word “Updated:” as the name and caption for the label in the rptIndEmployeeCompDelReq; the textbox name is “Updated Date” with its control source: [qryIndEmployeeCompDelReq]. I apologize if I’m too detailed in my questions; I’m still new at Microsoft Access. Thanks for your patience. Curtis

 

[JoeMiller]

1) frmReports has no control source, it is an unbound form which is used to capture information from the users. In this case the date you're after.

2) No they are not, you only need 1 date field, and 1 command button to openthe report.

3) There is no control source for txtUpdated, but make sure that you set the format of the text box to Short Date.

Joe Miller
joe.miller@flotech.net

 

[awl]

Mr. Joe Miller: Good morning. Thank you for your suggestions; I’m getting a date, however, not the result I’m after. 1) I created an unbound textbox which is the “one date field” with a command button to open the rptIndEmployeeCompDelReq in the frmReports. 2) In the qryIndEmployeeCompDelReq, I added the field: Updated: [Forms]![frmReports]![txtUpdated]. When I run the query, two “Enter Parameter Value” dialog boxes prompt information: the 1st one is asking for a date in the [Forms]![frmReports]![txtUpdated] parameter value, the 2nd is the StudentsID. I enter “a date” and the [StudentID#]; the results displayed is the 11 fields of appropriate data for each student’s completed courses with the [DateCompleted], no dates for the uncompleted courses, along with the date I entered in the 1st parameter in a new column. 3) When I enter the frmReport, I type in “a date”, press the Command Button, the StudentsID parameter value box opens, I type in the [StudentsID#], the rptIndEmployeeCompDelReq then opens. 4) I could not use the new DMax shorten formula for I still kept getting an #Error displayed, even with the new changes above. To get a date, the control source I then used: =[Forms]![frmReports]![txtUpdated]. When the report opens, the date from the frmReports is displayed in the Report Header. I then went back to the query, deleted the new field [Updated:……] I created, since the date data in the report was coming from the date input in the form. Question: a). When I exit and go back into the frmReport, the date entry blank. Is this correct? b). Why should I have to manually search the query data displayed, (71 records of 1 student’s completed/uncompleted courses) for the latest date a class was taken of the completed courses, and then manually enter that date in the frmReport, to produce a report that I want to show upfront in the Report Header, a date when the last class was taken. I was hoping to use a function, rather than setting up another parameter query to obtain the most recent date from a list of 25 unsorted dates. Thanks for your help. Curtis….

 

[JoeMiller]

Delete the new column that you made in your query. On frmReports (the form with the one date), add another textbox called txtStudentID (this will hold your student ID).

Then we go back to the query to add the proper criteria to the student id field and the field we want to get a certain date on. In the CRITERIA of student id delete what you have for criteria now and place this:

[Forms]![frmReports]![txtStudentID]

this cause the query to look at the FORM for it's criteria rather than providing a popup when you run the query.

In the criteria for the date field, place this:

[Forms]![frmReports]![txtUpdated]

which will again cause the query to look at the form for it's criteria. Hopefully we've got it now. Joe Miller
joe.miller@flotech.net

 

[awl]

Mr. Joe Miller: We are getting closer. I added the new textbox in the frmReports. In the query I changed the Criteria for the StudentID, deleted the old column, and created a new column titled: Updated: [Forms]![frmReports]![txtUpdated]. (Note: Before entering a Criteria in the new column, I had to have a field name. So, I put the criteria in the field row. Does this work the same as if I used the Criteria row? If not, what would be an appropriate field name?)

Now, two things occur depending if I run the query or use the form. Query: Two parameter value window prompts input for the [Forms]![frmReports]![txtUpdated]” and [Forms]![frmReports]![StudentID]” If I skip the data entries, there are no results displayed. Form: This works if: I enter the Date of the Query and Student ID, press the Command Button, and the report is automatically displayed, with the date I manually typed into the form. The tried using the shortened Dmax formula for the ControlSource in the report with no luck; I had to resort to the current formula: =[Forms]![frmReports]![txtUpdated] to get a date to be displayed. However, the desired result is not quite there yet. (Note: In my query, I have 11 fields. One of them is the [DateCompleted] field which lists those subjects that have a completed date entrance in the table, which is entered via a separate form. The user may not print the student’s report the same day as the changes were made to the table. A week or two goes by, the user then needs to print the student’s report. The user then has to somehow know what date was the last class taken for that student, then enter “that date” in the frmReports to produce the report. I’m trying to find a way, or a function, to automatically determine from the list of unsorted 25 dates, which of those dates is the most current). Hoping this is helpful. Curtis….

 

[JoeMiller]

Can you send me a copy of your database? We're going in circles and I'd like to show you what I mean.

Joe Miller
joe.miller@flotech.net

 

[awl]

Mr. Joe Miller. Yes, I have already sent a short version of the database. Thanks. Curtis...