Due Dates

[Atif Hasan]

Hi all
I m working on a contractual data base.
In every contract we have a starting date and expiry date and we have to
invoice our clients on quarterly basis. Is it possible in Access that we can
create some field which gives automatically next due date. I have created a
table which have fields like Job No., Name, Contract Start Date, Contract
Expiry Date, Contract period (in years), Contract Value.

 

[Allen Browne]

You will need an invoice table (to keep track of what you invoiced and when,
so you could get the last invoice date for the ClientID in the form like
this:
=DMax("InvoiceDate", "tblInvoice", "ClientID = " & Nz([ClientID],0))

If the next one is due in 3 months, the next due date would then be:
=DateAdd("m", 3, DMax("InvoiceDate", "tblInvoice", "ClientID = " &
Nz([ClientID],0)))

You will need to track the invoices separately from the jobs themselves.
Otherwise you will have no idea about what's been invoiced and what hasn't.
For example, say Job 45 is dated March 28. On April 1, you invoiced all the
Jan - Mar jobs. On April 2, someone modified the job (changing an amount,
adding a line, deleting a line.) Now if you view/print the invoices again,
you get something quite different from what was actually sent to the client.

 

[Atif Hasan]

HI
Thanks for ur help
but there is one problem , im using the code that u told me
=DateAdd("m",3,DMax("Date","Invoice"," [INVOICE]![JOB] = Forms![Main
Form]![Combo40]"))

i already have a table for Invoice
but in this table i also have the data of invoices which are not belongs to
original contracts , these are additional work invoices, i m also using a
field to specified which invoice belongs to contract and which is not, i have
a field CON for that purpose and i m using "C" for contract and "M" for
additional works. The code give me the last date of the Job No. i mentioned,
but if the last invoice having "M" (means belongs to additional work) then it
will add 3 months in that date , i want the last invoice having same Job No.
but only belongs to Contract Invoice i.e. "C"
hope u understand what i m trying to say

Thanks
--
Atif Hasan

You will need an invoice table (to keep track of what you invoiced and when,
so you could get the last invoice date for the ClientID in the form like
this:
=DMax("InvoiceDate", "tblInvoice", "ClientID = " & Nz([ClientID],0))

If the next one is due in 3 months, the next due date would then be:
=DateAdd("m", 3, DMax("InvoiceDate", "tblInvoice", "ClientID = " &
Nz([ClientID],0)))

You will need to track the invoices separately from the jobs themselves.
Otherwise you will have no idea about what's been invoiced and what hasn't.
For example, say Job 45 is dated March 28. On April 1, you invoiced all the
Jan - Mar jobs. On April 2, someone modified the job (changing an amount,
adding a line, deleting a line.) Now if you view/print the invoices again,
you get something quite different from what was actually sent to the client.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all
I m working on a contractual data base.
In every contract we have a starting date and expiry date and we have to
invoice our clients on quarterly basis. Is it possible in Access that we
can
create some field which gives automatically next due date. I have created
a
table which have fields like Job No., Name, Contract Start Date, Contract
Expiry Date, Contract period (in years), Contract Value.

 

[Allen Browne]

So you want the job number specified in the combo, and also a "C" in another
field:

=DateAdd("m",3,DMax("Date","Invoice", "([INVOICE]![JOB] = " &
Nz(Forms![Main Form]![Combo40],0) & ") AND ([AnotherField = 'C')"))


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HI
Thanks for ur help
but there is one problem , im using the code that u told me
=DateAdd("m",3,DMax("Date","Invoice"," [INVOICE]![JOB] = Forms![Main
Form]![Combo40]"))

i already have a table for Invoice
but in this table i also have the data of invoices which are not belongs
to
original contracts , these are additional work invoices, i m also using a
field to specified which invoice belongs to contract and which is not, i
have
a field CON for that purpose and i m using "C" for contract and "M" for
additional works. The code give me the last date of the Job No. i
mentioned,
but if the last invoice having "M" (means belongs to additional work) then
it
will add 3 months in that date , i want the last invoice having same Job
No.
but only belongs to Contract Invoice i.e. "C"
hope u understand what i m trying to say

Thanks
--
Atif Hasan

You will need an invoice table (to keep track of what you invoiced and
when,
so you could get the last invoice date for the ClientID in the form like
this:
=DMax("InvoiceDate", "tblInvoice", "ClientID = " & Nz([ClientID],0))

If the next one is due in 3 months, the next due date would then be:
=DateAdd("m", 3, DMax("InvoiceDate", "tblInvoice", "ClientID = " &
Nz([ClientID],0)))

You will need to track the invoices separately from the jobs themselves.
Otherwise you will have no idea about what's been invoiced and what
hasn't.
For example, say Job 45 is dated March 28. On April 1, you invoiced all
the
Jan - Mar jobs. On April 2, someone modified the job (changing an amount,
adding a line, deleting a line.) Now if you view/print the invoices
again,
you get something quite different from what was actually sent to the
client.

Hi all
I m working on a contractual data base.
In every contract we have a starting date and expiry date and we have
to
invoice our clients on quarterly basis. Is it possible in Access that
we
can
create some field which gives automatically next due date. I have
created
a
table which have fields like Job No., Name, Contract Start Date,
Contract
Expiry Date, Contract period (in years), Contract Value.