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

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

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
0
bobrossi56
Asked:
bobrossi56
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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
 
bobrossi56Author Commented:
Ureka!!!!! That was the magic syntax. Thanks for all the help and sticking with me.
Bob
0
 
bobrossi56Author Commented:
Thanks again!!!!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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