Error in Access (#Type!)

[Piyushjais]

I have inserted this argument in a calculated field



"IIf([From Date]-[Submission Date]>=0,[Total Days]-2,IIf([From Date]-[Submission Date]>=1,[Total Days]-1,IIf([From Date]-[Submission Date]>=2,[Total Days],0))) & [Mess Card]=Yes"

I am getting an error "#Type!". I am not an expert on access so require some help on the above. Is the argument wrong? what could be a correct argument. Basically i am checking the no of days leave has been applied. The calculation is based on a 48 hrs notice after which the leave is counted and also the mess card has to be deposited. If not deposited then the leaves are not accounted.

Please help

[June7]

Are your date fields all date/time type and not text?

You should reverse the conditions. Test >=2 first then >=1 then >=0

Why are you concatenating the result of [Mess Card]=Yes? This calc will return either -1 or 0.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[Piyushjais]

The entire calculation is based on the condition whether the card has been submitted or not. Is there any other way of checking?

[aytee111]

Create a form based on the table using the wizard (or query if more than one table involved), add all the fields mentioned in the expression you have above. Then take each calculation and make it its own field (e.g. one field will have =[From Date] - [Submission Date]).

Once you have each part of the calc working, you can begin to put them together, adding one piece at a time.

[isladogs]

I've done the following:
a) created an IIf section for [Mess Card]=True and which is then broken down into your 3 IIf statements
b) reversed the 3 statements so you have >=2 ; 1; 0 as your conditions
c) added Null as the output if [Mess Card]=False

I've assumed 'Mess Card' is a Yes/No field
If its text, replace True with 'Yes'

IIf([Mess Card]=True,IIf([From Date]-[Submission Date]>=2,[Total Days],0,IIf([From Date]-[Submission Date]=1,[Total Days]-1,IIf([From Date]-[Submission Date]=0,[Total Days]-2,0))),Null)

Hopefully I've got the bracketing correct but no promises ...
TEST it carefully & CHECK it gives correct answers
However, personally I wouldn't do an expression with 4 IIf as its too easy to make mistakes

A much easier approach would be a UDF with select cases for >=2;1;0 scenarios ... or a lookup table