DatePart or Format?? (Access2K/Win2K)

Topic

New Reply

I have a monthly report.

I’d like to open it from a Reports switchboard, via a parameter form, where i choose the month. I have built the switchboard, and the parameter form, and a combobox looks up the values of the order date in the recordsource. All ok so far.

What i’d like is for the order date in the combobox just to show the month and year, so instead of 20 or 30 orders showing dd-mm-yy, just 1 entry in the combobox showing the mmm-yy for those orders, and the same for all the other orders…

Is this a simple case of formatting, or is there some vba required??

Reply | Quote

Replies

Using the date field itself and setting the Format propety of the combo box to mmm-yy would not suppress the duplicates; you will have to set the Row Source of the combo box to an SQL statement or a query that selects distinct months:

SELECT DISTINCT Format([DateField], “mmm-yy”) FROM tblSomething

Reply | Quote

Using the date field itself and setting the Format propety of the combo box to mmm-yy would not suppress the duplicates; you will have to set the Row Source of the combo box to an SQL statement or a query that selects distinct months:

SELECT DISTINCT Format([DateField], “mmm-yy”) FROM tblSomething

Reply | Quote

And it does! Thank you!

I am fairly sure this is an unrelated problem, as your suggestion works in a small test qry.

When i try to use the form/cbobox as the criteria in the report’s query, it tells me it does not recognise the criteria as a valid source. HOWEVER in a test query with very many fewer fields it has no problem.

Is it possible that on a complex query (several tables and a subcrosstabquery), it reports an error on the criteria, yet on a simple query it does not???

Reply | Quote

In complicated queries, in particular in crosstab queries, you have to declare parameters explicitly. You will have to do this in the query that refers to the combo box in its criteria, and in queries built on it:
– Open the query in design view.
– Copy the criteria to the clipboard.
– Select Query | Parameters…
– Paste the criteria (or type them exactly as they are in the criteria row) into the Parameter column.
– Select the appropriate data type in the Data Type column.
– Click OK.

Reply | Quote

In complicated queries, in particular in crosstab queries, you have to declare parameters explicitly. You will have to do this in the query that refers to the combo box in its criteria, and in queries built on it:
– Open the query in design view.
– Copy the criteria to the clipboard.
– Select Query | Parameters…
– Paste the criteria (or type them exactly as they are in the criteria row) into the Parameter column.
– Select the appropriate data type in the Data Type column.
– Click OK.

Reply | Quote

And it does! Thank you!

I am fairly sure this is an unrelated problem, as your suggestion works in a small test qry.

When i try to use the form/cbobox as the criteria in the report’s query, it tells me it does not recognise the criteria as a valid source. HOWEVER in a test query with very many fewer fields it has no problem.

Is it possible that on a complex query (several tables and a subcrosstabquery), it reports an error on the criteria, yet on a simple query it does not???

Reply | Quote