Pull only current month query

[teachesms]

I remember doing queries with a statement that could either pull year or month out of the table data using the MONTH command for example. Right now I have a parameter query in the [StartDate] field as: [Enter Start Date in the MM/DD/YY format] however, when used the startdate of a class has to be on the particular day they type which is not what I want. There is NO enddate field for this database table. How do I pull out from the table (in a query) all the classes that are in JUNE for example, or JULY? without having the enddate field to work with. If I had it, I'd just do a between[startdate]and[enddate] query.

Thanks

[HansV]

One option is to add a calculated column

M: [ClassStart]-Day([ClassStart])+1

Clear the Show check box for this column, and set the criteria to

[Enter Month of Class in MM/DD/YY format]-Day([Enter Month of Class in MM/DD/YY format])+1

The user can enter an arbitrary date in the relevant month, e.g. 06/01/10 or 06/23/10 - the day doesn't matter.

[teachesms]

Hans,

I tried this, and this is what I get: (see graphic) (I also forgot to clear the check in the show/hide, but when I remembered it still didnt' work)

[HansV]

Try the following:
- Open the query in design view.
- If you're using Access 2003 or before, select Query | Parameters...
- If you're using Access 2007 or later, activate the Design tab of the ribbon, and click Parameters in the Show/Hide group.
- Enter the parameter in the Parameter column exactly as used in the query - in this example [Enter Month of Class in MM/DD/YY format].
- Select Date/Time in the Data Type column.
- Click OK.
This extra information should enable Access to execute the query.
Post back if it doesn't work.

[teachesms]

Hans,

That worked. That is wierd. Is there a reason for having to do this, or is it just a matter of assigning datatype to the field to make it work?

And...as always, thank you so very much for the help.

:)

[HansV]

If you just use a parameter, without declaring it, Access doesn't know what kind of data the user might enter - text, a number, a date. This means that Access has to do much more work, and if you perform a calculation with the parameter, it becomes too complicated. Telling Access in advance that you'll enter a date simplifies the processing.

Similarly, a crosstab query based on a parameter query will normally fail, but if you declare the parameter and its data type, it'll work.

[teachesms]

Declaring the variable ahead makes sense...just like in VBA. Thanks ahead.