DSum expression not working

  • Thread starter Thread starter gorsoft
  • Start date Start date
G

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
 
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
Click to expand...

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
 
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
Click to expand...

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
Click to expand...
 
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 & "#")
 
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



[email protected] said:
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:
Click to expand...
=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN #1/1/10# AND #12/31/10#")
Click to expand...
but  am trying to design it so that the expression doesn't have to be
changed every year, so when I use this:
Click to expand...
=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN DateSerial(Year(Date()), 1, 1) AND Date")
Click to expand...
I get an "#error" in the control.  My computer's date setting is for
the UK if that is relevant.
Click to expand...
Thanks for any help.
Click to expand...
Gordon
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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


[email protected] said:
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



[email protected] said:
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:
Click to expand...
=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN #1/1/10# AND #12/31/10#")
Click to expand...
but am trying to design it so that the expression doesn't have to be
changed every year, so when I use this:
Click to expand...
=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN DateSerial(Year(Date()), 1, 1) AND Date")
Click to expand...
I get an "#error" in the control. My computer's date setting is for
the UK if that is relevant.
Click to expand...
Thanks for any help.
Click to expand...
Gordon
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
.
Click to expand...
 
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



[email protected] said:
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 -
Click to expand...
Click to expand...
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:
Click to expand...
=DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate]
BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND # “ & Date &
"#")
Click to expand...
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 -
Click to expand...

- Show quoted text -
Click to expand...

Hi Daryl S,

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

Gordon
 
Back
Top