IIf statements

G

Guest

Is there a way to include another function inside of a IIf statement, like this
IIf([DATESENT]=Is Not Null, DateDiff("d",[DATEREC], [DATESENT],
DateDiff("d", [DATEREC], Now())) ?

(I put this statement in as a query and it said that I'm missing a comma
somewhere)

[DATESENT] and [DATEREC] are both in the date format and in the query.
 
G

Guest

Im not sure if thats a typo but you havent closed the bracket after the first
datediff. Maybe that could be the problem if not please give actual code used
 
F

fredg

Is there a way to include another function inside of a IIf statement, like this
IIf([DATESENT]=Is Not Null, DateDiff("d",[DATEREC], [DATESENT],
DateDiff("d", [DATEREC], Now())) ?

(I put this statement in as a query and it said that I'm missing a comma
somewhere)

[DATESENT] and [DATEREC] are both in the date format and in the query.
Click to expand...


1) IIf([DATESENT]=Is Not Null is not written correctly.
Nothing is "= Is Not Null".

You could write IIf([DateSent] Is Not Null, but I like IIf(Not
IsNull([DateSent]).

2) If this is in the control source of an unbound control, then you
must start the expression with an = sign.

3)You're missing a closing parenthesis after the first DateDiff
function.

4) I've also changed Now() to Date(), as you do not need the time
value in your function.

try:

= IIf(Not IsNull([DATESENT]), DateDiff("d",[DATEREC], [DATESENT]),
DateDiff("d", [DATEREC], Date()))
 
G

Guest

SELECT Master.[Last Name], Master.[First Name], Master.Rank, Master.BOS,
Master.[Date of Board], Master.[Date Received], Master.[REASON FOR >30 DAYS],
Master.PEB, Master.DR, Master.[EXP DATE], Master.[CA SIG], Master.INFO,
Master.NMA, Master.PE, Master.HR, Master.PCR, Master.AO, Master.ADDENDUM,
Master.[HOW MANY], Master.SURR, Master.SUS, Master.NOE, Master.[PT SIG],
Master.ADT, Master.[MESSAG GROUP], Master.COMMAND, Master.[PCM CLINIC],
Master.[SENT TO PEB], Master.[DATE SENT TO PEB], Master.TERMINATED,
Master.[DATE TERMINATED], Master.[REASON FOR TERMINATION], Master.ISSUES,
Master.[WEEKLY REPORT], Master.[NEW CASES], Master.[DATE RETURNED SUSPENDED],
Master.[REASON FOR SUSPENSION], Master.[UNIT ATTACHED TO], Master.SUPERVISOR,
Master.[SUPERVISOR PHONE], Master.[CCM ASSIGNED], Master.[NAME OF CCM],
Master.[PHONE NUMBER OF CCM], Master.COMMENTS, Master.[PHYSICALS NEEDED],
Master.SSN, Master.OIF, Master.LODI, Master.REBUTT, Master.[Date of
Rebuttal], Master.Notes, Master.[Transfer Date], Master.[Transfer POC],
Master.[Transfer Reason], Master.[Reviewed?], Master.[Review Date],
Master.[Review Comments], Master.[Sent for Second Review], Master.[Second
Review Comments], Master.[Date Received Back], Master.Other, Master.[Date
Board Transcribed], Master.[Date Received from Outside Facility],
Master.[Date Sent to Senior Member for Signatures], Master.[Date Returned
from Sen Memb], Master.[Date CA], Master.[Date PT], Master.[Reason for
Delay], Master.[Date Sent Dict Phys], Master.[Date Ret Dict Phys],
Master.[Ready to Be Sent to PEB], Master.[Marked for Attn], Master.[Case
Manager], IIf(Not IsNull([DATE SENT TO PEB]),DateDiff("d",[Date of
Board],[DATE SENT TO PEB],DateDiff("d",[Date of Board],Date()))) AS Days,
Master.[No Longer Tracking], Master.BMIA, Master.[BMIA Comments], Master.[MR
BREW]
FROM Master
GROUP BY Master.[Last Name], Master.[First Name], Master.Rank, Master.BOS,
Master.[Date of Board], Master.[Date Received], Master.[REASON FOR >30 DAYS],
Master.PEB, Master.DR, Master.[EXP DATE], Master.[CA SIG], Master.INFO,
Master.NMA, Master.PE, Master.HR, Master.PCR, Master.AO, Master.ADDENDUM,
Master.[HOW MANY], Master.SURR, Master.SUS, Master.NOE, Master.[PT SIG],
Master.ADT, Master.[MESSAG GROUP], Master.COMMAND, Master.[PCM CLINIC],
Master.[SENT TO PEB], Master.[DATE SENT TO PEB], Master.TERMINATED,
Master.[DATE TERMINATED], Master.[REASON FOR TERMINATION], Master.ISSUES,
Master.[WEEKLY REPORT], Master.[NEW CASES], Master.[DATE RETURNED SUSPENDED],
Master.[REASON FOR SUSPENSION], Master.[UNIT ATTACHED TO], Master.SUPERVISOR,
Master.[SUPERVISOR PHONE], Master.[CCM ASSIGNED], Master.[NAME OF CCM],
Master.[PHONE NUMBER OF CCM], Master.COMMENTS, Master.[PHYSICALS NEEDED],
Master.SSN, Master.OIF, Master.LODI, Master.REBUTT, Master.[Date of
Rebuttal], Master.Notes, Master.[Transfer Date], Master.[Transfer POC],
Master.[Transfer Reason], Master.[Reviewed?], Master.[Review Date],
Master.[Review Comments], Master.[Sent for Second Review], Master.[Second
Review Comments], Master.[Date Received Back], Master.Other, Master.[Date
Board Transcribed], Master.[Date Received from Outside Facility],
Master.[Date Sent to Senior Member for Signatures], Master.[Date Returned
from Sen Memb], Master.[Date CA], Master.[Date PT], Master.[Reason for
Delay], Master.[Date Sent Dict Phys], Master.[Date Ret Dict Phys],
Master.[Ready to Be Sent to PEB], Master.[Marked for Attn], Master.[Case
Manager], Master.[No Longer Tracking], Master.BMIA, Master.[BMIA Comments],
Master.[MR BREW]
HAVING (((Master.[No Longer Tracking])=No));

For "Days", it only returns those where the [DATE SENT TO PEB] is Not Null.
When I tried to switch it to
IIf(IsNull([DATE SENT TO PEB], DateDiff("d", [Date of Board], Date()),
DateDiff("d", [Date of Board], [DATE SENT TO PEB])))
it returned "ERROR#" messages on all of the rows.

Sorry for displaying the entire SQL statement. I didn't know how much would
be useful. And I used different table columns in the original post to make
it clearer.

fredg said:
Is there a way to include another function inside of a IIf statement, like this
IIf([DATESENT]=Is Not Null, DateDiff("d",[DATEREC], [DATESENT],
DateDiff("d", [DATEREC], Now())) ?

(I put this statement in as a query and it said that I'm missing a comma
somewhere)

[DATESENT] and [DATEREC] are both in the date format and in the query.
Click to expand...


1) IIf([DATESENT]=Is Not Null is not written correctly.
Nothing is "= Is Not Null".

You could write IIf([DateSent] Is Not Null, but I like IIf(Not
IsNull([DateSent]).

2) If this is in the control source of an unbound control, then you
must start the expression with an = sign.

3)You're missing a closing parenthesis after the first DateDiff
function.

4) I've also changed Now() to Date(), as you do not need the time
value in your function.

try:

= IIf(Not IsNull([DATESENT]), DateDiff("d",[DATEREC], [DATESENT]),
DateDiff("d", [DATEREC], Date()))
Click to expand...
 
D

Douglas J. Steele

Your parentheses are incorrect.

You've currently got:

IIf(Not IsNull([DATE SENT TO PEB]),DateDiff("d",[Date of
Board],[DATE SENT TO PEB],DateDiff("d",[Date of Board],Date())))

it should be

IIf(Not IsNull([DATE SENT TO PEB]),DateDiff("d",[Date of
Board],[DATE SENT TO PEB]),DateDiff("d",[Date of Board],Date()))

(you need a closing parenthesis before the second , DateDiff and one fewer
at the end)

Your second attempt was

IIf(IsNull([DATE SENT TO PEB], DateDiff("d", [Date of Board], Date()),
DateDiff("d", [Date of Board], [DATE SENT TO PEB])))

It should be

IIf(IsNull([DATE SENT TO PEB]), DateDiff("d", [Date of Board], Date()),
DateDiff("d", [Date of Board], [DATE SENT TO PEB]))

(you need a closing parenthesis for the IsNull function call and one fewer
at the end)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


DandimLee said:
SELECT Master.[Last Name], Master.[First Name], Master.Rank, Master.BOS,
Master.[Date of Board], Master.[Date Received], Master.[REASON FOR >30
DAYS],
Master.PEB, Master.DR, Master.[EXP DATE], Master.[CA SIG], Master.INFO,
Master.NMA, Master.PE, Master.HR, Master.PCR, Master.AO, Master.ADDENDUM,
Master.[HOW MANY], Master.SURR, Master.SUS, Master.NOE, Master.[PT SIG],
Master.ADT, Master.[MESSAG GROUP], Master.COMMAND, Master.[PCM CLINIC],
Master.[SENT TO PEB], Master.[DATE SENT TO PEB], Master.TERMINATED,
Master.[DATE TERMINATED], Master.[REASON FOR TERMINATION], Master.ISSUES,
Master.[WEEKLY REPORT], Master.[NEW CASES], Master.[DATE RETURNED
SUSPENDED],
Master.[REASON FOR SUSPENSION], Master.[UNIT ATTACHED TO],
Master.SUPERVISOR,
Master.[SUPERVISOR PHONE], Master.[CCM ASSIGNED], Master.[NAME OF CCM],
Master.[PHONE NUMBER OF CCM], Master.COMMENTS, Master.[PHYSICALS NEEDED],
Master.SSN, Master.OIF, Master.LODI, Master.REBUTT, Master.[Date of
Rebuttal], Master.Notes, Master.[Transfer Date], Master.[Transfer POC],
Master.[Transfer Reason], Master.[Reviewed?], Master.[Review Date],
Master.[Review Comments], Master.[Sent for Second Review], Master.[Second
Review Comments], Master.[Date Received Back], Master.Other, Master.[Date
Board Transcribed], Master.[Date Received from Outside Facility],
Master.[Date Sent to Senior Member for Signatures], Master.[Date Returned
from Sen Memb], Master.[Date CA], Master.[Date PT], Master.[Reason for
Delay], Master.[Date Sent Dict Phys], Master.[Date Ret Dict Phys],
Master.[Ready to Be Sent to PEB], Master.[Marked for Attn], Master.[Case
Manager], IIf(Not IsNull([DATE SENT TO PEB]),DateDiff("d",[Date of
Board],[DATE SENT TO PEB],DateDiff("d",[Date of Board],Date()))) AS Days,
Master.[No Longer Tracking], Master.BMIA, Master.[BMIA Comments],
Master.[MR
BREW]
FROM Master
GROUP BY Master.[Last Name], Master.[First Name], Master.Rank, Master.BOS,
Master.[Date of Board], Master.[Date Received], Master.[REASON FOR >30
DAYS],
Master.PEB, Master.DR, Master.[EXP DATE], Master.[CA SIG], Master.INFO,
Master.NMA, Master.PE, Master.HR, Master.PCR, Master.AO, Master.ADDENDUM,
Master.[HOW MANY], Master.SURR, Master.SUS, Master.NOE, Master.[PT SIG],
Master.ADT, Master.[MESSAG GROUP], Master.COMMAND, Master.[PCM CLINIC],
Master.[SENT TO PEB], Master.[DATE SENT TO PEB], Master.TERMINATED,
Master.[DATE TERMINATED], Master.[REASON FOR TERMINATION], Master.ISSUES,
Master.[WEEKLY REPORT], Master.[NEW CASES], Master.[DATE RETURNED
SUSPENDED],
Master.[REASON FOR SUSPENSION], Master.[UNIT ATTACHED TO],
Master.SUPERVISOR,
Master.[SUPERVISOR PHONE], Master.[CCM ASSIGNED], Master.[NAME OF CCM],
Master.[PHONE NUMBER OF CCM], Master.COMMENTS, Master.[PHYSICALS NEEDED],
Master.SSN, Master.OIF, Master.LODI, Master.REBUTT, Master.[Date of
Rebuttal], Master.Notes, Master.[Transfer Date], Master.[Transfer POC],
Master.[Transfer Reason], Master.[Reviewed?], Master.[Review Date],
Master.[Review Comments], Master.[Sent for Second Review], Master.[Second
Review Comments], Master.[Date Received Back], Master.Other, Master.[Date
Board Transcribed], Master.[Date Received from Outside Facility],
Master.[Date Sent to Senior Member for Signatures], Master.[Date Returned
from Sen Memb], Master.[Date CA], Master.[Date PT], Master.[Reason for
Delay], Master.[Date Sent Dict Phys], Master.[Date Ret Dict Phys],
Master.[Ready to Be Sent to PEB], Master.[Marked for Attn], Master.[Case
Manager], Master.[No Longer Tracking], Master.BMIA, Master.[BMIA
Comments],
Master.[MR BREW]
HAVING (((Master.[No Longer Tracking])=No));

For "Days", it only returns those where the [DATE SENT TO PEB] is Not
Null.
When I tried to switch it to
IIf(IsNull([DATE SENT TO PEB], DateDiff("d", [Date of Board], Date()),
DateDiff("d", [Date of Board], [DATE SENT TO PEB])))
it returned "ERROR#" messages on all of the rows.

Sorry for displaying the entire SQL statement. I didn't know how much
would
be useful. And I used different table columns in the original post to
make
it clearer.

fredg said:
Is there a way to include another function inside of a IIf statement,
like this
IIf([DATESENT]=Is Not Null, DateDiff("d",[DATEREC], [DATESENT],
DateDiff("d", [DATEREC], Now())) ?

(I put this statement in as a query and it said that I'm missing a
comma
somewhere)

[DATESENT] and [DATEREC] are both in the date format and in the query.
Click to expand...


1) IIf([DATESENT]=Is Not Null is not written correctly.
Nothing is "= Is Not Null".

You could write IIf([DateSent] Is Not Null, but I like IIf(Not
IsNull([DateSent]).

2) If this is in the control source of an unbound control, then you
must start the expression with an = sign.

3)You're missing a closing parenthesis after the first DateDiff
function.

4) I've also changed Now() to Date(), as you do not need the time
value in your function.

try:

= IIf(Not IsNull([DATESENT]), DateDiff("d",[DATEREC], [DATESENT]),
DateDiff("d", [DATEREC], Date()))
Click to expand...
Click to expand...
 
G

Guest

Thanks. It's working now. Sorry for wasting your time on transcription
errors.

Douglas J. Steele said:
Your parentheses are incorrect.

You've currently got:

IIf(Not IsNull([DATE SENT TO PEB]),DateDiff("d",[Date of
Board],[DATE SENT TO PEB],DateDiff("d",[Date of Board],Date())))

it should be

IIf(Not IsNull([DATE SENT TO PEB]),DateDiff("d",[Date of
Board],[DATE SENT TO PEB]),DateDiff("d",[Date of Board],Date()))

(you need a closing parenthesis before the second , DateDiff and one fewer
at the end)

Your second attempt was

IIf(IsNull([DATE SENT TO PEB], DateDiff("d", [Date of Board], Date()),
DateDiff("d", [Date of Board], [DATE SENT TO PEB])))

It should be

IIf(IsNull([DATE SENT TO PEB]), DateDiff("d", [Date of Board], Date()),
DateDiff("d", [Date of Board], [DATE SENT TO PEB]))

(you need a closing parenthesis for the IsNull function call and one fewer
at the end)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


DandimLee said:
SELECT Master.[Last Name], Master.[First Name], Master.Rank, Master.BOS,
Master.[Date of Board], Master.[Date Received], Master.[REASON FOR >30
DAYS],
Master.PEB, Master.DR, Master.[EXP DATE], Master.[CA SIG], Master.INFO,
Master.NMA, Master.PE, Master.HR, Master.PCR, Master.AO, Master.ADDENDUM,
Master.[HOW MANY], Master.SURR, Master.SUS, Master.NOE, Master.[PT SIG],
Master.ADT, Master.[MESSAG GROUP], Master.COMMAND, Master.[PCM CLINIC],
Master.[SENT TO PEB], Master.[DATE SENT TO PEB], Master.TERMINATED,
Master.[DATE TERMINATED], Master.[REASON FOR TERMINATION], Master.ISSUES,
Master.[WEEKLY REPORT], Master.[NEW CASES], Master.[DATE RETURNED
SUSPENDED],
Master.[REASON FOR SUSPENSION], Master.[UNIT ATTACHED TO],
Master.SUPERVISOR,
Master.[SUPERVISOR PHONE], Master.[CCM ASSIGNED], Master.[NAME OF CCM],
Master.[PHONE NUMBER OF CCM], Master.COMMENTS, Master.[PHYSICALS NEEDED],
Master.SSN, Master.OIF, Master.LODI, Master.REBUTT, Master.[Date of
Rebuttal], Master.Notes, Master.[Transfer Date], Master.[Transfer POC],
Master.[Transfer Reason], Master.[Reviewed?], Master.[Review Date],
Master.[Review Comments], Master.[Sent for Second Review], Master.[Second
Review Comments], Master.[Date Received Back], Master.Other, Master.[Date
Board Transcribed], Master.[Date Received from Outside Facility],
Master.[Date Sent to Senior Member for Signatures], Master.[Date Returned
from Sen Memb], Master.[Date CA], Master.[Date PT], Master.[Reason for
Delay], Master.[Date Sent Dict Phys], Master.[Date Ret Dict Phys],
Master.[Ready to Be Sent to PEB], Master.[Marked for Attn], Master.[Case
Manager], IIf(Not IsNull([DATE SENT TO PEB]),DateDiff("d",[Date of
Board],[DATE SENT TO PEB],DateDiff("d",[Date of Board],Date()))) AS Days,
Master.[No Longer Tracking], Master.BMIA, Master.[BMIA Comments],
Master.[MR
BREW]
FROM Master
GROUP BY Master.[Last Name], Master.[First Name], Master.Rank, Master.BOS,
Master.[Date of Board], Master.[Date Received], Master.[REASON FOR >30
DAYS],
Master.PEB, Master.DR, Master.[EXP DATE], Master.[CA SIG], Master.INFO,
Master.NMA, Master.PE, Master.HR, Master.PCR, Master.AO, Master.ADDENDUM,
Master.[HOW MANY], Master.SURR, Master.SUS, Master.NOE, Master.[PT SIG],
Master.ADT, Master.[MESSAG GROUP], Master.COMMAND, Master.[PCM CLINIC],
Master.[SENT TO PEB], Master.[DATE SENT TO PEB], Master.TERMINATED,
Master.[DATE TERMINATED], Master.[REASON FOR TERMINATION], Master.ISSUES,
Master.[WEEKLY REPORT], Master.[NEW CASES], Master.[DATE RETURNED
SUSPENDED],
Master.[REASON FOR SUSPENSION], Master.[UNIT ATTACHED TO],
Master.SUPERVISOR,
Master.[SUPERVISOR PHONE], Master.[CCM ASSIGNED], Master.[NAME OF CCM],
Master.[PHONE NUMBER OF CCM], Master.COMMENTS, Master.[PHYSICALS NEEDED],
Master.SSN, Master.OIF, Master.LODI, Master.REBUTT, Master.[Date of
Rebuttal], Master.Notes, Master.[Transfer Date], Master.[Transfer POC],
Master.[Transfer Reason], Master.[Reviewed?], Master.[Review Date],
Master.[Review Comments], Master.[Sent for Second Review], Master.[Second
Review Comments], Master.[Date Received Back], Master.Other, Master.[Date
Board Transcribed], Master.[Date Received from Outside Facility],
Master.[Date Sent to Senior Member for Signatures], Master.[Date Returned
from Sen Memb], Master.[Date CA], Master.[Date PT], Master.[Reason for
Delay], Master.[Date Sent Dict Phys], Master.[Date Ret Dict Phys],
Master.[Ready to Be Sent to PEB], Master.[Marked for Attn], Master.[Case
Manager], Master.[No Longer Tracking], Master.BMIA, Master.[BMIA
Comments],
Master.[MR BREW]
HAVING (((Master.[No Longer Tracking])=No));

For "Days", it only returns those where the [DATE SENT TO PEB] is Not
Null.
When I tried to switch it to
IIf(IsNull([DATE SENT TO PEB], DateDiff("d", [Date of Board], Date()),
DateDiff("d", [Date of Board], [DATE SENT TO PEB])))
it returned "ERROR#" messages on all of the rows.

Sorry for displaying the entire SQL statement. I didn't know how much
would
be useful. And I used different table columns in the original post to
make
it clearer.

fredg said:
On Thu, 2 Aug 2007 06:00:03 -0700, DandimLee wrote:

Is there a way to include another function inside of a IIf statement,
like this
IIf([DATESENT]=Is Not Null, DateDiff("d",[DATEREC], [DATESENT],
DateDiff("d", [DATEREC], Now())) ?

(I put this statement in as a query and it said that I'm missing a
comma
somewhere)

[DATESENT] and [DATEREC] are both in the date format and in the query.


1) IIf([DATESENT]=Is Not Null is not written correctly.
Nothing is "= Is Not Null".

You could write IIf([DateSent] Is Not Null, but I like IIf(Not
IsNull([DateSent]).

2) If this is in the control source of an unbound control, then you
must start the expression with an = sign.

3)You're missing a closing parenthesis after the first DateDiff
function.

4) I've also changed Now() to Date(), as you do not need the time
value in your function.

try:

= IIf(Not IsNull([DATESENT]), DateDiff("d",[DATEREC], [DATESENT]),
DateDiff("d", [DATEREC], Date()))
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If by "transcription errors", you mean you retyped the expressions in your
original post, please think of using Copy and Paste in the future.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find all records not updated this month. 2
Treeview problem ,So strange ! 11
Refference Unbound Text Box 1
Minus today 2
Conditions with a nested IIF 2
change values in a form based on other values 4
Conditional criteria in a query 4
Calculating Date and Time using IIf statements - Please Help Me 1

Top