Make the value of the DateSerial it's own variable
Print it
Pass it into the DSUM

Sorry Alex, I still have a loooooooong way to go. Baby steps? Thanks

I tested your DSum function and it worked perfectly for me.

- Is there anything in the query preventing the function from working correctly?
- It may be another date format problem.

Bizaar. Looking at it makes sense but yeah, the following shows what i've tried to do and how its worked.

Again, the YTD looks perfect but doing MTD or TODAY doesn't work. I've printed the date function used underneath
Form (Pic 1)
Code (Pic 2)

- The Query is a Union Query, Pic (3)
- The Text Box on the Form has been set as Currency with 0 Decimal Places in properties

Still having an issue with this one. As you can see from 3 pics up, the date function is working, pulling the correct date. But when used in my DSUM (2 Pics Up), its having the wrong affect - Today is generating nothing and Month To Date is pulling year to date.

No error messages and compile brings nothing up. I'm really trying to avoid using queries so i don't get caught up but i might have to revert to them for the Today and Month figures if i can't work this out.

Does anyone have any ideas?

Dan,

Try wrapping the DateSerial function in the Format function (US format).

Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy")

Getting there, Getting There!
Got the Today working but Month to Date still isn't

Text_WMTDCIMS = DSum("GrossWrittenAMT", "SalesAllQ", "GrossWrittenDate>=#" & Format(DateSerial(Year(Date), Month(Date), 1), "dd/mm/yyyy") & "# And AdvisorID = 3")

Change the format to mm/dd/yyyy

Strange! It Works. Guess It didn't matter with the Year to Date...

Thank You Kevin. Just sent a little something to express my gratitude, just wish i could do more. Have a great weekend mate.