The setting you entered isn't valid for this property - totals query

[smatchymo]

I'm starting to feel obnoxious starting this many threads on my first project. I did solve one of my issues myself though... Anyway, in my last thread, it was mentioned that I should look into totals queries. I'm running into the error "the setting you entered isn't valid for this property" when I run it. The problem is with my TransactionYear and TransactionDate fields. The data in these fields comes from a query and they're formatted as general numbers.

TransactionYear: Year([TransactionDate])


TransactionMonth: Month([TransactionDate])

The TransactionDate field is formatted as Date/Time. Is there something I can do with the formatting to fix this, or is there another way I can get the year and month number from the transaction date that won't cause me to get this error? Through testing I have confirmed that both of these fields are causing the issue. I also only get the error when creating a totals query. Other queries involving these fields work normally.

Edit: I should add that I am able to click through the error, and the query gives me the data I'm looking for. I have to click through the error twice to get the query to run, once for each problem field.
Edit2: I'm able to get it to work if I save the conversion from date to year# and month# until the totals query. I had previously tried left and right functions instead of the year and month functions, but I had the same issue. Tables-->Query1 w/functions converting date to year#/month#-->TotalsQuery (**FAIL**). However, Tables-->Query1-->TotalsQuery w/functions converting date to year/month# (**SUCCESS**)