DSum Syntax...I Give Up! 1

[thirtydash9]

Can anyone help with this DSum Syntax? I have been at it for hours.

If I take off the 'AND' part it works fine. I'm pretty sure the problem is with the 2nd condition.

Me.txtCpesNeeded = DSum "[creditsReceived]", "subqryCpeLetter", "[sortNum] = Me.txtSortNum AND [appliedOn]>=#" & Me.txtNextAnnivMinusOne & "#")

I have tried every possible variation I can think of, including pasting in the syntax directly from the Help file and replacing with my object names. I have checked that all of the object names are correct and do exist in the related query and report.

When I try to run this I get run-time error 2001 (you canceled the previous operation).

p.s. me.txtNextAnnivMinusOne is an unbound text box based on the local variable dteNextAnnivMinusOne. If there is a way to call a local variable within DSum I would love to know.

Thank you to whoever can help!
fiddlr

 

[hneal98]

just off the cuff, try entering Me.txtNextAnnivMinusOne.text instead of just Me.txtNextAnnivMinusOne.

This may have nothing to do with your problem, but at least you will eliminate the possibility.

 

[PJStephenson]

couple of things

Me.txtCpesNeeded = DSum "[creditsReceived]", "subqryCpeLetter", "[sortNum] = [red] " & Me.txtSortNum & "[/red] AND [appliedOn]>=#" & [red]format(Me.txtNextAnnivMinusOne,"dd mmm yyyy")[/red] & "#")

possibly

[η][β][π]

 

[mp9]

Do you not also need to open the brackets after DSum, i.e. building on the last post...

Me.txtCpesNeeded = DSum[red]([/red]"[creditsReceived]", "subqryCpeLetter", "[sortNum] = " & Me.txtSortNum & " AND [appliedOn]>=#" & format(Me.txtNextAnnivMinusOne,"mm/dd/yyyy") & "#")

Note I've also used a different date format mask to allow for variations in date format used by different bits of Access (more about this here).

Hope this helps.

 

[thirtydash9]

Eureka!

Thank you so much, hneal98, pjstephenson and mp9 for helping me out. It was the last missing piece in this report.

It must be the date format that did it; I never would have thought of it myself.

It's one of those things that if you don't know it, there's almost no way to find out, except from this awesome forum.

Thanks again,
fiddlr