Iif (eval,null,false) 1

[merlynsdad]

I'm trying to get an IIf expression to give me a null if true, and a result if false. Basically, if a certain event type has a duration of 0:00, I want a blank result, otherwise I want the "time stamp" as my result. I'll use VBA to delete these records, unless there's a way to do that in this expression as well.

The code is as follows:

Idle: Iif(([tblTemp_Event!event_type]=4) AND ([tblTemp_Event!duration]=0:00),"",([time_stamp]))

I'm getting a syntax error. I've also tried the word Null instead of the double quotes with the same result. Any help will be appreciated. Thanks.

If the square peg won't fit in the round hole, sand off the corners.

 

[randy700]

Have you tried replacing the double quotes with 0?


Randy

 

[MajP]

[tblTemp_Event!duration]=0:00
that is not going to work
text field: "0:00"
date/time field: #0:00:00# or simply 0
numeric: 0

 

[PHV]

What about this ?
Idle: Iif(tblTemp_Event.event_type=4 AND tblTemp_Event.duration=#0:00#,Null,time_stamp)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[merlynsdad]

MajP, your suggestion of

Idle: IIf([tblTemp_Event!event_type]=4 And [tblTemp_Event!duration]=#0:00:00#,0,[time_stamp]) compiles to (when I click OK in the Zoom window)

Idle: IIf([tblTemp_Event!event_type]=4 And [tblTemp_Event!duration]=#12:00:00 AM#,0,[time_stamp]) and gives a syntax error.

/////////////

PHV, your suggestion compiles to:

Idle: IIf([tblTemp_Event!event_type]=4 And [tblTemp_Event!duration]=#12:00:00 AM#,Null,[time_stamp]) and also gives a syntax error.

Either way I'm still getting those two dreaded words "syntax error." I don't have much hair left; help please.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

I don't like the []s in the expression. Try:

Idle: IIf([tblTemp_Event[b][red]][/red][/b]![b][red][[/red][/b]event_type]=4 And [tblTemp_Event[b][red]][/red][/b]![b][red][[/red][/b]duration]=#12:00:00 AM#,Null,[time_stamp])

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

Thanks, but it's still telling me the expression contains invalid syntax. Wouldn't it be nice if someone would write a program to evaluate syntax as you enter it and tell you WHAT is wrong, instead of leaving you to guess? But then my agency probably wouldn't buy it anyway!

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

Is event_type a numeric field and duration a date/time?

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

Yes, event_type is a numeric and duration is a date/time.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

Does every record have values in each of these fields?
Can you share the full SQL view?

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

Yes, every record has an event_type and every record has a duration, although some of the durations are zero. You may not want the full SQL after seeing it, but here it is. I'm using just two employees and one date for testing purposes. The SEID is actually the employee number. Once I made the query I copied the SQL into VBA.

strSQL = "SELECT DISTINCT tblTemp_Event.SEID, tblTemp_Event.date_stamp, tblTemp_Event.time_stamp, " _
& "tblTemp_Event.event_type, IIf(([tblTemp_Event!event_type]=2),[time_stamp]) AS signon, tblTemp_Event.duration, " _
& "IIf(([tblTemp_Event!event_type]=4) And ([tblTemp_Event!duration]=#12/30/1899#),0,[time_stamp]) AS idle, " _
& "IIf(([tblTemp_Event!event_type]=3),[time_stamp]) AS signoff " _
& "FROM tblTemp_Event INNER JOIN TEMP_AGENT ON tblTemp_Event.SEID = TEMP_AGENT.unum " _
& "GROUP BY tblTemp_Event.SEID, tblTemp_Event.date_stamp, tblTemp_Event.time_stamp, tblTemp_Event.event_type, " _
& "IIf(([tblTemp_Event!event_type]=2),[time_stamp]), tblTemp_Event.duration, IIf(([tblTemp_Event!event_type]=4) " _
& "And ([tblTemp_Event!duration]=#12/30/1899#),0,[time_stamp]), IIf(([tblTemp_Event!event_type]=3),[time_stamp]) " _
& "HAVING (((tblTemp_Event.SEID) ALike 'WCMB') AND ((tblTemp_Event.date_stamp)=#9/2/2011#)) " _
& "OR (((tblTemp_Event.SEID) ALike 'ZNKB') AND ((tblTemp_Event.date_stamp)=#9/2/2011#)) " _
& "ORDER BY tblTemp_Event.SEID DESC , tblTemp_Event.time_stamp, tblTemp_Event.event_type;"
' Debug.Print strSQL
db.Execute strSQL

If the square peg won't fit in the round hole, sand off the corners.

 

[merlynsdad]

BTW,
"IIf(([tblTemp_Event!event_type]=4) And ([tblTemp_Event!duration]=#12/30/1899#),0,[time_stamp]) AS idle

still isn't doing what I want, e.g. deleting records where the event_type=4 and duration is zero, but I'm using this SQL string so I can keep working until I get the string fixed.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

The "Execute" command is for action (update, deleted, append, maketable) queries. There is no deletion involved.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

You have other issues with your SQL.
1) why use alike when you should use = since there are no wildcards
2) I would move the filter from the HAVING to a WHERE clause

WHERE tblTemp_Event.SEID IN ( 'WCMB','ZNKB') AND tblTemp_Event.date_stamp=#9/2/2011#

3) Most of your IIf()s are missing the third arguments.


Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

Let's approach this from a different perspective. What I want to do is to delete the record when the event_type = 4 AND the duration is 0:00. Can I do this as a one step process, or do I need multiple steps?

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

Build a delete query in the query designer and then just run it from code in your form. There is no need for multiple steps.

Duane
Hook'D on Access
MS Access MVP