Previous month returns blank text box

[BRosland]

Have text box to calculate sales from previous month using
=DSum("[Roof_Quote]","TCTTracks","[Quote_Date] =" &
Format(DateSerial(Year(Date()),-1,1),"\#yyyy-mm-dd\#") & " And [Ordered] =
Yes")
But all I get is a empty box.

 

[BRosland]

Thanks for the reply Boyd
But new string returns a #error

Have text box to calculate sales from previous month using
=DSum("[Roof_Quote]","TCTTracks","[Quote_Date] =" &
Format(DateSerial(Year(Date()),-1,1),"\#yyyy-mm-dd\#") & " And [Ordered] =
Yes")
But all I get is a empty box.

If the field [Quote_Date] is a date data type this try:

=DSum("[Roof_Quote]","TCTTracks","[Quote_Date] Between" & DateSerial(Year
(Date()), Month(Date())-1,1) & " and " & DateSerial(Year(Date()), Month(Date
()),0) & " And [Ordered] = Yes" )

--
Boyd Trimmell
aka HiTechCoach
http://www.hitechcoach.com
http://www.officeprogramming.com

Message posted via AccessMonster.com

 

[BRosland]

Thanks again Boyd
Still get an error. Either I get all records from last year or none.

Thanks for the reply Boyd
But new string returns a #error

Have text box to calculate sales from previous month using
=DSum("[Roof_Quote]","TCTTracks","[Quote_Date] =" &

[quoted text clipped - 7 lines]

(Date()), Month(Date())-1,1) & " and " & DateSerial(Year(Date()), Month(Date
()),0) & " And [Ordered] = Yes" )

try changing the Yes to True liek this:

=DSum("[Roof_Quote]","TCTTracks","( [Quote_Date] Between" & DateSerial(Year
(Date()), Month(Date())-1,1) & " and " & DateSerial(Year(Date()), Month(Date
()),0) & ") And ( [Ordered] = True )" )

--
Boyd Trimmell
aka HiTechCoach
http://www.hitechcoach.com
http://www.officeprogramming.com

 

[BRosland]

Well after a few changes no longer have #error but now this string gives
totals all records regardless of the year:

=DSum("[Roof_Quote]","TCTTracks","[Quote_Date] Between" &
"DateSerial(#Year(Date()),Month(Date())-1,1#) & " And " &
DateSerial(#Year(Date()),Month(Date()),0#)" & " And [Ordered] = Yes")

Thanks again Boyd
Still get an error. Either I get all records from last year or none.

Thanks for the reply Boyd
But new string returns a #error

Have text box to calculate sales from previous month using
=DSum("[Roof_Quote]","TCTTracks","[Quote_Date] =" &
[quoted text clipped - 7 lines]
(Date()), Month(Date())-1,1) & " and " & DateSerial(Year(Date()), Month(Date
()),0) & " And [Ordered] = Yes" )

try changing the Yes to True liek this:

=DSum("[Roof_Quote]","TCTTracks","( [Quote_Date] Between" & DateSerial(Year
(Date()), Month(Date())-1,1) & " and " & DateSerial(Year(Date()), Month(Date
()),0) & ") And ( [Ordered] = True )" )

--
Boyd Trimmell
aka HiTechCoach
http://www.hitechcoach.com
http://www.officeprogramming.com

 

[Rick Brandt]

Well after a few changes no longer have #error but now this string gives
totals all records regardless of the year:

=DSum("[Roof_Quote]","TCTTracks","[Quote_Date] Between" &
"DateSerial(#Year(Date()),Month(Date())-1,1#) & " And " &
DateSerial(#Year(Date()),Month(Date()),0#)" & " And [Ordered] = Yes")

Get rid of the # characters within DateSerial().

You have no space after "Between" nor a space before DateSerial. That
will result in BetweenDateSerial.