Query to dispay one months data

[Simon]

I have a query that shows all the order that have been placed. I would
like to know how to set up the query for when it runs that it asks me
which month orders i want it to display.
I would like to be able to type in ' November 06' and also '11/06'

I can not remeber how to do this in the query

 

[Rick Brandt]

I have a query that shows all the order that have been placed. I would
like to know how to set up the query for when it runs that it asks me
which month orders i want it to display.
I would like to be able to type in ' November 06' and also '11/06'

I can not remeber how to do this in the query

The problem is that when you supply only two parts of a date as you have above,
the numeric portion will be assumed to be the day and the year will default to
the current year.

Self prompting parameter queries are one of the first things you should
"outgrow" in Access. Much better to create a form where you enter the criteria
that the query uses by referencing the form. That way you can use any number of
mechanisms in the form to properly translate what you enter into what you
actually intend it to mean and then the query does not have to deal with any
ambiguity.

 

[Guest]

As Rick says referencing controls on an unbound dialogue form is a much
better approach to this sort of problem. I would normally use two combo
boxes on the form, one which lists all the years over a range, e.g. 2000 to
2020, the other which list the months. I'd normally have the combo box show
the month names but have a value of the number by using a value list like so
as its RowSource:

1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December

and setting the combo box's BoundColumn property to 1, its ColumnCount to 2
and its ColumnWidths to 0;8cm (or rough equivalent in inches, but the first
dimension must be zero to hide the month numbers). Add a button to the form
to open the query or better still a form or report based on the query.

In the query you can then use the Year and Month functions to restrict the
result to the selected months, e.g.

SELECT *
FROM Orders
WHERE YEAR(OrderDate) = Forms!frmOrdersDlg!cboYear
AND MONTH(OrderDate) = Forms!frmOrdersDlg!cboMonth;

where frmOrdersDlg is the name of the form and cboYear and cboMonth are the
names of the two combo boxes.

Ken Sheridan
Stafford, England