Syntax Error (Missing Operator) In Query (1 Viewer)

[raweber]

Here's the offending code:

IIf(IsNull([SuspendDate]),(Date()-[DateReceived]),IIf(Isnull([ResumeDate]),
([SuspendDate]-[DateRecieved]),(([Date()-[DateReceived])-([ResumeDate]-[SuspendDate])))
AS Queue

As near as I can tell I have Then and Else conditions for each IIf statement, all my parentheses are closed off. I tried it as

Queue:IIf(IsNull(...

but got the same error.

Essentially this is a single nested IIf, checking for the presence of [SuspendDate] and [ResumeDate] and calculating the days elapsed appropriately.

That is, if there is no [SuspendDate], then subtract the [ReceivedDate] from the current date. If there is a [SuspendDate] but no [Resumedate] then subtract the [SuspendDate] from the Current Date. Lastly, if there is both a [SuspendDate] and a [ResumeDate] then subtract the time in suspension from the total ellapsed time.

Any ideas?

Thanks, Rob

 

[vbaInet]

Re: Syntax Error (Missing Opertor) In Query

The main culprit is highlighted in red:

IIf(IsNull([SuspendDate]),(Date()-[DateReceived]),IIf(Isnull([ResumeDate]),
([SuspendDate]-[DateRecieved]),(([COLOR=red][[/COLOR]Date()-[DateReceived])-([ResumeDate]-[SuspendDate])))
AS Queue

But you also had some unecessary parentheses, so here's the amended code:

IIf(IsNull([SuspendDate]), Date()-[DateReceived], IIf(Isnull([ResumeDate]), 
[SuspendDate]-[DateRecieved], Date()-[DateReceived]-[ResumeDate]-[SuspendDate])) 
AS Queue

 

[raweber]

Re: Syntax Error (Missing Opertor) In Query

Oh, that's brilliant! Thanks so much.

One other thing - DOH!

Thanks again,

Rob

 

[vbaInet]

Re: Syntax Error (Missing Opertor) In Query

You're welcome!

 

[MSAccessRookie]

Re: Syntax Error (Missing Opertor) In Query

Subtraction is a non-commutative operation, and I think that removing the two parentheses that are marked in RED changes the outcome (if not the intent) of the operation. See if the following code works right:

[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]IIf([/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    IsNull([SuspendDate]),[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    (Date()-[DateReceived]),[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    IIf(Isnull([ResumeDate]),[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        ([SuspendDate]-[DateRecieved]),[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        ([Date()-[DateReceived][COLOR=red][B])[/B][/COLOR]-[COLOR=red][B]([/B][/COLOR][ResumeDate]-[SuspendDate])[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    )[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]) AS Queue[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

 

[boblarson]

Re: Syntax Error (Missing Opertor) In Query

One thing that I didn't see anyone else catch too is a misspelled word in red:

'
 
IIf(IsNull([SuspendDate]), Date()-[DateRe[B]cei[/B]ved], IIf(Isnull([ResumeDate]), 
[SuspendDate]-[DateRe[B][COLOR=red]cie[/COLOR][/B]ved], Date()-[DateRe[COLOR=blue][B]cei[/B][/COLOR]ved]-[ResumeDate]-[SuspendDate])) 
AS Queue
 
'

 

[MSAccessRookie]

Re: Syntax Error (Missing Opertor) In Query

Good Catch Bob, I guess I looked right through that one.