AND OR Formula

[alecmat]

Afternoon,

I am trying to write a formula in ACCESS for the first time. I would like to write the below, its a basic IF Then statement that involves "OR" and "And".. In Excel it would be:

if(AND(OR(Freight terms=PP, Freight terms=PC, Freight terms=CC),Arrive in Yard>0,Current Appt>End ETA,1,2))

But how do you write in in Access? (the below is my attempt)

= iif(( [Freight Terms]="PP" OR [Freight Terms]="PC" OR [Freight Terms]="CC" AND [Arrive in Yard] >0 And [Current Appt] > [End ETA] ,"1",2))

Any asistance would be appreciated.



Thanks
-Alec

[alecmat]

Correction on my first post: Cleaned up verison of the formula is below, which is functional, but does not return the desired data...What is the best way to say "if a cell is not blank" in Access ? I am using ">0", but that may not be best in Access........ Thanks Again!

Expr1: IIf([Freight Terms]="PP" Or [Freight Terms]="PC" Or [Freight Terms]="CC" And [Arrive in Yard]>0 And [Current Appt]>[End ETA],"1","2")

[alansidman]

Is Not Null. A point to keep in mind in Access, there is no such thing as a blank field. It is either Null or not Null. In Excel, if you have a space or "" in a cell, it is considered to be empty or equal to zero. In Access this is not the case as it is deemed to hold data. It truly must be null.

[alecmat]

Is Not Null. A point to keep in mind in Access, there is no such thing as a blank field. It is either Null or not Null. In Excel, if you have a space or "" in a cell, it is considered to be empty or equal to zero. In Access this is not the case as it is deemed to hold data. It truly must be null.

Ok Thanks for the clarification.

[alecmat]

Alansidman,

Sadly, I received an error whey I attempted the "Is Not Null" in the formula...please see the below:

I simply want to say if that is the below cell (Freight Terms) is NOT BLANK (i.e, there is data in the cell), then to give me a 1, if not, give me a 2. How would I write that?

I am getting an error that states "undefined function"IsNotNull" in expression", and I am trying to understand why. Unlike Excel, the IsBlank() function does not appear to work...Any assistance would be appreciated

Expr2: IIf(IsNotNull([Freight Terms]),"1","2")

Thank You,

[alecmat]

Alansidman,

Sadly, I received an error whey I attempted the "Is Not Null" in the formula...please see the below:

I simply want to say if that is the below cell (Freight Terms) is NOT BLANK (i.e, there is data in the cell), then to give me a 1, if not, give me a 2. How would I write that?

I am getting an error that states "undefined function"IsNotNull" in expression", and I am trying to understand why. Unlike Excel, the IsBlank() function does not appear to work...Any assistance would be appreciated

Expr2: IIf(IsNotNull([Freight Terms]),"1","2")

Thank You,

Upon researching Access functions, I have not come across Is Not Null...However, the functions IsNumeric() and IsDate() appear to solve this issue in Access.
If that offers any value to anyone.

[alansidman]

See my response here: http://www.excelforum.com/access-tab...-not-null.html

Cross posted.

Please read: http://www.excelguru.ca/content.php?184