Month To Date & Year To Date

  • Thread starter Thread starter Comcast News Groups
  • Start date Start date
C

Comcast News Groups

have a field InvoiceDate in a table called "ClientTracking" and a field
InvoiceTotal in a table called "CheckTracking". I am trying to create a form
that will sum the InvoiceTotal as YearToDate in one box and MonthToDate in
another box.

Any help?

TIA
R. Fisher
 
R,

One possible approach would involve the use of domain functions. Put
unbound textboxes on the form. For YearToDate, put this as its
ControlSource...
=DSum("[InvoiceTotal]","YourQuery","[InvoiceDate]>DateSerial(Year(Date()),1,0)")
.... and for MonthToDate...
=DSum("[InvoiceTotal]","YourQuery","[InvoiceDate]>DateSerial(Year(Date()),Month(Date()),0)")

- Steve Schapel, Microsoft Access MVP
 
I have a basic understanding of Access, some of the functions are foreign to
me ...

Tried this and am getting #error.

First question - how does the "Your Query" come into play here? what is this
query supposed to be?
Secondly, the record source for the form itself is the CheckTracking table.
The InvoiceDate field is in a table called ClientTracking. If the record
source for the form is CheckTracking (where the values are that I want to
add up), don't I have to somehow tell the function to use the ClientTracking
table to perform the DateSerial function on the InvoiceDate field?

Thanks -
R. Fisher

Steve Schapel said:
R,

One possible approach would involve the use of domain functions. Put
unbound textboxes on the form. For YearToDate, put this as its
ControlSource...
=DSum("[InvoiceTotal]","YourQuery","[InvoiceDate]>DateSerial(Year(Date()),1,
0)")
... and for MonthToDate...
=DSum("[InvoiceTotal]","YourQuery","[InvoiceDate]>DateSerial(Year(Date()),Mo
nth(Date()),0)")

- Steve Schapel, Microsoft Access MVP


have a field InvoiceDate in a table called "ClientTracking" and a field
InvoiceTotal in a table called "CheckTracking". I am trying to create a form
that will sum the InvoiceTotal as YearToDate in one box and MonthToDate in
another box.

Any help?

TIA
R. Fisher
Click to expand...
Click to expand...
 
Back
Top