Problem with month function

[Mitt1]

Hi

I have an invoice table which shows the date (Short Date) of the month the invoice was made

I have created a query using the month function so when I search I enter 1 to 12 for the corresponding month and this should return the invoices for that month but the problem is nothing gets returned.

In the query field I have Month([invoiceDate]) and in the criteria I have [Please enter Month]

Any help is greatly appreciated

 

[pbaldy]

Does the query field return 1-12 if you take the criteria off? You realize that over time, this will return records for multiple years?

 

[Mitt1]

But if remove the criteria then I can't search for the month

 

[pbaldy]

Yes, I know. It's an effort to make sure the field is returning what you think it is.

 

[Galaxiom]

As records accumulate your query will get slower and slower because it must apply the Month function to every record before it can select.

Far better to specify a date range in the Where clause. With an index on the date field this technique can easily be one hundred times faster. It also covers the year.

 

[Simon_MT]

I agree, the problem is searching an expression - also try an Index on the Date

Simon