M/S - Access 2007 - Displaying Values of Parameters in a FORM

Question

I'm just learning Access 07 and coding so, this may be a very simple question:

I have a FORM that I want to display the value of Parameters so that I know what I have inputed. Similar to [Start Date] & [End Date].

"Your query will start at 07/01/2009 and end on 07/10/2009."

I can do this in a REPORT "Parameters!Start Date.value" but this does not work in a FORM.

Thanks for your help.

Answer 1

Instead of relying on the query to throw inputboxes for your start and end dates, just make a form with start date and end date text boxes, and refer to those in your query.

So instead of:

Between [Start Date] and [End Date]

Do:

Between Forms!MyForm!txtStartDate and Forms!MyForm!txtEndDate

To put default values into the text boxes, you can use the Date function in the Form.OnLoad event.

You can say me.txtStartDate = Date, which will give you today's date. You can add or subtract days from Date, and use the DateAdd function to add or subtract intervals such as Months. The first day of the month is

CDate("1/" & Format(DateAdd("m", -1, Date), "mmm/yyyy"))

and the last day of the month is

CDate("1/" & Format(dat, "mmm/yyyy")) - 1

Answer 2

To be able to display the paramter values on your form, echo the parameters in the SELECT statement, e.g.:

  PARAMETERS [Start Date] DateTime, [End Date] DateTime;
  SELECT MyTable.*, [Start Date] AS Starting, [End Date] AS Ending
  FROM MyTable
  WHERE MyTable.DateField Between [Start Date] And [End Date]));

Then as the controlsource of the control on your form that you want to display the parameters, you'd use this:

  ="Your query will start at " & [Starting] & " and end on " & [Ending] & "."

Alternatively, you could put that in the SELECT statement of your query:

  SELECT MyTable.*, "Your query will start at " & [Start Date] & " and end on " & [End Date] & "." AS Message

...and then you'd use the Message field as the controlsource of your control.