ACCESS / EXCEL formula comparison

[Leadparsons]

Hi All,

New DB is working great thanks to all your help. Have a new problem.
I have 3 columns in excel and a 4th with a formula

Main Reject Return 60 days
27/08/2014 21/10/2014 22/10/2014 21/12/2014
24/01/2015 25/01/2015 DO REJECTIONS
17/02/2015 18/04/2015

"Main" is the first input date
"Reject" is the date it gets rejected
"Return" is the date returned after Reject

The 4th column needs to show 60 days from the "Main" date, "Do Rejections" if a date is in "Reject" and 60 days from "Return" date

Excel I use"=IF(AND(B2>0,C2>0),C2+60,IF(AND(B2>0,C2<1),"DO REJECTIONS",IF(AND(B2<1,A2>0),A2+60,0)))"

I Have tried in access IF(AND([Reject]>0,[Return]>0),[Return]+60,IF(AND([Reject]>0,[Return]<1),"DO REJECTIONS",IF(AND([Reject]<1,Main]>0),[Main]+60,0)))

Can anyone help ?

 

[Joe4]

Note. If you need more help in creating the formula, please re-post your data table from your first post. As you can see, all extra spaces are removed, so we cannot tell which columns your data is in. There are a few ways around this:

1. Use the tool described here: http://www.mrexcel.com/forum/about-board/508133-attachments.html . Note if you use this, I would recommend trying it out in the "Test Here" forum first, to make sure you have it working correctly before posting it here.

2. Use the Code Tags. This will maintain all extra spacing. It still won't look exactly like you type it in, but if you use the Advanced Editor, you can preview your post before posting it. So you can play around with it, inserting/removing spaces until things line up properly, before actually committing the post.

 

[Leadparsons]

So Could I combine IIF with AND

like - IIF(B2>0) AND (C2>0), C2+60)

 

[Leadparsons]

Ben,

the < or > are greater or less than expressions, if the date is less than 0 or null / the date is greater than or contains a date.

I f you are using the Expression Builder, below the space for the expression it says + - / * & ... And Or Like ...
However the syntax is different to Excel. I'm trying to write your query in Access so I'll come back when I've got the basic expression to work but you can't return both text and dates within the same field so be aware that you may have to approach this differently. Also your Expression contains terms like <1 what does this mean in the context of a date?

 

[Ben Nevis]

The following replicates your Excel formula:

ExprName: IIf([tblYourTable]![Reject]>0 And [tblYourTable]![Return]>0,[tblYourTable]![Return]+60,IIf([tblYourTable]![Reject]>0 And [tblYourTable]![Return]<1,"Do Rejections",IIf([tblYourTable]![Reject]<1 And [tblYourTable]![Main]>0,[tblYourTable]![Return]+60,0)))

Substitute tblYourTable with the name of your table. It won't return "Do Rejections" because it can't put text in a date field so you may need to split the date and the text between 2 fields in the query.

Also, the conditions <>0 and <>1 don't work well in this context you need to consider using IsNull where the field is empty and <> a calculated date since all valid dates are going to be >1

 

[Leadparsons]

I cant find the Test Here forum ?

Note. If you need more help in creating the formula, please re-post your data table from your first post. As you can see, all extra spaces are removed, so we cannot tell which columns your data is in. There are a few ways around this:

1. Use the tool described here: http://www.mrexcel.com/forum/about-board/508133-attachments.html . Note if you use this, I would recommend trying it out in the "Test Here" forum first, to make sure you have it working correctly before posting it here.

2. Use the Code Tags. This will maintain all extra spacing. It still won't look exactly like you type it in, but if you use the Advanced Editor, you can preview your post before posting it. So you can play around with it, inserting/removing spaces until things line up properly, before actually committing the post.

 

[Leadparsons]

I have 3 columns in excel and a 4th with a formula

Main Reject Return 60 days
27/08/2014 21/10/2014 22/10/2014 21/12/2014
24/01/2015 25/01/2015 DO REJECTIONS
17/02/2015 18/04/2015

"Main" is the first input date
"Reject" is the date it gets rejected
"Return" is the date returned after Reject

The 4th column needs to show 60 days from the "Main" date, 60 days from "Return" date or "Do Rejections" if a date is in "Reject" column

So I need an expression that states 60 days from which ever date is greater, the Main or Reject dates.
If there is no date in Reject than 60 days is worked from the Main date.
If there is a date in Reject then "Do rejections" should be displayed.
Once the rejections are complete and a date is populated in Returns then 60 days should be from that date.

Excel I use =IF(AND(Reject>0,Return>0),Return+60,IF(AND(Reject>0,Return<1),"DO REJECTIONS",IF(AND(Reject<1,Main>0),Main+60,0)))"

 

[Leadparsons]

OK, will give it a go

The following replicates your Excel formula:

ExprName: IIf([tblYourTable]![Reject]>0 And [tblYourTable]![Return]>0,[tblYourTable]![Return]+60,IIf([tblYourTable]![Reject]>0 And [tblYourTable]![Return]<1,"Do Rejections",IIf([tblYourTable]![Reject]<1 And [tblYourTable]![Main]>0,[tblYourTable]![Return]+60,0)))

Substitute tblYourTable with the name of your table. It won't return "Do Rejections" because it can't put text in a date field so you may need to split the date and the text between 2 fields in the query.

Also, the conditions <>0 and <>1 don't work well in this context you need to consider using IsNull where the field is empty and <> a calculated date since all valid dates are going to be >1

 

[Joe4]

So Could I combine IIF with AND

like - IIF(B2>0) AND (C2>0), C2+60)

No, the structure is bad. You are closing the parentheses before completing the IIF statement (missing a left parenthesis), and you do not have the 3rd argument (what to do if false - sometimes a nested IIF goes here).

It would be something like:

IIF((B2>0) AND (C2>0),C2+60, [I]what do do if false[/I])

I cant find the Test Here forum ?

See: Test Here

Post #16 just looks like a repeat of your original question. Not sure what you were trying to do there.

 

[Leadparsons]

Ben,

That works if there are dates in Main and Reject and Return, but I also want it to work out 60 days if only the main is populated.
Will think of something else for the "Do Rejections".

The following replicates your Excel formula:

ExprName: IIf([tblYourTable]![Reject]>0 And [tblYourTable]![Return]>0,[tblYourTable]![Return]+60,IIf([tblYourTable]![Reject]>0 And [tblYourTable]![Return]<1,"Do Rejections",IIf([tblYourTable]![Reject]<1 And [tblYourTable]![Main]>0,[tblYourTable]![Return]+60,0)))

Substitute tblYourTable with the name of your table. It won't return "Do Rejections" because it can't put text in a date field so you may need to split the date and the text between 2 fields in the query.

Also, the conditions <>0 and <>1 don't work well in this context you need to consider using IsNull where the field is empty and <> a calculated date since all valid dates are going to be >1

 

[Ben Nevis]

If you set up 2 fields in your query:

Expr 1: IIf(IsDate([tblYourTable]![Reject]) And IsDate([tblYourTable]![Return]),[tblYourTable]![Return]+60,IIf(IsNull([tblYourTable]![Reject]) And IsDate([tblYourTable]![Main]),[tblYourTable]![Main]+60,Null))


Expr2: IIf(IsDate([tblYourTable]![Reject]) And IsNull([tblYourTable]![Return]),"Do Rejections",Null)

The query will return a calculated date or null in the 1st column
or
It will return Do Rejections or Null in the 2nd column

Using is IsDate and IsNull avoids the ambiguity of < or > 0 or 1