YTD Formula doesn't work for Oct-Dec

I have records which represent invoices including the date
and invoice total. In a query I add two columns
converting the date to a month number (1-12) and the date
to a year (2003, 2004, etc). The query prompts the sales
person for the [current year] and [current month]. The
YTD sales formula in the query is as follows:
if([current year]=[year] and [month]=[current month], ...
This function works until October (10), then I have to
change the formula to .... and [month]([current month]
+1),... this doesn't make any sense to me. The original
formala should work fine (it does for months 1-9), but as
soon as I hit a two digit month, it stops working.

The new formula works, but my curiosity is killing me.

Any ideas?

Phil

Phil, just wondering whether Access may be performing a text comparison
between these values instead of a numeric comparison.

If these are calculated fields, or numbers read from text boxes, see:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

The other possiblity is that the terms Year and Month are being
misunderstood at some point because these are the names of VBA functions.
You could try renaming your fields/controls.

--
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.

"Phil" wrote in message
...
I have records which represent invoices including the date
and invoice total. In a query I add two columns
converting the date to a month number (1-12) and the date
to a year (2003, 2004, etc). The query prompts the sales
person for the [current year] and [current month]. The
YTD sales formula in the query is as follows:
if([current year]=[year] and [month]=[current month], ...
This function works until October (10), then I have to
change the formula to .... and [month]([current month]
+1),... this doesn't make any sense to me. The original
formala should work fine (it does for months 1-9), but as
soon as I hit a two digit month, it stops working.

The new formula works, but my curiosity is killing me.

Any ideas?

Phil