On 5 Feb, 14:18, Daryl S wrote:
Gordon -

Let Access evaluate the date - don't include the date functions in quotes -
and use the delimeters:

=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND #& Date & "#")

--
Daryl S



" wrote:
I have a control on a form which displays a running total of tickets
issued year to date (since 1 Jan). *The following expression entered
as the control source for a textbox on the form works OK:

=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN #1/1/10# AND #12/31/10#")

but *am trying to design it so that the expression doesn't have to be
changed every year, so when I use this:

=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN DateSerial(Year(Date()), 1, 1) AND Date")

I get an "#error" in the control. *My computer's date setting is for
the UK if that is relevant.

Thanks for any help.

Gordon
.- Hide quoted text -

- Show quoted text -

Thanks for the responses, guys. Whilst I understand what I was doing
wrong, I still can't fix it. I think your expression, Daryl, may have
been missing a delimiter after the AND #, so using this as the
expression:

=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND # “ & Date &
"#")

I still get an "expression has an invalid date value" error. Must be
something simple, but for the life of me I can't see it.

Help

Gordon