Multiple formula in one field in MS Access

[VKKT]

Hi All
Greetings,

After a long time I am back with some queries.

Field 1: Date to PM
Field 2: Date from PM (this may be blank if not received from PM)

a) Calculate days between 2 & 1 ( i.e days taken by PM)
b) Calculate days pending 2 & 1 (i.e days pending till date with PM, if 2 is blank)

How to combine the above two formula in one field in MS Access Form.

Regards,
VKKT

[HansV]

Try this in a query based on the table:

DifferenceL Nz([Field 2],Date())-[Field 1]

Alternatively, in the Control Source of a text box on a form or report bound to the table:

=Nz([Field 2],Date())-[Field 1]

[VKKT]

Thanks Mr. Hans for the immediate response as usual.

I forget to mention one point, if the Feild 2 is not blank, then the result should be blank, i.e no days pending. Is this possible?

Regards,
VKKT

[HansV]

You want to calculate the days between 1 and 2, but if 2 is not empty, the result should be empty. I don't understand that.

[EileenStifone]

In the query you must enter the following instructions in two columns:

Code: Select all

DaysTakenByPM:IIf(IsNull([Field 2]);0;Date()-[Field 1])

Code: Select all

DaysPendingByDate:IIf(IsNull([Field 2]);Date()-[Field 1];0)

[VKKT]

Sorry Hans, you are correct, I was confused.
thanks
VKKT