Dates in Parameter Query

[shandyw]

Calling all MS Access Gurus!

How can I allow users to enter a date in a parameter query through a custom dialog box so that all the records pertaining to a particular month are extracted? In the past I have used a Like query, which enabled users to indicate the required month by typing in */03/* for March, for example. But now I want to make this criteria part of a custom dialog box which allows users to specify other criteria as well. The complete list of criteria are “ClientCode”, PartnerCode”, “ManagerCode”, and “ClientYearEnd”. I have created the form for the dialog box and entered the first 3 criteria as [Forms]![Fmergefilters]![ClientCode] and so on. The first 3 criteria work fine, singly or together. However, the ClientYearEnd one only works if a complete date is entered eg 31/03/96. This doesn’t help me because my users need to be able to extract records by month.

Many thanks
Shandy@sayervincent.co.uk

 

[jhall156]

The trick is to put a derived column in your query. In the QBE Grid make a column TheMonthatePart('m',[yourdatefield])
then in the criteria put your form reference. JHall

 

[shandyw]

Thanks jhall156 for your useful reply. I haven't got it to work yet. Perhaps I'm doing something silly?
I inserted a new column in the QBE grid.
In the Field Section I typed
TheMonth: DatePart('m',[clientyearend])
In the Criteria Section, I typed
[Forms]![FMergeFilters]![clientyearend]

The query doesn't work. What could be wrong?
Thanks
Shandy

 

[jhall156]

I'm sorry, there should be double quotes around m i.e. DatePart("m", etc
Different languages have different delimeter rules and I get 'em mixed up a lot. JHall