Access 2003 - Report Page header date fields

I have a report based on a query. The query has 3 prompts, Start Date, End Date, Student Name. The query works fine. The report also works fine...until I try to add the Start and End dates the user entered into the report header. The query uses [Start Date], [End Date]. When I add a label at the top of the report that says TO: and a field that says [Start Date] and FROM: and a field that says [END DATE], when I run the report I now get prompted TWICE for the start and end dates. The report displays the correct data, and it has the correct dates at the top, but I don't want to be prompted twice for the dates. If I remove the two fields [Start Date] / [End Date] from the report header, it works fine. How do I reference these user enetred dates in the header and not get prompted twice.
thx experts...BobR
bobrossi56Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What table fields are being filtered by those user-entere values? In other words, when the user enters a Start Date, how is that information used at the table level?

Can you show us the SQL of that query?

Scott
Dale FyeOwner, Developing Solutions LLCCommented:
One way would be to use the Report_Load event.  Put a single label (lbl_Title) in the report header.

Then add a line of code in the Report_Load event, something like:

Private Sub Report_Load

    me.lbl_Title.Caption = "From:" & me.[Start Date] & " to " & me.[End Date]

End Sub

I actually have a function I call in cases like this.  To use it, you would use:

    me.lbl_Title.Caption = fnDateRange(me.[Start Date], me.[End Date)


Public Function fnDateRange(StartDate As Date, EndDate As Date) As String

    If StartDate = EndDate Then
        fnDateRange = Format(StartDate, "dd mmm yy")
    ElseIf Format(StartDate, "yyyymm") = Format(EndDate, "yyyymm") Then
        fnDateRange = Day(StartDate) & " - " & Format(EndDate, "d mmm yy")
    ElseIf Format(StartDate, "yyyy") = Format(EndDate, "yyyy") Then
        fnDateRange = Format(StartDate, "d mmm") & " - " & Format(EndDate, "d mmm yyyy")
    Else
        fnDateRange = Format(StartDate, "d mmm yyyy") & " - " & Format(EndDate, "d mmm yyyy")
    End If
    
End Function

Open in new window

bobrossi56Author Commented:
Scott...The field in ReportDate, and it is asking for the HORSE name, not the STUDENT name as I originally said, not that it makes any diff ....here is the SQL:

SELECT tblHorseAssign.ReportDate, tblHorseAssign.HorseName, tblHorseAssign.ReportedBy, tblHorseAssign.Paid, tblHorseAssign.PaidBy, tblHorseAssign.StartTime, tblHorseAssign.EndTime, tblHorseAssign.StudentName, 24*([EndTime]-[StartTime]) AS Hrs, [Enter Horse Name, leave blank for all] AS Expr1
FROM tblHorseAssign
WHERE (((tblHorseAssign.ReportDate) Between [Start Date MM/DD/YYYY] And [End Date MM/DD/YYYY]) AND (([Enter Horse Name, leave blank for all]) Is Null)) OR (((tblHorseAssign.HorseName)=[Enter Horse Name, leave blank for all]) AND (([Enter Horse Name, leave blank for all]) Is Not Null));
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Nick67Commented:
The best way to do this is a 'parameter' form.
Instead of [Start Date MM/DD/YYYY]  And  [End Date MM/DD/YYYY], which prompts the user, you build a small form, frmParameters that has two textboxes and a command button. txtStartDate, txtEndDate and cmdOpenTheReport.
You replace [Start Date MM/DD/YYYY]  And  [End Date MM/DD/YYYY] with
Forms!frmParameters!txtStartDate AND Forms!frmParameters!txtEndDate
and you use the command button to open the report.

Your header controls reference the same form controls as the query.
Many problems can be solved at once
You can check that the textboxes get proper dates before the report opens, which you can't with prompts
You never get prompted, never mind multiple times
You can check if the report would return no records and stop the report from even trying to open

'Parameter' forms are a very good practice.
bobrossi56Author Commented:
Nick67...I tried your suggesiton and I like it a lot. I actually added a combo box on the parameters form that pulls in the Instructor Names from tblInstructorNames and it passes whatever name selected to the query. Works GREAT!!! ONLY problem is, before I made these mods and I was just propmted for a date range and an instructors name I could leave the prompt for Instructor Name blank and get ALL the instructors. This feature no longer works. This is the SQL I was using before the frmparameter mod, and I just replaced the variables with the frmparameters variables.

SELECT tblHorseAssign.ReportDate, tblHorseAssign.HorseName, tblHorseAssign.ReportedBy, tblHorseAssign.Paid, tblHorseAssign.PaidBy, tblHorseAssign.StartTime, tblHorseAssign.EndTime, tblHorseAssign.StudentName, 24*([EndTime]-[StartTime]) AS Hrs, Forms!frmParameters!ComboInstructorName AS Expr1
FROM tblHorseAssign
WHERE (((tblHorseAssign.ReportDate)=[Forms]![frmParameters]![txtStartDate] And (tblHorseAssign.ReportDate)=[Forms]![frmParameters]![txtEndDate]) AND (([Forms]![frmParameters]![ComboInstructorName]) Is Null)) OR (((tblHorseAssign.ReportedBy)=[Forms]![frmParameters]![ComboInstructorName]) AND (([Forms]![frmParameters]![ComboInstructorName]) Is Not Null));

WHat can I dio to get the "leave blank" feature working again?
thx...Bob
Nick67Commented:
Instead of
AND (([Forms]![frmParameters]![ComboInstructorName]) Is Null)) OR (((tblHorseAssign.ReportedBy)=[Forms]![frmParameters]![ComboInstructorName]) AND (([Forms]![frmParameters]![ComboInstructorName]) Is Not Null));

try

AND tblHorseAssign.ReportedBy  = Nz([Forms]![frmParameters]![ComboInstructorName],"*")

You may need LIKE instead of =

AND tblHorseAssign.ReportedBy  LIKE Nz([Forms]![frmParameters]![ComboInstructorName],"*")



bobrossi56Author Commented:
Nick67...tried it both ways and each way gave me an error when I tried to save the query, sais a ) was missing....so I added a ) at the end:
AND tblHorseAssign.ReportedBy  = Nz([Forms]![frmParameters]![ComboInstructorName],"*"))

Ran the parameter form, left the Instructor name blank and the report returned no records. Then I picked a name from the drop down and the report still returned nothing.
Nick67Commented:
try
AND tblHorseAssign.ReportedBy  Like Nz([Forms]![frmParameters]![ComboInstructorName],"*"))
bobrossi56Author Commented:
Nope, same  outcome as the other attempts. Report returns no data left blank or when an instructor isa selected.
Nick67Commented:
I am working on it!
It's always a trick bit of syntax.
That syntax should have worked.

Here's a small shim to demo it.
I did three ComboBoxes, but the idea is the same
horse.mdb
Nick67Commented:
This should do it, unless your combo is returning an ID number or some other column and not text to match ReportedBy

SELECT tblHorseAssign.ReportDate, tblHorseAssign.HorseName, tblHorseAssign.ReportedBy, tblHorseAssign.Paid, tblHorseAssign.PaidBy, tblHorseAssign.StartTime, tblHorseAssign.EndTime, tblHorseAssign.StudentName, 24*([EndTime]-[StartTime]) AS Hrs, Forms!frmParameters!ComboInstructorName AS Expr1
FROM tblHorseAssign
WHERE (((tblHorseAssign.ReportDate) Between [forms]![frmParameters]![txtStartDate] And [forms]![frmParameters]![txtEndDate]) AND ((tblHorseAssign.ReportedBy) Like nz([forms]![frmParameters]![ComboInstructorName],"*")));


Throw
MsgBox [forms]![frmParameters]![ComboInstructorName] into the command button's code and ensure it's returning what you exepected

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobrossi56Author Commented:
Ureka!!!!! That was the magic syntax. Thanks for all the help and sticking with me.
Bob
bobrossi56Author Commented:
Thanks again!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.