Multiple formula in one field in MS Access

VKKT
2StarLounger
Posts: 179
Joined: 13 Jun 2018, 07:50

Multiple formula in one field in MS Access

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
Posts: 76155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Multiple formula in one field in MS Access

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]
Regards,
Hans

VKKT
2StarLounger
Posts: 179
Joined: 13 Jun 2018, 07:50

Re: Multiple formula in one field in MS Access

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
Posts: 76155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Multiple formula in one field in MS Access

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.
Regards,
Hans

EileenStifone
NewLounger
Posts: 8
Joined: 16 Aug 2022, 05:39

Re: Multiple formula in one field in MS Access

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)``
Last edited by EileenStifone on 25 Nov 2022, 14:48, edited 2 times in total.

VKKT
2StarLounger
Posts: 179
Joined: 13 Jun 2018, 07:50

Re: Multiple formula in one field in MS Access

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