IIF IsNull Expression

msjeb007

Registered User.
Local time
Today, 11:48
Joined
Mar 9, 2018
Messages
13
I have an access query to record equipment casualty reports. There are initial dates and correct dates. I need to compute the number of days from initial date to today if the report is active, or compute the number of days from initial date to correct date if the report is closed. Below is my attempt but I am getting an invalid syntax error:

IIf([Correct Date] IsNull, (DateDiff('d', [Initial Date],Date()), (DateDiff('d'[Initial Date], [Correct Date])

What am I doing wrong?:banghead:
 
The first part of an IIF statement must resolve to True or False. Usually this is done via a comparison operator (=, >, <, etc.), but can be done with functions that resolve to one of those two values.

Your's is simply a statement--one that doesn't make sense to the computer because it doesn't resolve to True or False. I would use the IsNull function (https://www.techonthenet.com/access/functions/advanced/isnull.php) instead of what you have.
 
Hmm, parenthesis seem off, try...


Code:
IIf([Correct Date] IsNull, DateDiff('d', [Initial Date],Date()), DateDiff('d'[Initial Date], [Correct Date]))
 
Code:
(DateDiff('d'[B][COLOR="Red"]!!!![/COLOR][/B][Initial Date], [Correct Date])

seems to be a missing comma where the !!!! is located
 
As plog stated the Access function is
isNull(expression)
not
Expresression isNull
 
So you are looking for something like;

Code:
IIF(nz([Correct Date],0) = 0,DateDiff('d',[Initial Date],date()),datediff('d',[Initial Date],[Correct Date]))

nz(Field,ReturnIfNull) allows you to accept both bad (0) data as well as actual nulls.

For your initial post, I'd suggest counting your parens. Start with zero and add one for each "(" and subtract one for each ")". Your original has one too many.

I would also remove the spaces in your field names as soon as you can. Makes it much less likely you'll forget to add the brackets.
 
Code:
DateDiff("d", [Initial Date], Nz([Correct Date], Date())
 
IsNull is a VBA function and your use of it is incorrect.
Is Null is SQL Syntax.

Code:
IIf(IsNull([Correct Date]), (DateDiff('d', [Initial Date],Date()), (DateDiff('d'[Initial Date], [Correct Date])
 
In case anyone doesn't quite understand.

My code posted above does the same task, avoids IsNull, is about half the length and easier to read than any of other solutions.
 
I have a if else statement in miscrosoft access. My user keyed in txtB with the input YES but the statement return an alert box no input instead of the value YES. Below is my if else statement. Please help me find out where is the mistake. Please

If me.txtA.value <>"" and me.txtB.value ="" then
TxtLetter.value = me.txtA.value
Elseif me.txtA.value="" and me.txtB.value <>"" then
TxtLetter.value = me.txtB.value
Elseif me.txtA.value <>"" and me.txtB.value<>"" then
TxtLetter.value = me.txtA.value
Else
Msgbox"no input"
 
Alvin85
Your post has nothing to do with the rest of this thread.
Please start a new thread for this issue
 

Users who are viewing this thread

Back
Top Bottom