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()))