Multiple formula in one field in MS Access

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

Multiple formula in one field in MS Access

Post by VKKT »

Hi All
Greetings, :cheers:

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Multiple formula in one field in MS Access

Post by 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]
Best wishes,
Hans

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

Re: Multiple formula in one field in MS Access

Post by 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Multiple formula in one field in MS Access

Post by 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.
Best wishes,
Hans

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

Re: Multiple formula in one field in MS Access

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

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

Re: Multiple formula in one field in MS Access

Post by VKKT »

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