Solved Add Start Date and End Date to Report Header as a Subtitle

[Khurramshaikh47]

I have a report that runs a query that asks you to select the Start Date and End Date. The report runs correctly and presents the proper information. What I would like to do is to have the dates that I run the report for to appear in the header of the report.

The following report is from 1-Jan-21 to 4-Jan-21

Please guide how to do this.

Thank you

 

[theDBguy]

When you say the report "asks" for the input dates, are you using a parameter query or a form?

 

[Khurramshaikh47]

When you say the report "asks" for the input dates, are you using a parameter query or a form?

I am using Parameter.

 

[Pat Hartman]

Use form fields. Have the user enter the two dates on the form, The button to run the report should validate the two dates and then open the report using the dates in the wHERE argument of the OpenReport method. The report itself can reference the form field to display the date.

Use the following as the control source for the start date and the same concept for the end date:
=Forms!YourFormName!txtStartDate

 

[theDBguy]

I am using Parameter.

Try using the parameters in the control source of an unbound Textbox in your report. For example,

=[Enter Start Date]

 

[Pat Hartman]

That will work but using parameters rather than form field references is problematic. For example, if you open the report in preview, you are prompted for the variables. If you then print the report, you are prompted again. That doesn't happen with form field references.

 

[gemma-the-husky]

you could have a textbox on your report bound to =reportdatestring()

Have a module like this

public reportdates(2) as date

function readreportdates(choose) as date
readreportdates = reportdate(choose)
end function

function reportdatestring as string
reportdatestring = "Report for dates from " & format(readreportdates(1),"mm/dd/yyyy") & " and " & format(readreportdates(2),"mm/dd/yyyy")
end function

then before you open the report simply this

reportdates(1) = #8/1/2021# 'or read it from a data picker, etc
reportdates(2) = #8/31/2021# 'or read it from a data picker, etc

 

[Khurramshaikh47]

Thank you very much to all of you! My issue has been resolved in Petty Cash Report by Creating TextBox with Control Source =Min([PaymentDate]) and =Max([PaymentDate]).

But the same problem is in another report Let me show you its Query:

The PaymentDate is not visible in my report . When I am trying to check in show box in my Query It showing me an error "Microsoft Access can't display the field for which you entered Where in the Total row."

When I am adding new PaymentDate field with checked show box result of my Query getting change.
Now how can I add Date Range on its title.

 

[theDBguy]

Hi. Looks like you didn't try my suggestion.

 

[Khurramshaikh47]

Hi. Looks like you didn't try my suggestion.

Yes my dear I've tried

But Its not showing proper date. Not showing year. I have to type complete date every time like: 1-Jan-21.
Is there any other solution?

 

[gemma-the-husky]

You could just include the payment date as a detail column, but make it not visible.

 

[theDBguy]

Yes my dear I've tried
View attachment 94768
But Its not showing proper date. Not showing year. I have to type complete date every time like: 1-Jan-21.
Is there any other solution?

Hi. Does that mean when you get the prompt, you only enter "1 Jan?"

 

[Khurramshaikh47]

You could just include the payment date as a detail column, but make it not visible.

Yes because Its the some of different dates by Head Of Account wise. But PaymentDate field was necessary because I want this report for a specific date range. And I also want that date range to show on the top of my report.

 

[Khurramshaikh47]

Hi. Does that mean when you get the prompt, you only enter "1 Jan?"

Yes some time I type just 1 Jan and I get the report according to date range.

 

[theDBguy]

Yes some time I type just 1 Jan and I get the report according to date range.

In that case, you could try this:

=[Enter Start Date] & " " & Year(Date)

 

[Khurramshaikh47]

In that case, you could try this:

=[Enter Start Date] & " " & Year(Date)

Will this show the current year?

 

[Khurramshaikh47]

In that case, you could try this:

=[Enter Start Date] & " " & Year(Date)

Asking for Date Parameter Value

 

[theDBguy]

Asking for Date Parameter Value

View attachment 94773

Try:

=[Enter Start Dat] & " " & Year(Date())

 

[theDBguy]

Try:

=[Enter Start Dat] & " " & Year(Date())

Hi. Sorry, better yet, try this one.

=Format([Enter Start Date], "d mmm yyyy")

 

[Khurramshaikh47]

Hi. Sorry, better yet, try this one.

=Format([Enter Start Date], "d mmm yyyy")

Thank you so much

is there any way to 1-Jan-2021?