Values returned by expressions based on changing dates

[Guest]

I have a report that I generate each month and there are certain fields that
return a value based on start and end dates.

What I would like to do is set the date values, in a table for example and
then in the query look to that table for the criteria.

I have a couple of queries that call the same information (i.e. the start
and end dates) and rather than having to change the criteria in each query I
would like to set the dates and have the queries look for the values each
time the report is run.

See below an example of on of the query columns that returns a value based
on the date criteria.

Open/Closed Status: IIf([DateClosed] Is Null Or [DateClosed] Between
#03/03/2006# And #03/31/2006,"OPEN","CLOSED")

Please help

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Set up the query w/ form reference Parameters. E.g.:

PARAMETERS Forms!CriteriaFormName!txtStartDate DateTime,
Forms!CriteriaFormName!txtEndDate DateTime;
SELECT ...
FROM ...
WHERE date_column BETWEEN Forms!CriteriaFormName!txtStartDate AND
Forms!CriteriaFormName!txtEndDate
.... etc. ...

Set up a criteria form that has 2 TextBoxes for the Start and End dates.
Put 2 CommandButtons on this form: OK and Cancel. Here are the Click
events for each button:

Private Sub cmdOK_Click()

' have some code to check that both dates are present
' make your own dataOK() function - returns True if
' all required data is present on the form
If dataOK Then
' Visible = False hides the form so the report can continue
Me.Visible = False
End If

End Sub

Private Sub cmdCancel_Click()
' just close the form - the report will see the criteria
' form is closed & cancel the report
DoCmd.Close acForm, Me.Name
End Sub


In the report's Open event put something like this:

Private Sub Report_Open(Cancel As Integer)

' Open the criteria form in Dialog mode so this VBA code
' will wait until the criteria form is inVisible or closed.
DoCmd.OpenForm "CriteriaFormName", WindowMode:=acDialog

If Not CurrentProject.AllForms("CriteriaFormName").IsLoaded Then
' The criteria form is closed - cancel the report
Cancel = True

' Else
' otherwise the criteria form is inVisible (OK was clicked) and
' has the dates & now the report can run. The query uses the
' dates in the criteria form to filter the data.
End If

End Sub

If you want the criteria dates to show on the report just create a
TextBox for each date w/ a ControlSource like this:

=Forms!CriteriaFormName!txtStartDate

Format the TextBox as a Date.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDWxkYechKqOuFEgEQI+hACgiRQNtmnHvmPNYqDlVOqNHKBslF0AoMnQ
VDRsHy6I9YNR6kmPIPyZB30m
=XYiC
-----END PGP SIGNATURE-----

I have a report that I generate each month and there are certain fields that
return a value based on start and end dates.

What I would like to do is set the date values, in a table for example and
then in the query look to that table for the criteria.

I have a couple of queries that call the same information (i.e. the start
and end dates) and rather than having to change the criteria in each query I
would like to set the dates and have the queries look for the values each
time the report is run.

See below an example of on of the query columns that returns a value based
on the date criteria.

Open/Closed Status: IIf([DateClosed] Is Null Or [DateClosed] Between
#03/03/2006# And #03/31/2006,"OPEN","CLOSED")

Please help