payment duration (date issue)

[samer]

This is purchase invoice due date issue:
I’ve this table:
Invoice number.......Number
Date.....................Date/time.....MM/YYYY
Duration time.........Number

I am going to enter the month and the year – for the invoice -.
The duration time will be after 1,2,3 and 4 month.
So in the duration time field when I enter the 1 or 2 or 3 or 4
It will automatically calculate like this:

Invoice number......Date.......Duration time...Due date
1001..............04/2004............2..............06/2004
1002..............04/2004............3..............07/2004
1003..............03/2004............1..............04/2004
1004..............03/2004............4..............07/2004
1005..............12/2004............1..............01/2005
1006..............12/2004............2..............02/2005

Duration time in month and year , both month and year will change.
So how can I do it please? [/SIZE]

 

[tangoman66]

I think it is possible to use a formula such as =[Date] + ([Duration Time]*31) However this would become inaccurate due to the fact that not every month has 31 days. You could change the 1/2/3/4 month idea to periods of days i.e. 30/31 then this method should be appropriate

 

[Mile-O]

If you are only going to enter the Month and Date (why no Day?) then I'd leave those textboxes on your form unbound and have a hidden one bound to the Date field (bad name; it's a reserved word).

On the BeforeUpdate of the form use this line to put the value into the hidden control: =DateSerial(YearControl, MonthControl, 1)

You don't need the DueDate field in your table WHATSOEVER as it's calculable at any time in a query, form, or report by using the DateAdd() function.

 

[samer]

still i need to use month and year , Actually it must be like this . So what
will the solution be ... ?

 

[samer]

If you are only going to enter the Month and Date (why no Day?) then I'd leave those textboxes on your form unbound and have a hidden one bound to the Date field (bad name; it's a reserved word).

On the BeforeUpdate of the form use this line to put the value into the hidden control: =DateSerial(YearControl, MonthControl, 1)

You don't need the DueDate field in your table WHATSOEVER as it's calculable at any time in a query, form, or report by using the DateAdd() function.

still when i need to creat a report it will not appear , reports do not depend
on forms thats why I need to do that in a quiry depending on a table .
I did understand that your solution in form .. isn't it ?

 

[samer]

I think it is possible to use a formula such as =[Date] + ([Duration Time]*31) However this would become inaccurate due to the fact that not every month has 31 days. You could change the 1/2/3/4 month idea to periods of days i.e. 30/31 then this method should be appropriate

what did you meen : chang 1/2/3/4 to period days ? how ?

 

[Mile-O]

still when i need to creat a report it will not appear , reports do not depend
on forms thats why I need to do that in a quiry depending on a table .

I did understand that your solution in form .. isn't it ?

Not quite. To be honest I'm a little unsure after revisiting your question.

Are you saying that you are actually putting a full date into the field Date ie. with a day, a month, and a year ?

If not, as it reads like you only intend on putting a month and a year into the field, then I'd recommend adding a Day, even if it's unnecessary.

The query can use the DateAdd() function on the full date to add the duration.

 

[samer]

Not quite. To be honest I'm a little unsure after revisiting your question.

Are you saying that you are actually putting a full date into the field Date ie. with a day, a month, and a year ?

If not, as it reads like you only intend on putting a month and a year into the field, then I'd recommend adding a Day, even if it's unnecessary.

The query can use the DateAdd() function on the full date to add the duration.

thanks a lot you have been helpful ...

 

[samer]

I think it is possible to use a formula such as =[Date] + ([Duration Time]*31) However this would become inaccurate due to the fact that not every month has 31 days. You could change the 1/2/3/4 month idea to periods of days i.e. 30/31 then this method should be appropriate

thanks for you I solve my issue .