I want to create a parameter query in which the criteria is based on a range of dates.

My database contains a lists of sales contracts with their subsequent closing date. In a form the date is entered in a mm/dd/yy format.

I want the parameter query to generate all the closings for a particular month. I understand that I can set the criteria in the parameter query to ask the first and last date of the month in question, but I would much rather ask the question, "Enter the month you wish to see, such as April".

Can this be done since the criteria (months of the year) is a different format than the data in the field (mm/dd/yy)?

I, personally, use forms for input for queries and all. So you could select the month in a combo box (with the month number as the bound column and then display the month name). You would use this in the criteria of the date field:


Between DateSerial(Year(Date()), [Forms]![YourFormnameHere]![YourComboName], 1 ) And DateSerial(Year(Date()), [Forms]![YourFormnameHere]![YourComboName] + 1, 0)

Before I try to decipher the criteria statement (LOL), I want to make sure I explained myself clearly.

When the date is entered in the form, it must be in the mm/dd/yy format since the contract can close on any date of the month. You mentioned a combo box, which I am assuming you meant within the form. So if I understand correctly, in using a combo box the way you described I would be limited to the name of the month only and not a specific date as is required.

Am I reading you correctly?

Okay, you can just change it to this:

Between DateSerial(Year(Date()), Month([Forms]![YourFormnameHere]![YourTEXTBoxNameHere]), 1 ) And DateSerial(Year(Date()), Month([Forms]![YourFormnameHere]![YourTEXTBoxNameHere]) + 1, 0)

As a FYI-

If you plan on using the same query in more than one forms, you may want to use a parameter query so you still have only one query without having to track each instance of combobox that behaves similarly (e.g. select a month for closings).

In such case, a bit of VBA is needed to use the parameter queries with forms. But if it's going to be only on one form (which usually is the case for creating filters for a report), then Bob's method is certainly the most efficient. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Ah ha... I understand a little bit more on what you were saying in your original post.

When you said, "I, personally, use forms for input for queries and all.", you were referring to a form used as a Dialog Box, right? Now I understand what you meant by using a combo box.

This is a little advanced for me at the moment, but with the help you gave me and the research I did as I was waiting for a reply, I think I will be able to get through it!

Thanks!

Oh, and just a little explanation of the DateSerial code I used.

The input is DateSerial(Year, Month, Day)

and I had set the year to be the year of the current date with Year(Date()) and then the month by using either the combo or the text box and then putting in the day. In the first part it is the first which is the first day of the month and then in the second part, I put the month plus one and day 0 which turns out to be the last day of the previous month (that part is kinda cool - I didn't figure that out - someone taught me that).

Bob,

OK, so I have completed a tutorial on Dialog Boxes that will generate a query. Now I am trying to apply what I learned there with what you showed me earlier.

Simply put, the records in my database contain "Community" (a three letter mnemonic representing each of our 14 communities), "Lot Number", "Buyer Name", "Closing Date" (mm/dd/yy), "Closed" (yes, no or cancelled) and a "Contract Price".

I would like the Dialog Box to ask "Month" (combo box will have January, February, etc.), "Closed" (combo box will have "Yes", "No", "Cancelled" or "All") and "Community" (combo box will have the 14 mnemonics representing each of our 14 communities or ALL).

The query should then show, for example, all the contracts that did close in March for X Community. I have designed the Form/Dialog Box and know how to link everything (I hope), but I need help on the code for the criteria.

Thanks for all you help!!!

Rob

So basically you will have three combo boxes, right? If you want all three in an AND relationship, you can do this in the criteria cells of the relevant query columns:

[Forms]![NameOfForm]![NameOfControl]

Do not forget that you must refer to the column that corresponds to the bound column of the combo box, not necessarily the first visible column.

Thank you!