Need to create small macro that calculates values of a column using simple excel formulas!

[rpeare]

1. I am using COLC to calculate the day rate (your second column of calculated values on the example spreadsheet)



How I am choosing to display it is the only thing I'm doing, I wanted it as a short date because I was comparing my results to the results on the spreadsheet and I'm a bit cramped for space on this computer.

2. To get a running sum of a field on a report you do this:

a. Add the original field to the report
b. Add the original field to the report again
c. Go to the PROPERTIES of the second field and look for the RUNNING SUM field, you can choose 'over all' or 'over group', you choose based on what you want for your final look.
d. Run the report

you'll see two columns side by side 1 with the original value and one with the cumulative sum of values.

[hacerz]

1. I am using COLC to calculate the day rate (your second column of calculated values on the example spreadsheet)

How I am choosing to display it is the only thing I'm doing, I wanted it as a short date because I was comparing my results to the results on the spreadsheet and I'm a bit cramped for space on this computer.

2. To get a running sum of a field on a report you do this:

a. Add the original field to the report
b. Add the original field to the report again
c. Go to the PROPERTIES of the second field and look for the RUNNING SUM field, you can choose 'over all' or 'over group', you choose based on what you want for your final look.
d. Run the report

you'll see two columns side by side 1 with the original value and one with the cumulative sum of values.

if I got this right, you are using short date for colc to calculate the "day rate" but I don't see any day rate column. If you are talking about the same day column then i think it will go best with the timestamp because if there are same date rows but with different timestamp then it can identify the difference between the time and give the correct result.

You may be true because even if the date is same then all we are saying is DL-same day.


I will try those steps and hopefully it will fulfil my sum for segment data column.
Thanks

[rpeare]

I am not using the short date for a day calculation.. I'm not sure how much clearer it can be than the example. If I were using the date part of the field only I would be using something like datediff("d", dayx, dayy).

you just have to be careful in this example that you've essentially got the same table linked into the table twice so you have to reference TABLE as well as FIELD in your query

i.e. [CurrItem]![ColC]

[ssanfu]

@rpeare, nice queries. I tried to do the same thing but I forgot about using a Cartesian join.

I used your queries (and my table/field names) and started a test. For just a number like 1002, the query completed fairly fast and the numbers matched. Then I ran the query (just the column calcs you had) on the full dataset (~70,000+ records). After 2 hrs the query is still running. (Pent 4 dual core, 3.2 GHz, 4 GB ram)
Will be even slower with the rest of the calculated columns.


@hacerz, here is my version of the dB. You will have to import the data from Data.xlsx - Access with the full data set was to large to upload.

Have you thought about using code in Excel to sort the rows and copy down the formulas and not bother with Access? Might be easier and faster...

[hacerz]

@rpeare, nice queries. I tried to do the same thing but I forgot about using a Cartesian join.

I used your queries (and my table/field names) and started a test. For just a number like 1002, the query completed fairly fast and the numbers matched. Then I ran the query (just the column calcs you had) on the full dataset (~70,000+ records). After 2 hrs the query is still running. (Pent 4 dual core, 3.2 GHz, 4 GB ram)
Will be even slower with the rest of the calculated columns.


@hacerz, here is my version of the dB. You will have to import the data from Data.xlsx - Access with the full data set was to large to upload.

Have you thought about using code in Excel to sort the rows and copy down the formulas and not bother with Access? Might be easier and faster...

Thanks. I will check your database version. The excel has the auto-fill option and thats what it will calculate but i have to sort like almost 700 or more unique numbers manually and then auto fill the new rows if any and then do the same for all the other numbers. where the macro in access comes in handy where I just run a macro and it opens the data file either import it or its been linked already and then sees new rows and calculates the column values.

I am not sure if you have all the queries for all the 10 columns. @rpeare had the nice queries for four of them and he gave advice on others too which is very nice of him.

so just so i got this right, your database has same queries which rpeare had, but u ran them for all the 70,000 ~ rows and it took so long?


ALso please let me know if you have the queries for the other columns .

Thanks

[hacerz]

I am not using the short date for a day calculation.. I'm not sure how much clearer it can be than the example. If I were using the date part of the field only I would be using something like datediff("d", dayx, dayy).

you just have to be careful in this example that you've essentially got the same table linked into the table twice so you have to reference TABLE as well as FIELD in your query

i.e. [CurrItem]![ColC]

Thanks for making it clear man. I just had a doubt in my mind. Ya, i will try the other queries and let you know if I am sucessfull.

Thanks

[rpeare]

You're right this is running slowly with a 70,000 record file, the solution here is to append the cartesian query to a table then run it. The cartesian took about 3 - 4 minutes to run. I'm not normally a fan of temp tables but this would be a good place for one.

This the code I ran to populate a temp table:

Dim db As DAO.Database
Dim sSQL As String
Dim rst As DAO.Recordset
Dim sPrevNum
Dim sCurrNum
Dim dPrevDate
Dim dCurrDate
Dim dStart As Date
Dim dEnd As Date

dStart = Now
Set db = CurrentDb
db.Execute ("DELETE * FROM tblTemp")

sSQL = "SELECT UniqueNumber, Cax_Time FROM myData ORDER BY UniqueNumber, Cax_Time"
Set rst = db.OpenRecordset(sSQL)

Do While rst.EOF <> True
    sCurrNum = rst!uniquenumber
    dCurrDate = rst!cax_time
    If sCurrNum <> sPrevNum Then
        sSQL = "INSERT INTO tblTemp (UniqueNumber, CurrentRecord) VALUES ("
        sSQL = sSQL & "'" & sCurrNum & "', "
        sSQL = sSQL & "#" & dCurrDate & "#)"
        db.Execute sSQL
    Else
        sSQL = "INSERT INTO tblTemp (UniqueNumber, CurrentRecord, PrevRecord) VALUES ("
        sSQL = sSQL & "'" & sCurrNum & "', "
        sSQL = sSQL & "#" & dCurrDate & "#, "
        sSQL = sSQL & "#" & dPrevDate & "#)"
        db.Execute sSQL
    End If
    sPrevNum = sCurrNum
    dPrevDate = dCurrDate
    rst.MoveNext
Loop
dEnd = Now
Debug.Print "Run Time: " & DateDiff("s", dStart, dEnd)
Debug.Print "Done"

Set db = Nothing

It ran in about 30 seconds.

I then ran the query qryFinal and it ran pretty quickly as well, however there's some problem with the date/time field, it's not able to link by it consistently and I"m not sure why I'm looking into it now.

[rpeare]

Ok I had to change the code because it is getting late and I can't figure out why the time is not linking correctly so here's what I did:

FIRST - I created a table named tblTemp. It has two fields CurrentRecord and PrevRecord, both data types are long integer

SECOND - Ran this code:

Dim db As DAO.Database
Dim sSQL As String
Dim rst As DAO.Recordset
Dim sPrevNum
Dim dStart As Date
Dim dEnd As Date
Dim iPrevID As Long

dStart = Now
Set db = CurrentDb
db.Execute ("DELETE * FROM tblTemp")

sSQL = "SELECT Xlid, UniqueNumber, Cax_Time FROM myData ORDER BY UniqueNumber, Cax_Time"
Set rst = db.OpenRecordset(sSQL)

Do While rst.EOF <> True
    If rst!uniquenumber <> sPrevNum Then
        db.Execute ("INSERT INTO tblTemp (CurrentRecord) VALUES (" & rst!xlid & ")")
    Else
        db.Execute ("INSERT INTO tblTemp (CurrentRecord, PrevRecord) VALUES (" & rst!xlid & ", " & iPrevID & ")")
    End If
    iPrevID = rst!xlid
    sPrevNum = rst!uniquenumber
    rst.MoveNext
Loop
dEnd = Now
Debug.Print "Run Time: " & DateDiff("s", dStart, dEnd)
Debug.Print "Done"

Set db = Nothing

THIRD - I ran the query qryFinal, but with this modified SQL

SELECT CurrImport.NName, CurrImport.UniqueNumber, CurrImport.Cax_Time, CurrImport.Cax_Time, CurrImport.Cx_Mtr, CurrImport.X_Mtr, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![UniqueNumber]<>[previmport]![UniqueNumber],Null,[currimport]![X_Mtr]-[previmport]![X_Mtr])) AS XDelta, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![UniqueNumber]<>[previmport]![UniqueNumber],Null,IIf([previmport]![Cax_Time]-[currimport]![Cax_Time]=0,0,([currimport]![X_Mtr]-[previmport]![X_Mtr])/([currimport]![Cax_Time]-[previmport]![Cax_Time])))) AS XDay, IIf([xday]>=0 And [xday]<19,"OK","Discontinuous") AS Check1, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![Cax_Time]-[previmport]![Cax_Time]<1 And [check1]="Discontinuous","DL-Same Day",Null)) AS SameDay
FROM (tblTemp LEFT JOIN MyData AS CurrImport ON tblTemp.CurrentRecord = CurrImport.Xlid) LEFT JOIN MyData AS PrevImport ON tblTemp.PrevRecord = PrevImport.Xlid
ORDER BY CurrImport.UniqueNumber, CurrImport.Cax_Time;

The code runs in about 25 seconds
The query runs almost instantly

Note this relies on Ssnafu's table (MyData) having a system generated ID (autonumber) associated with it.

[hacerz]

Ok I had to change the code because it is getting late and I can't figure out why the time is not linking correctly so here's what I did:

FIRST - I created a table named tblTemp. It has two fields CurrentRecord and PrevRecord, both data types are long integer

SECOND - Ran this code:

Dim db As DAO.Database
Dim sSQL As String
Dim rst As DAO.Recordset
Dim sPrevNum
Dim dStart As Date
Dim dEnd As Date
Dim iPrevID As Long

dStart = Now
Set db = CurrentDb
db.Execute ("DELETE * FROM tblTemp")

sSQL = "SELECT Xlid, UniqueNumber, Cax_Time FROM myData ORDER BY UniqueNumber, Cax_Time"
Set rst = db.OpenRecordset(sSQL)

Do While rst.EOF <> True
    If rst!uniquenumber <> sPrevNum Then
        db.Execute ("INSERT INTO tblTemp (CurrentRecord) VALUES (" & rst!xlid & ")")
    Else
        db.Execute ("INSERT INTO tblTemp (CurrentRecord, PrevRecord) VALUES (" & rst!xlid & ", " & iPrevID & ")")
    End If
    iPrevID = rst!xlid
    sPrevNum = rst!uniquenumber
    rst.MoveNext
Loop
dEnd = Now
Debug.Print "Run Time: " & DateDiff("s", dStart, dEnd)
Debug.Print "Done"

Set db = Nothing

THIRD - I ran the query qryFinal, but with this modified SQL

SELECT CurrImport.NName, CurrImport.UniqueNumber, CurrImport.Cax_Time, CurrImport.Cax_Time, CurrImport.Cx_Mtr, CurrImport.X_Mtr, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![UniqueNumber]<>[previmport]![UniqueNumber],Null,[currimport]![X_Mtr]-[previmport]![X_Mtr])) AS XDelta, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![UniqueNumber]<>[previmport]![UniqueNumber],Null,IIf([previmport]![Cax_Time]-[currimport]![Cax_Time]=0,0,([currimport]![X_Mtr]-[previmport]![X_Mtr])/([currimport]![Cax_Time]-[previmport]![Cax_Time])))) AS XDay, IIf([xday]>=0 And [xday]<19,"OK","Discontinuous") AS Check1, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![Cax_Time]-[previmport]![Cax_Time]<1 And [check1]="Discontinuous","DL-Same Day",Null)) AS SameDay
FROM (tblTemp LEFT JOIN MyData AS CurrImport ON tblTemp.CurrentRecord = CurrImport.Xlid) LEFT JOIN MyData AS PrevImport ON tblTemp.PrevRecord = PrevImport.Xlid
ORDER BY CurrImport.UniqueNumber, CurrImport.Cax_Time;

The code runs in about 25 seconds
The query runs almost instantly

Note this relies on Ssnafu's table (MyData) having a system generated ID (autonumber) associated with it.

Just so I got this right, I am gonna have to put my attention to the last code in your last post#38 and not the #37 right? Can you please just tell me how to run these codes. If I am not wrong I just create a new blank module and put in the code inside the function and just run it and save it with any name for later use.....I also see a query , where should I put it and run it.

Also what db I am working on @rpeare or @ssanfu for these queries and macro to work?


Please let me know.

Thanks

[hacerz]

@rpeare, nice queries. I tried to do the same thing but I forgot about using a Cartesian join.

I used your queries (and my table/field names) and started a test. For just a number like 1002, the query completed fairly fast and the numbers matched. Then I ran the query (just the column calcs you had) on the full dataset (~70,000+ records). After 2 hrs the query is still running. (Pent 4 dual core, 3.2 GHz, 4 GB ram)
Will be even slower with the rest of the calculated columns.


@hacerz, here is my version of the dB. You will have to import the data from Data.xlsx - Access with the full data set was to large to upload.

Have you thought about using code in Excel to sort the rows and copy down the formulas and not bother with Access? Might be easier and faster...

Ok I had to change the code because it is getting late and I can't figure out why the time is not linking correctly so here's what I did:

FIRST - I created a table named tblTemp. It has two fields CurrentRecord and PrevRecord, both data types are long integer

SECOND - Ran this code:

Dim db As DAO.Database
Dim sSQL As String
Dim rst As DAO.Recordset
Dim sPrevNum
Dim dStart As Date
Dim dEnd As Date
Dim iPrevID As Long

dStart = Now
Set db = CurrentDb
db.Execute ("DELETE * FROM tblTemp")

sSQL = "SELECT Xlid, UniqueNumber, Cax_Time FROM myData ORDER BY UniqueNumber, Cax_Time"
Set rst = db.OpenRecordset(sSQL)

Do While rst.EOF <> True
    If rst!uniquenumber <> sPrevNum Then
        db.Execute ("INSERT INTO tblTemp (CurrentRecord) VALUES (" & rst!xlid & ")")
    Else
        db.Execute ("INSERT INTO tblTemp (CurrentRecord, PrevRecord) VALUES (" & rst!xlid & ", " & iPrevID & ")")
    End If
    iPrevID = rst!xlid
    sPrevNum = rst!uniquenumber
    rst.MoveNext
Loop
dEnd = Now
Debug.Print "Run Time: " & DateDiff("s", dStart, dEnd)
Debug.Print "Done"

Set db = Nothing

THIRD - I ran the query qryFinal, but with this modified SQL

SELECT CurrImport.NName, CurrImport.UniqueNumber, CurrImport.Cax_Time, CurrImport.Cax_Time, CurrImport.Cx_Mtr, CurrImport.X_Mtr, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![UniqueNumber]<>[previmport]![UniqueNumber],Null,[currimport]![X_Mtr]-[previmport]![X_Mtr])) AS XDelta, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![UniqueNumber]<>[previmport]![UniqueNumber],Null,IIf([previmport]![Cax_Time]-[currimport]![Cax_Time]=0,0,([currimport]![X_Mtr]-[previmport]![X_Mtr])/([currimport]![Cax_Time]-[previmport]![Cax_Time])))) AS XDay, IIf([xday]>=0 And [xday]<19,"OK","Discontinuous") AS Check1, IIf(IsNull([previmport]![Cax_Time]),Null,IIf([currimport]![Cax_Time]-[previmport]![Cax_Time]<1 And [check1]="Discontinuous","DL-Same Day",Null)) AS SameDay
FROM (tblTemp LEFT JOIN MyData AS CurrImport ON tblTemp.CurrentRecord = CurrImport.Xlid) LEFT JOIN MyData AS PrevImport ON tblTemp.PrevRecord = PrevImport.Xlid
ORDER BY CurrImport.UniqueNumber, CurrImport.Cax_Time;

The code runs in about 25 seconds
The query runs almost instantly

Note this relies on Ssnafu's table (MyData) having a system generated ID (autonumber) associated with it.

Just so I got this right, I am gonna have to put my attention to the last code in your last post#38 and not the #37 right? Can you please just tell me how to run these codes. If I am not wrong I just create a new blank module and put in the code inside the function and just run it and save it with any name for later use.....I also see a query , where should I put it and run it.

Also what db I am working on @rpeare or @ssanfu for these queries and macro to work?


Please let me know.

Thanks

[ssanfu]

rpeare changed from using a query to using a temp table. The temp table has (requires) two fields named CurrentRecord & PrevRecord of type Long.
The code is required to execute (run) after new records are added. Then the query "qryFinal" will display the results of the calculations.
Then the query (qryFinal updated to the query in post #38) will display the calculations.
Note: there are only 4 calculated fields in the query. YOU must create remaining 8 calculated fields in the query.

---------------------------

In the attached dB, I left only records for "UniqueNumbers" 1002 & 1006. The dB with 70,500+ records was too large to post even Zipped.

After looking at the dB, here is what you have to do:


1) In the access dB, delete ALL of the records in the table "MyData". Do a "Compact and Repair".

2) Open MY "Data.xlsx". Look at row 1. The names you have are not acceptable for access fields, so I changed them. You need to change the names in YOUR Data.xlsx (row 1) to match the names I used.

3) Import the records from "Data.xlsx". (the 70,500+ records)

4) On Form1, Click the "Recalc" button. You MUST click the "Recalc" button after records have been added.
You can click it at any time, but you MUST click it after records are added.

I added a combo box to be able to filter the recordset. If you select 8500, only those records with a Unique Number of 8500 will appear.
You know what the button is for.


REMEMBER: Every time you add (import) new records to the dB, you MUST click the "Recalc" button.


Again....Don't forget to add the other 8 calculations to the query, then to the form.

[hacerz]

rpeare changed from using a query to using a temp table. The temp table has (requires) two fields named CurrentRecord & PrevRecord of type Long.
The code is required to execute (run) after new records are added. Then the query "qryFinal" will display the results of the calculations.
Then the query (qryFinal updated to the query in post #38) will display the calculations.
Note: there are only 4 calculated fields in the query. YOU must create remaining 8 calculated fields in the query.

---------------------------

In the attached dB, I left only records for "UniqueNumbers" 1002 & 1006. The dB with 70,500+ records was too large to post even Zipped.

After looking at the dB, here is what you have to do:


1) In the access dB, delete ALL of the records in the table "MyData". Do a "Compact and Repair".

2) Open MY "Data.xlsx". Look at row 1. The names you have are not acceptable for access fields, so I changed them. You need to change the names in YOUR Data.xlsx (row 1) to match the names I used.

3) Import the records from "Data.xlsx". (the 70,500+ records)

4) On Form1, Click the "Recalc" button. You MUST click the "Recalc" button after records have been added.
You can click it at any time, but you MUST click it after records are added.

I added a combo box to be able to filter the recordset. If you select 8500, only those records with a Unique Number of 8500 will appear.
You know what the button is for.


REMEMBER: Every time you add (import) new records to the dB, you MUST click the "Recalc" button.


Again....Don't forget to add the other 8 calculations to the query, then to the form.

Thanks for the reply. I will look in to the DB file and make sure i get this right. Just wondering are the other queries for the other columns are difficult to write. If you can please just provide me with atleast the segment data columns (2 columns), I appreciate it.

I just saw the form with gui interface. That looks awesome.

Thanks

[ssanfu]

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:

=IF(B56=B55,E56-E55,"")

Converted Formula to Access query:

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.......

=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:

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.

[hacerz]

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:

=IF(B56=B55,E56-E55,"")

Converted Formula to Access query:

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.......

=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:

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.

Thanks for the detailed explanation man. Really appreciate that. I will write the others and let you know if any questions. Yes the X-Delta and Xday are already done by you guys.

Thanks a lot.