Subsequent subtraction, if the field is null subtract from next one in sequence

[Lidya]

Hi all,


I am new to the forum and also Access novice. I will appreciate very much if someone can help me solve my access problem, I have been trying to find the solution for few days and I finally decided to ask.
I need to subtract one column from another but there are many nulls which I need them as flags. Here is an example:


ID....value1..... value2...... value3
1......608....... null...........500
2......550....... 537.......... 528
3......617....... null........... null
4......698....... 620........... null


The values decrease from filed "value 1" to field "value 3". So I need to subtract Value 2 ( if is not null) from Value 1 and Value 3 (If is not null) from value 2 BUT if value 2 is null than subtract it from Value 1.
In summary I just need the differences between the fields that are not null. If the field is null subtract from next one and so on until you get a not null field.
The differences do not have to be in one column but in 3 in this example. It sounds complicated, I tried using IIf statement but it gets to complicated and there was not enough space to write the whole conditional statement as I have 11 columns to subtract from each other. Can somebody give me any idea of how to solve this and if I need to use SQL how to write it?


Thanks, I appreciate your help

 

[empire03]

Hi Lidya, do you have an example of what you want for your output???
If possible can you post your file here so we can take a look?

 

[plog]

Iff statements are the way to go, but not crammed into SQL. You need to create a VBA function in a Module.

What is your table name and field names? Is it just 3 values? What happens if 2 or more are null? What if none are null?

 

[Lidya]

Thanks for replaying all,
I can't post my file it is a huge one, my table contains many fields but I need to do the calculation on 12 of them.
In my example, I gave the fields (columns) names: Value1, 2, and 3 there are 12 of them and they actually represent surface elevation. I need to find the thickness between two adjasent surfaces, if some surface is missing (null) the next adjacent should be used. I was thinking about the IIF statement in this way:
If "value12" is not null than subtract it from "Value11”, If "Value11"is null than subtract it from "Value 10", If "Value10" is null than subtract it from "Value9"........otherwise if all are nulls keep NULL.

I need to do this statement for each of the 12 columns and will get again
12 columns now named "difference" in which instead the surface elevation now will be the difference or thickness between the surfaces.

This is my idea of how to do this task, I just couldn't write that statement properly and it was too long, so I thought there is maybe another way.