Formulas about Access

[haufca]

Hello,

How can I write the formulas which work in Excel below?

Cell# Cell Name
O1 SP Date
R1 End Mission Date
A1 Today
S1 Date at Silopi
Q1 Mission Days
P1 Fixed Days
T1 Add. Demurrage Days
U1 Demurrage Days

1- Excel : If(O1=0;0;If(R1=0;A1-O1;R1-O1))
Access : ?

2- Excel : If(S1-R1>1;1;0)
Access : ?

3- Excel : If(R1=0;"Still in Mission";Q1-P1+T1)
Access :?

4- Excel : If(R1=0;1050;U1*85)
Access : ?

5- Excel : If(R1=0;"Still in Mission";"Done")
Access : ?

If you help me about that, I will be thankful.

 

[namliam]

Iif is the equivalent of If in Excel offcourse you must use column names instead of cell ranges... The syntax of IIf is quite the same as If...

Good luck !

 

[haufca]

Okey but what kind of parantheses do I have to use ?
This one : ()
or
this one : []
thanks,

 

[neileg]

Have you looked up Iif() in the help files?

You use round braces for the function. Square braces are used around field names. The separator in the function is a comma, not a semicolon (at least in English installs of Access).

 

[haufca]

both of you,
thank you very much. I will perform these tonight.
Lets see what will happen?

 

[The_Doc_Man]

First, you are doomed to confusion very quickly.

THERE IS NO CELL NAME. (The Matrix "There is no spoon")

The cells that you see in datasheet view are dynamically created slots that have absolutely no relation to anything once that view is closed and one more update of the table occurs.

In Excel, cells have an address because their position is meaningful whether or not anything is in the cell. In Access, the RECORD (and its FIELDS) must have some content to allow discrimination between record A and record B.

To find a given Cell in Excel, you need a row number and column number. In Access, you need a key value and a field name. But the key value isn't related to a particular row in a partcular datasheet view. You can sort the rows OF THE VIEW by clicking the fields that "drive" the order of presentation. But when it comes to Excel-like references, no can do.

My warning is that you are applying a structural assumption to Access that is not valid. Excel is not Access is not Excel.

In a query, you can reference the field names like this:

[A] + / [C]

assuming you had fields named A, B, and C.

 

[Steve5]

The IIF statements in Access are too diffacult to wrap your mind around. It would be much easier to show you if you posted the EXCEL sheet with a few of your records and results you want. Then I could build you a quicky DB with the formulas you need.