textbox usage for calculations

[John]

I am in a quandry and am looking for guidance. I have a
form that has on it various controls. My issue revolves
around the tboxes that show the following info;

DateInvoiced (manually enter date)
DateInvoiceDue (this calculates out with an
expression =DateInvoiced + 30)
DateAmountReceived (manually enter date)
AgedDueTime (would like to calculate out)

It is the AgedDueTime field that there is questions
about. I would like to track the aged receivables. The
DateInvoiced will always have a date in it as soon as the
invoice goes out. The DateInvoiceDue calculates as soon
as the DateInvoiced is entered. The DateAmountReceived
is not completed until the money arrives. I would like
to have the AgedDueTime keep the days beginning with the
DateInvoiced. The days prior to DateInvoiceDue would be
positive numbers and after the DateInvoiceDue would be
negative numbers. The days continue to count until the
DateAmountReceived is filled in then it would stop
counting and continue to show the number of days (whether
positive or negative). I have tried DateDiff, IIF, If-
Then,expressions, and Nx(). I can not get a number in
the Aged like I would like. I shows once the DateRecvd
is completed.
Anyone have ideas on this?
Thanks in advance.
*** John

 

[Allen Browne]

AgedDueTime is a calculated field, I presume?
Set its Control Source to something like this:

=IIF(IsNull([DateAmountReceived]),
[DateInvoiceDue] - Date(),
[DateAmountReceived] - [DateInvoiceDue])

 

[John]

Allen, it worked. Thanks from the other side of the
world.
*** John

-----Original Message-----
AgedDueTime is a calculated field, I presume?
Set its Control Source to something like this:

=IIF(IsNull([DateAmountReceived]),
[DateInvoiceDue] - Date(),
[DateAmountReceived] - [DateInvoiceDue])

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

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

I am in a quandry and am looking for guidance. I have a
form that has on it various controls. My issue revolves
around the tboxes that show the following info;

DateInvoiced (manually enter date)
DateInvoiceDue (this calculates out with an
expression =DateInvoiced + 30)
DateAmountReceived (manually enter date)
AgedDueTime (would like to calculate out)

It is the AgedDueTime field that there is questions
about. I would like to track the aged receivables. The
DateInvoiced will always have a date in it as soon as the
invoice goes out. The DateInvoiceDue calculates as soon
as the DateInvoiced is entered. The DateAmountReceived
is not completed until the money arrives. I would like
to have the AgedDueTime keep the days beginning with the
DateInvoiced. The days prior to DateInvoiceDue would be
positive numbers and after the DateInvoiceDue would be
negative numbers. The days continue to count until the
DateAmountReceived is filled in then it would stop
counting and continue to show the number of days (whether
positive or negative). I have tried DateDiff, IIF, If-
Then,expressions, and Nx(). I can not get a number in
the Aged like I would like. I shows once the DateRecvd
is completed.
Anyone have ideas on this?
Thanks in advance.
*** John

.