I'm sorry I can see why your confused. I am not using SQL, only VBA in Access. I have a new database with one table that has the current date in a cell.
I created a table named MonthDate took off the Primary key, and changed the Field Name to CurrMonth, and in the Default Value I entered the Now() Function.
I created a Query looking at MonthDate, pulling field CurrMonth into the Query and made a second field called PrevMonth. I entered the formula Month(Now()) just to see a month in the second field and make sure it was working, but it didn't bring anything back. Instead it was blank or null. I'm expecting the Month 11 to come back. It didn't.
If you're not able to help do you know of anything I can try to figure out why it would return a null value?