DSum #Error

  • Thread starter Thread starter BRosland
  • Start date Start date
B

BRosland

control box in form should show quote amount ($) of beams month to date and
should only add amount that have -1 in the order field.
=DSum([Beam_Quote],"TCTTracks","[Quote_Date] >=
DateSerial(Year(Date()),Month(Date()),1) And [Ordered] = -1") but all I get
is #Error.
 
Try putting need quotes around [Beam_Quote]: it's supposed to be the name of
the field, so it's a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BRosland said:
control box in form should show quote amount ($) of beams month to date
and
should only add amount that have -1 in the order field.
=DSum([Beam_Quote],"TCTTracks","[Quote_Date] >=
DateSerial(Year(Date()),Month(Date()),1) And [Ordered] = -1") but all I
get
is #Error.
Click to expand...
 
Thanks Doug
That works as long as I do not have the and portion of the string which
shows the record is a order.

Douglas J. Steele said:
Try putting need quotes around [Beam_Quote]: it's supposed to be the name of
the field, so it's a string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BRosland said:
control box in form should show quote amount ($) of beams month to date
and
should only add amount that have -1 in the order field.
=DSum([Beam_Quote],"TCTTracks","[Quote_Date] >=
DateSerial(Year(Date()),Month(Date()),1) And [Ordered] = -1") but all I
get
is #Error.
Click to expand...
Click to expand...
 
You are missing more delimiters and quotes than you may realize

=DSum("[Beam_Quote]","TCTTracks","[Quote_Date] >=#" &
DateSerial(Year(Date()),Month(Date()),1) & "# And [Ordered] = -1")
--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com


BRosland said:
control box in form should show quote amount ($) of beams month to date
and
should only add amount that have -1 in the order field.
=DSum([Beam_Quote],"TCTTracks","[Quote_Date] >=
DateSerial(Year(Date()),Month(Date()),1) And [Ordered] = -1") but all I
get
is #Error.
Click to expand...
 
I don't think that's necessary, Bob.

"[Quote_Date] >= DateSerial(Year(Date()),Month(Date()),1)"

is perfectly valid.

And, to be picky, your suggestion will not work much of the time for users
whose Short Date format has been set to dd/mm/yyyy. To make it universal, it
should be

=DSum("[Beam_Quote]","TCTTracks","[Quote_Date] >=" &
Format(DateSerial(Year(Date()),Month(Date()),1), "\#yyyy\-mm\-dd\#") & "
And [Ordered] = -1")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob Larson said:
You are missing more delimiters and quotes than you may realize

=DSum("[Beam_Quote]","TCTTracks","[Quote_Date] >=#" &
DateSerial(Year(Date()),Month(Date()),1) & "# And [Ordered] = -1")
--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com


BRosland said:
control box in form should show quote amount ($) of beams month to date
and
should only add amount that have -1 in the order field.
=DSum([Beam_Quote],"TCTTracks","[Quote_Date] >=
DateSerial(Year(Date()),Month(Date()),1) And [Ordered] = -1") but all I
get
is #Error.
Click to expand...
Click to expand...
 
Back
Top