calculating in query depending of date

[crujazz]

Goodday all,

I used to create sheets with excel but now i want to transfer everything to access. This is my problem:

a table with:
column Date (D)
column Pressure Left (PL)
column Pressure Right (PR)

for example:

D1 06-jul-09
PL1 250
PR1 0

D2 07-jul-09
PL2 221
PR2 0

In excel i can use several functions =IF(D2>D1;PL1-PL2;"0").....

Basicly i want to use kinda formula in access.....how to do that??

Thnx in advanced

 

[wazz]

you could look up the values (various ways to do that) and store them in variables, or an array and then use your formula, substituting the values of the variables.

in your ex, you would look up the values for the dates (a=value-for-date-1; b=value-for-date-2). you would already know which date is greater so you would just have to subtract the values. hth.

 

[crujazz]

Oke, i get your point but do u use a dloopup function for each date field or is it easier to dim an array and if so how to cause in my ex i picked just one record of 1200.....

Forgive me but i used to use access but it has been several years ago...

 

[wazz]

guess it depends how you picked the record.
- if the values are on a form you could just 'read the form'.
- you could save a query that reads from your form or reads from a recordset and do a DLookup on the query.
- if you get the record via a recordset in vba you can read the values from the rs.

how are you selecting the record/where are the values?

 

[crujazz]

Well let me start with the fact that i was trying to use recordselect but i ran into the problem that the date field is basicly leading in all

todays date has a different value than yesterdays date and the value is neagtive if i follow the rules of counting

Take a look at my attachment (its made up in excel)