Use Expression Builder to write a Nested IF Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Can anyone help me to convert the following Excel function into an Access
Expression?

IF((TODAY()-[Date_X])<0,"Message 1",IF((TODAY()-[Date_X])<=2,"Message
2","Yes"))

I am not sure how to write a nested function using the Expression Builder.

Really appreciate anyone's help on this! Thks in advance! ;-)
 
Change 'IF' to 'IIF' and 'TODAY()' to 'Date()'.

In Access, 'If' is used in VBA code, but 'IIf' (Immediate If) in expressions
in queries, control source properties, etc.
 
Hi Brendan,

I am familiar with the IIF and Date() function. But am unable to link the
two IF() functions together to become a Nested Function. Any ideas? ;-)

Brendan Reynolds said:
Change 'IF' to 'IIF' and 'TODAY()' to 'Date()'.

In Access, 'If' is used in VBA code, but 'IIf' (Immediate If) in expressions
in queries, control source properties, etc.

--
Brendan Reynolds (MVP)

jy said:
Hi,

Can anyone help me to convert the following Excel function into an Access
Expression?

IF((TODAY()-[Date_X])<0,"Message 1",IF((TODAY()-[Date_X])<=2,"Message
2","Yes"))

I am not sure how to write a nested function using the Expression Builder.

Really appreciate anyone's help on this! Thks in advance! ;-)
Click to expand...
Click to expand...
 
Brendan's suggesting

IIf((Date()-[Date_X])<0,"Message 1",IIf((Date()-[Date_X])<=2,"Message
2","Yes"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jy said:
Hi Brendan,

I am familiar with the IIF and Date() function. But am unable to link the
two IF() functions together to become a Nested Function. Any ideas? ;-)

Brendan Reynolds said:
Change 'IF' to 'IIF' and 'TODAY()' to 'Date()'.

In Access, 'If' is used in VBA code, but 'IIf' (Immediate If) in
expressions
in queries, control source properties, etc.

--
Brendan Reynolds (MVP)

jy said:
Hi,

Can anyone help me to convert the following Excel function into an
Access
Expression?

IF((TODAY()-[Date_X])<0,"Message 1",IF((TODAY()-[Date_X])<=2,"Message
2","Yes"))

I am not sure how to write a nested function using the Expression
Builder.

Really appreciate anyone's help on this! Thks in advance! ;-)
Click to expand...
Click to expand...
Click to expand...
 
Hi Douglas and Brendan,

My expression is as below:

Hold Limit: (IIf ( ( Date()- [Max Hold Date] ) <= 0,"EXCEEDED!", IIf (Date()
- [Max Hold Date] )<=2 , "TRIGGERED!", "Yes" ) )

But the error message showed as: "The expression you entered has a function
containing the wrong number of arguments".

Is there anything wrong with my expression?

Thks! ;-)




Douglas J. Steele said:
Brendan's suggesting

IIf((Date()-[Date_X])<0,"Message 1",IIf((Date()-[Date_X])<=2,"Message
2","Yes"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jy said:
Hi Brendan,

I am familiar with the IIF and Date() function. But am unable to link the
two IF() functions together to become a Nested Function. Any ideas? ;-)

Brendan Reynolds said:
Change 'IF' to 'IIF' and 'TODAY()' to 'Date()'.

In Access, 'If' is used in VBA code, but 'IIf' (Immediate If) in
expressions
in queries, control source properties, etc.

--
Brendan Reynolds (MVP)

Hi,

Can anyone help me to convert the following Excel function into an
Access
Expression?

IF((TODAY()-[Date_X])<0,"Message 1",IF((TODAY()-[Date_X])<=2,"Message
2","Yes"))

I am not sure how to write a nested function using the Expression
Builder.

Really appreciate anyone's help on this! Thks in advance! ;-)
Click to expand...
Click to expand...
Click to expand...
 
I think you probably have one parenthesis too many, or one too few, or one
in the wrong place, but I'm having a hard time figuring out just which one!
:-(

This works for me ...

Hold Limit:
IIf(Date()-[TestDate]<=0,"EXCEEDED!",IIf(Date()-[TestDate]<=2,"TRIGGERED!","Yes"))

--
Brendan Reynolds (MVP)

jy said:
Hi Douglas and Brendan,

My expression is as below:

Hold Limit: (IIf ( ( Date()- [Max Hold Date] ) <= 0,"EXCEEDED!", IIf
(Date()
- [Max Hold Date] )<=2 , "TRIGGERED!", "Yes" ) )

But the error message showed as: "The expression you entered has a
function
containing the wrong number of arguments".

Is there anything wrong with my expression?

Thks! ;-)




Douglas J. Steele said:
Brendan's suggesting

IIf((Date()-[Date_X])<0,"Message 1",IIf((Date()-[Date_X])<=2,"Message
2","Yes"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jy said:
Hi Brendan,

I am familiar with the IIF and Date() function. But am unable to link
the
two IF() functions together to become a Nested Function. Any ideas? ;-)

:

Change 'IF' to 'IIF' and 'TODAY()' to 'Date()'.

In Access, 'If' is used in VBA code, but 'IIf' (Immediate If) in
expressions
in queries, control source properties, etc.

--
Brendan Reynolds (MVP)

Hi,

Can anyone help me to convert the following Excel function into an
Access
Expression?

IF((TODAY()-[Date_X])<0,"Message
1",IF((TODAY()-[Date_X])<=2,"Message
2","Yes"))

I am not sure how to write a nested function using the Expression
Builder.

Really appreciate anyone's help on this! Thks in advance! ;-)
Click to expand...
Click to expand...
Click to expand...
 
Hi Brendan,

Thanks so much!! It works!! Yippee!! ;-D



Brendan Reynolds said:
I think you probably have one parenthesis too many, or one too few, or one
in the wrong place, but I'm having a hard time figuring out just which one!
:-(

This works for me ...

Hold Limit:
IIf(Date()-[TestDate]<=0,"EXCEEDED!",IIf(Date()-[TestDate]<=2,"TRIGGERED!","Yes"))

--
Brendan Reynolds (MVP)

jy said:
Hi Douglas and Brendan,

My expression is as below:

Hold Limit: (IIf ( ( Date()- [Max Hold Date] ) <= 0,"EXCEEDED!", IIf
(Date()
- [Max Hold Date] )<=2 , "TRIGGERED!", "Yes" ) )

But the error message showed as: "The expression you entered has a
function
containing the wrong number of arguments".

Is there anything wrong with my expression?

Thks! ;-)




Douglas J. Steele said:
Brendan's suggesting

IIf((Date()-[Date_X])<0,"Message 1",IIf((Date()-[Date_X])<=2,"Message
2","Yes"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi Brendan,

I am familiar with the IIF and Date() function. But am unable to link
the
two IF() functions together to become a Nested Function. Any ideas? ;-)

:

Change 'IF' to 'IIF' and 'TODAY()' to 'Date()'.

In Access, 'If' is used in VBA code, but 'IIf' (Immediate If) in
expressions
in queries, control source properties, etc.

--
Brendan Reynolds (MVP)

Hi,

Can anyone help me to convert the following Excel function into an
Access
Expression?

IF((TODAY()-[Date_X])<0,"Message
1",IF((TODAY()-[Date_X])<=2,"Message
2","Yes"))

I am not sure how to write a nested function using the Expression
Builder.

Really appreciate anyone's help on this! Thks in advance! ;-)
Click to expand...
Click to expand...
Click to expand...
 
Hi,
Thanks for such a great website. It has been very useful to me. I have done an IF formula to calculate Tax in excel but would like to write it as a an expression in access, can anyone help pls? Please see the formula below.
Pls note that if taxable income is 5,751,882 then tax = 1,605,565.

IF(TAXABLE INCOME<=150000,0,IF(TAXABLE INCOME<=450000,0.15*(450000-150000),45000))+IF((TAXABLE INCOME-450000)<300000, (TAXABLE INCOME-450000)*0.2,60000)+IF((TAXABLE INCOME-750000)>0,(TAXABLE INCOME-750000)*0.3,0)

Thanks
 
Back
Top