AND Expression

[mardimus]

I am trying to create an expression that lists a value in a new column based on the logic that a date has passed today. Any help on how to recreate this as the Today or AND expression is not working.

Was Project Delinquent?:=IIF(AND(IsNull(Date1),[Date2]<TODAY()),1,0)

Thanks for any help.

Marty

 

[alansidman]

The syntax in Access is slightly different from Excel. try this: =IIF(IsNull(Date1) and IsNull(Date2)

 

[mardimus]

Thanks for the info. I will try it. Also, my expression/function statement was cut off. Here is an updated one: =IIF(And(IsNull([DATE1]),[DATE2]<Today()),1,0)

The syntax in Access is slightly different from Excel. try this: =IIF(IsNull(Date1) and IsNull(Date2)

 

[mardimus]

This is close but not there yet. Was Project Delinquent?:=IIF(IsNull(Date1) and IsNull(Date2)<Today()),1,0).

Thoughts?

 

[Joe4]

You IIF formula is incomplete. IIF has three arguments (see: MS Access: iif Function).

Perhaps your formula is being cut off. If so, see here: http://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[mardimus]

Darn, I did not even notice it. Here is the formula again:Was Project Delinquent?:=IIF(IsNull(Act RFU) and IsNull(Updated RFU) < Today (),1,0)

You IIF formula is incomplete. IIF has three arguments (see: MS Access: iif Function).

Perhaps your formula is being cut off. If so, see here: http://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[Joe4]

Your first argument does not make sense:

[COLOR=#333333]IsNull(Act RFU) and IsNull(Updated RFU) < Today ()[/COLOR]

IsNull returns True or False (is the value Null or not?), so I am not sure why you are trying to compare True/False values to Today().

Can you explain exactly what it is you are trying to do here?

 

[mardimus]

The logic (in excel) is: IF(AND(ISBLANK([@[Act RFU]]),[@[Comp RFU]] < TODAY()),1,0)

It basically states that if one of the fields is blank then use the other field and is the date greater than today, if so then assign it a value.

I need to convert this to an expression in access.

Your first argument does not make sense:

[COLOR=#333333]IsNull(Act RFU) and IsNull(Updated RFU) < Today ()[/COLOR]

IsNull returns True or False (is the value Null or not?), so I am not sure why you are trying to compare True/False values to Today().

Can you explain exactly what it is you are trying to do here?

 

[Joe4]

What your expression actually states is the following:

If BOTH:
- [Act RFU] is blank
AND
- [Comp RFU] is less than today
THEN return 1,
OTHERWISE return 0.

So it is NOT checking to see if [Act RFU] is less than today, nor is it checking to see if [Comp RFU] is blank.

Is that exactly what you want it to do?

 

[mardimus]

Sorry, Your explanation is more clear than mine. I had to re-read my statement when I read yours and your logic is clear and correct. My apologies as I am currently multitasking. What you stated below is correct. Because the actual RFU is blank the majority of the time and the Comp RFU is always a value.

If BOTH:
- [Act RFU] is blank
AND
- [Comp RFU] is less than today
THEN return 1,
OTHERWISE return 0.

I need to convert this logic to access. If possible. Thanks for the clarity.

What your expression actually states is the following:

If BOTH:
- [Act RFU] is blank
AND
- [Comp RFU] is less than today
THEN return 1,
OTHERWISE return 0.

So it is NOT checking to see if [Act RFU] is less than today, nor is it checking to see if [Comp RFU] is blank.

Is that exactly what you want it to do?