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?
 
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
0
 
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
0
 
Dale FyeCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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));
0
 
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.
0
 
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
0
 
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],"*")



0
 
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.
0
 
Nick67Commented:
try
AND tblHorseAssign.ReportedBy  Like Nz([Forms]![frmParameters]![ComboInstructorName],"*"))
0
 
bobrossi56Author Commented:
Nope, same  outcome as the other attempts. Report returns no data left blank or when an instructor isa selected.
0
 
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
0
 
bobrossi56Author Commented:
Ureka!!!!! That was the magic syntax. Thanks for all the help and sticking with me.
Bob
0
 
bobrossi56Author Commented:
Thanks again!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.