There are too many places where this can go wrong, e.g. you have:
- a field which is (presumably) a Date/Time type;
- an undeclared parameter (which could therefore be anything at all);
- the Like operator (which is going to give you a string comparision);
- wildcards so it could pick up numbers anywhere in the string;
- an expectation that the formatting of the field affects the contents
(which is not the case).
To fix all that, we need to ensure Access is matching the data types
correctly, that the paremter is correct, and the comparsion is applied on
the right part of the field. The suggestion below expects the parameter to
be entered as a month number (i.e. 1 to 12), and then compares that to the
month part of the date. Note that this will retrieve records that match that
month, regardless of year.
1. In a fresh column in the Field row in query design, enter:
Month([Job_Date])
In the Criteria row under this field, enter:
[Enter Month Number]
2. Choose Parameters on the Query menu
Access opens a dialog.
Enter:
[Enter Month Number] Integer
For general suggestions on working with dates in Access in a country that
uses the d/m/y date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>I have problems using dates as parameters in select queries. I changed the
>date format from mmddyyyy to ddmmyyyy. Initially my parameter query where
[quoted text clipped - 19 lines]
>
> Abay
Many thanks for your reply .. I have altered the code as you suggested and
of course it works like a charm. Thank you for the link to your tips on
International Dates in Access, they are very informative.
Also you rightly pointed out that I am extracting on "month" regardless of
year. I would like to have the selection default to the current year & some
suggestions in that area would be much appreciated. Or failing that I would
like to be able to be able to enter the month and year as "mmyy" as an
answer to the parameter prompt, so far I have come up with the following
(sample code) which uses two parameter prompts and requires the full yyyy to
be entered.
SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE (((Qtech_hours_1.Wo_no)>"w") AND ((Qtech_hours_1.Tech_init)<>"cn" And
(Qtech_hours_1.Tech_init)<>"3p") AND ((Qtech_hours_1.Wo_back)=True) AND
((Month([Job_date]))=[Enter Month]) AND
((DatePart("yyyy",[Job_date]))=[Enter Year]));
Again many thanks for your help which is much appreciated ...
Abay
> There are too many places where this can go wrong, e.g. you have:
> - a field which is (presumably) a Date/Time type;
[quoted text clipped - 50 lines]
>>
>> Abay
John Spencer - 05 Nov 2005 23:19 GMT
I would probably do something like the following, if I were going to do this
with a parameter query.
SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE Qtech_hours_1.Wo_no>"w" AND Qtech_hours_1.Tech_init<>"cn" And
Qtech_hours_1.Tech_ini<>"3p" AND Qtech_hours_1.Wo_back=True AND
Job_Date Between DateSerial([Enter Year],[Enter Month],1) and DateSerial([Enter
Year], [Enter Month]+1,0)
With date serial you can enter two-digit years (or 4-digit) and the function
will guess at the century (>=30 = 20th century; <30 21st Century). The actual
year number is settable as a system parameter, but the default is 30.
> Many thanks for your reply .. I have altered the code as you suggested and
> of course it works like a charm. Thank you for the link to your tips on
[quoted text clipped - 81 lines]
> >>
> >> Abay
Allen Browne - 06 Nov 2005 03:02 GMT
Abay, John's answer is definately the way to go.
If you have an index on the Job_Date field, JET will be able to use that
index to find dates between the 2 literal values generated by DateSerial(),
so that is much more efficient than the Month() function.

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
> I would probably do something like the following, if I were going to do
> this
[quoted text clipped - 108 lines]
>> >>
>> >> Abay
Abay - 06 Nov 2005 07:33 GMT
Thank you both again and I will follow your advise, seems like the way to
go.
Abay
> Abay, John's answer is definately the way to go.
>
[quoted text clipped - 118 lines]
>>> >>
>>> >> Abay