Parameter Query - Putting Dates in the report 3

[Tamrak]

This post contains two sections but they are related to each other.

Section 1:

I have a table that contents the "date" field. When I run a parameter query, Between [Enter the date] and [enter the date], all values came up.

I have some questions:

1. Will the value entered, start and end, must be in the same format in the table structure? For example, in the table structure, the date field was set at 99/99/9999. If a user input in the 99/99/99 format, will it return every records?

Section 2: Report

1. I created a report based on the query provided. However, I would like the value of the parameter query (date) appeared in the report header. Is it possible?

For example: My report header says:

Sales information

I would like the following lines to have the start date and the end date based on the value enter at the prompt. For example, I enter 01/01/2001 (start) and 02/15/2001 (end), these values will show in the report header.

All advises will be very much appreciated. Thank you.

P.S. Please be specific, if I need to adjust something in the report format. I am very novice to Access. Thank you.

 

[MichaelRed]

1st part. No. BUT you need to be careful in the handling of the dates. If you enter only the datepart (mm/dd/yy or equiv), then the time part defaults to Midnight ((12:00:00 AM), so the end date must be set to the FOLLOWING date to get all of the records with the same datepart.

2nd Part. Several methods. First Method (easiest, but subject to some "Uglies&quot. Just place the date (parameter)fields in the Report Header. Works fine for MOST occassions. If the query does not return any records, places "#Name?" in the report. Next Method. (Harder but prettier). Use a form to launch the report (this is ALWAYS a good idea). Reference the Form fields in the report for the dates. Primary dis-advantage is that SOMEONE - who THINKS they know all about MS Access will try to open the report directlty and 'cry' about the error.

Why do I say " ... always a good idea ... ". Simply, you can check user input for problems and supply derfalts. e.g. You could set up your report to normally work on a week or month basis. The USER enters one date - you calculate the other. The User enters a start date after the end date. You trap the problem and alert the user to the problem, clear the date fields and return to the form. These possabilities are almost endless. You could have only one date (Start date) and an option group for the interval (days | Weeks | Months | Years - - check for non-negative numeric value in another 'textbox' and calculate the end date).


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Tamrak]

Mike,

Thank you for the post. In a meantime, I had made it work based on your input.

 

[pdupreez]

You could of course create a piece of code in the On Load event directly in the report, thus cancelling out any external forms. I have used the attached code for achieving the above. Please note I am not a programmer, but a frustrated user, thus my coding technique is not too bright:

Option Compare Database
Option Explicit
Dim TABLENAME, ENDINGDATE, STARTDATE As String
Dim ACCPERIOD, MNTHPERIOD, YEARPERIOD, PREVPERIOD, PREVYEAR As String

Private Sub Report_NoData(Cancel As Integer)
MsgBox "NO DATA AVAILABLE"
End Sub

Private Sub Report_Open(Cancel As Integer)
ACCPERIOD = InputBox("Input Accounting Period", "Input Data&quot
MNTHPERIOD = Left(ACCPERIOD, 2)
YEARPERIOD = Right(ACCPERIOD, 4)
If MNTHPERIOD = "01" Then
PREVPERIOD = "12"
PREVYEAR = Val(YEARPERIOD) - 1
Else
PREVPERIOD = Val(MNTHPERIOD) - 1
PREVYEAR = YEARPERIOD
End If
ENDINGDATE = Format("25/" & MNTHPERIOD & "/" & YEARPERIOD, "DD-MMM-YYYY&quot
STARTDATE = Format("26/" & PREVPERIOD & "/" & PREVYEAR, "DD-MMM-YYYY&quot

Me.RecordSource = &quot;SELECT * FROM QRYSAMPLES WHERE (PackingDate >= #&quot; & STARTDATE & &quot;# And PackingDate <= #&quot; & ENDINGDATE & &quot;#)&quot;

End Sub

To use the parameters (startdate and enddate in my case) I create a public string ie.

Public HEADER as string in the declaration section of the report, and then I format the string as required. In your case I would type :

HEADER = STARTDATE & &quot; TO &quot; & ENDDATE

In the report I create a text box in the report header section, and I enter the following into it DataSource Property:

=&quot;SALES INFORMATION FOR &quot; & [header], where it would display something like:

&quot;SALES INFORMATION FOR 01/12/01 TO 01/01/02&quot; (DD/MM/YY FORMAT)

Hope you can make use of this!

Pieter