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 ?

 

[Andrew Poulsom]

I know nothing about Access formulas but you appear to be missing an opening bracket for the first reference to Main.

 

[Leadparsons]

Entered, but still not working ;-(

 

[Ben Nevis]

In Access it's IIf not If.
Also, you only seem to be referencing the field names, normally these have to be preceded by the table name and a dot. Are you using the QBE grid to write your query?

 

[Leadparsons]

No im just writing it direct into a query field

 

[Leadparsons]

Don't know what QBE is and im entering it direct into a query field on the end.

 

[Ben Nevis]

If you are in the query field click on the icon in the toolbar that looks like a magician's wand it brings up the expression builder where you can find all the valid syntax

 

[Leadparsons]

That's fine, but I still can not get it to work even using the expression builder. Does Access have an AND expression ?

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 IIF(AND([Reject]>0,[Return]>0),[Return]+60,IIF(AND([Reject]>0,[Return]<1),"DO REJECTIONS",IIF(AND([Reject]<1,[Main]>0),[Main]+60,0)))

 

[Ben Nevis]

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?

 

[Joe4]

Does Access have an AND expression?

Yes, but it is structured differently (actually, it is structured more logically than Excel).

Instead of having this (like in Excel):

[COLOR=#333333]AND(B2>0,C2>0)[/COLOR]

it would look like this in Access:

[COLOR=#333333](B2>0) AND (C2>0)[/COLOR]

Also, Access uses the "Immediate If" (IIF) instead if "If". See: http://www.techonthenet.com/access/functions/advanced/iif.php