Previous month returns blank text box

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

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.
 
Thanks for the reply Boyd
But new string returns a #error

Boyd Trimmell aka HiTechCoach via Access said:
BRosland said:
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.
Click to expand...


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
Click to expand...
 
Thanks again Boyd
Still get an error. Either I get all records from last year or none.

Boyd Trimmell aka HiTechCoach via Access said:
BRosland said:
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] =" &
Click to expand...
[quoted text clipped - 7 lines]
(Date()), Month(Date())-1,1) & " and " & DateSerial(Year(Date()), Month(Date
()),0) & " And [Ordered] = Yes" )
Click to expand...
Click to expand...


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


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

Boyd Trimmell aka HiTechCoach via Access said:
BRosland said:
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" )
Click to expand...


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

Get rid of the # characters within DateSerial().

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