Getting dates to format on a report

[cer5yc]

I have a report that is pulled from a parameter query (based on a date field). So the user clicks a Preview Report button on a switchboard form and then enters a report start date and a report end date. The parameter is as follows:

WHERE (((PO.Date)>=[Enter Report Start Date] And (PO.Date)<=[Enter Report End Date]));

I want the start date and end date the user enters to show at the top of my report.

For instance if the user enter a start date of 1/1/2007 and an end date of 10/31/2007, I want the report to show:

January 1, 2007 - October 31, 2007

Does anyone have any idea on how I can do this?

 

[SkipVought]

Hi,

Check out the Format function.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[genomon]

Use the FORMAT function. Can't remember for sure, but I think the format string is something like "mmmmdyyyy".

Always remember that you're unique. Just like everyone else.

 

[cer5yc]

I can use the FORMAT function to get the date formatted properly, my problem is I can't figure out how to get the report to pull the values the user enters into the parameter boxes

Here's some further details....

The report pulls info from two unrelated tables, so the record source for the report is a join query. The SQL is as follows:

SELECT PO.POID, PO.PrimaryLOB, PO.ProjectName, PO.[Term of Contract], PO.[Contract Life Spend], PO.[CY Spend], PO.[CapBudgeted Amount], PO.[OpBudgeted Amount], PO.BudgetCenterNumber, PO.GLAccountNumber, PO.[CapInitial Vendor Pricing], PO.[OpInitial Vendor Pricing], PO.[CY CapBud PL], PO.[CY OpBud PL], PO.[CY CapNBud PL], PO.[CY OpNBud PL], PO.[CY Total PL], PO.[CY Cash Save], PO.[FY Total P/L], PO.[FY Cash Save], PO.Date, PO.[FYCapFinal Vendor Pricing], PO.[FYOpFinal Vendor Pricing]
FROM PO
WHERE (((PO.Date)>=[Enter Report Start Date] And (PO.Date)<=[Enter Report End Date]))
UNION SELECT [Financial Data].ID, Data.[Primary LOB], Data.[Project Name], [Financial Data].[Term of Contract], [Financial Data].[Contract Life Spend], [Financial Data].[CY Spend], [Financial Data].[CapBudgeted Amount], [Financial Data].[OpBudgeted Amount], Data.[Budget Center Number], Data.[GL Account Number], [Financial Data].[CapInitial Vendor Pricing], [Financial Data].[OpInitial Vendor Pricing], [Financial Data].[CY CapBud PL], [Financial Data].[CY OpBud PL], [Financial Data].[CY CapNBud PL], [Financial Data].[CY OpNBud PL], [Financial Data].[CY Total PL], [Financial Data].[CY Cash Save], [Financial Data].[FY Total P/L], [Financial Data].[FY Cash Save], [Financial Data].Date, [Financial Data].[FYCapFinal Vendor Pricing], [Financial Data].[FYOpFinal Vendor Pricing]
FROM Data RIGHT JOIN [Financial Data] ON Data.ID = [Financial Data].[Contract ID]
WHERE ((([Financial Data].Date)>=[Enter Report Start Date] And ([Financial Data].Date)<=[Enter Report End Date]));

As I stated before the user gets to the report by clicking a button and entering a start date and end date. Both the start date and end date are pulled from (1) Date field in the underlying tables. The SQL is as follows:

WHERE (((PO.Date)>=[Enter Report Start Date] And (PO.Date)<=[Enter Report End Date]))

WHERE ((([Financial Data].Date)>=[Enter Report Start Date] And ([Financial Data].Date)<=[Enter Report End Date]));


I have a text box on my report where I want the dates to show - but I can't figure out how to get the report to know what the user typed in for the start date and end date

 

[cer5yc]

Never mind got it working!!