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
Multiple formula in one field in MS Access
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
-
- Administrator
- Posts: 78574
- 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]
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
Hans
-
- 2StarLounger
- Posts: 184
- 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
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
-
- Administrator
- Posts: 78574
- 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.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 14
- 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.
-
- 2StarLounger
- Posts: 184
- 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
thanks
VKKT