Concept: In Access there is a function, IIF() (immediate IF), that has the syntax
IIF(
Condition,
Value if TRUE,
Value if FALSE)
The condition has to evaluate to either TRUE or FALSE
The "Value if TRUE" can be hard coded, ie 6 or "Hello", NULL or the result of a calculation.
The "Value if FALSE" can be hard coded, ie 6 or "Hello", NULL or the result of a calculation.
You have the formula in Excel, you just have substitute Access names for Excel column names.
The formula in Excel:
Code:
=IF(B56=B55,E56-E55,"")
Converted Formula to Access query:
Code:
XDelta: IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![UniqueNumber]<>[previmport]![UniqueNumber],Null,[currimport]![X_Mtr]-[previmport]![X_Mtr]))
--------------------
How to get there:
Let's look at the formula in Excel column F.......
Code:
=IF(B56=B55,E56-E55,"")
Excel: The column name is "X Delta" (from row 1)
Excel: Column B is the "Unique_Number" and column E is "X_Mtr" (remember, must convert to Access column names- no spaces)
Excel: The row number tells you if it is the previous record or current record.
query: The column name " XDelta:" (must include the colon)
"B56=B55" becomes
"Unique_Number56=Unique_Number55"
55 and 56 are the previous and current rows, so now the formula becomes
"[currimport]!Unique_Number=[previmport]!Unique_Number"
"the value if TRUE" argument in the Excel formula is "E56-E55" Replacing the column, we have
"X_Mtr56-X_Mtr55"
Then replacing the row, we get
"[currimport]!X_Mtr - [previmport]!X_Mtr"
And , of course,
"" is equal to
NULL. ("the value if FALSE")
Now the Excel formula converted To Access looks like:
Code:
XDelta: IIF([currimport]!Unique_Number=[previmport]!Unique_Number,[currimport]!X_Mtr - [previmport]!X_Mtr , NULL)
rpeare added a check for the date. So there is another IIF(), resulting in nested IIF() statments.
XDelta:IIF(
IsNull([previmport]![Cax_Time]),
Null,
IIF([currimport]!Unique_Number=[previmport]!Unique_Number ,[currimport]!X_Mtr - [previmport]!X_Mtr , NULL))
There are two ways to create a formula: Positive logic or negative logic. One is not better that the other. Both have their uses.
The 2 of the formulas,
XDelta and
XDay use negative logic.
Converting positive logic to negative logic is easy. Change the "=" to "<>" and swap the
Value if TRUE,
Value if FALSE clauses.
Changing the above formula to negative logic, we get
XDelta:IIF(
IsNull([previmport]![Cax_Time]),
Null,
IIF([currimport]!Unique_Number<>[previmport]!Unique_Number , NULL ,[currimport]!X_Mtr - [previmport]!X_Mtr))
----------------------------
XDelta and
XDay look to be the hardest formulas to convert. I wouldn't think you would have any trouble converting the rest of the Excel calculated columns.