IIF IsNull Expression (1 Viewer)

msjeb007

Registered User.
Local time
Yesterday, 20:05
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:
 

plog

Banishment Pending
Local time
Yesterday, 19:05
Joined
May 11, 2011
Messages
11,613
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.
 

GinaWhipp

AWF VIP
Local time
Yesterday, 20:05
Joined
Jun 21, 2011
Messages
5,901
Hmm, parenthesis seem off, try...


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

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:05
Joined
Jan 23, 2006
Messages
15,364
Code:
(DateDiff('d'[B][COLOR="Red"]!!!![/COLOR][/B][Initial Date], [Correct Date])

seems to be a missing comma where the !!!! is located
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:05
Joined
May 21, 2018
Messages
8,463
As plog stated the Access function is
isNull(expression)
not
Expresression isNull
 

Mark_

Longboard on the internet
Local time
Yesterday, 17:05
Joined
Sep 12, 2017
Messages
2,111
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:05
Joined
Jan 20, 2009
Messages
12,849
Code:
DateDiff("d", [Initial Date], Nz([Correct Date], Date())
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:05
Joined
Feb 19, 2002
Messages
42,981
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])
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:05
Joined
Jan 20, 2009
Messages
12,849
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.
 

Alvin85

Registered User.
Local time
Today, 08:05
Joined
Jul 7, 2018
Messages
17
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"
 

isladogs

MVP / VIP
Local time
Today, 00:05
Joined
Jan 14, 2017
Messages
18,186
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

Top Bottom