DSum expression not working

[gorsoft]

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

 

[XPS350]

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

Because your dates are now variables, you can't use a fixed string as
where-clause. Try:
=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN " & DateSerial(Year(Date()), 1, 1) & " AND " & Date)

Groeten,

Peter
http://access.xps350.com

 

[Tom van Stiphout]

What he said, but then also add #-signs to wrap the date values.

-Tom.
Microsoft Access MVP

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

Because your dates are now variables, you can't use a fixed string as
where-clause. Try:
=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN " & DateSerial(Year(Date()), 1, 1) & " AND " & Date)

Groeten,

Peter
http://access.xps350.com

 

[Daryl S]

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 & "#")

 

[gorsoft]

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

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

 

[Daryl S]

Gordon -

You don't have any variables or controls called "Date" do you? You can try
using Date() instead of Date. I also notice a “ (left double quote) in your
statement that should be a regular double quote ". Try this:

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


--
Daryl S

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

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
.

 

[gorsoft]

Gordon -

You don't have any variables or controls called "Date" do you?  You cantry
using Date() instead of Date.  I also notice a “ (left double quote) in your
statement that should be a regular double quote ".  Try this:

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

--
Daryl S

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
:
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.

Gordon
.- Hide quoted text -

- Show quoted text -

Hi Daryl S,

The brackets after Date did the trick - your revised code above now
works a treat. Many thanks.

Gordon