In my query I have CourseDate on which I want to search. It’s in dd/mm/yy format, so I’ve made a new field, so that I can search by month:

Mth: Format([CourseDate], “mm/yy”)

With Criteria: Between [Start Month mm/yy] And [End Month mm/yy]

For some reason this sometimes works by returning the required data, but other times it’s pulled in other months not required. Also, it sometimes gives me the parameter boxes a second time instead of just once!

Anyone got any ideas as to what’s going wrong, or another way of doing it please!?

If you are only going to use month and year, you need to swap the two columns so that year is first. When you use all three parts of a date, Access recognizes it as a date and converts it to its internal date format for matching/sorting purposes. However, using only the two parts, the field is being treated simply as a text string. Therefore in order to get the proper results, you need to have the fields in major/minor order and in the case of dates, that is yy/mm. Given the following text string (don't think of it as a date because Access isn't) - 02/99 - You will find that it would fall between 01/02 and 03/02 but 99/02 would not fall between 02/01 and 02/03.

Excellent, all sorted - I didn't realize that. Thank you very much Pat.