Help with Dlookup to grab value in previous record

[gcot]

I created an expression based on the response in this post

http://www.mrexcel.com/forum/showthread.php?t=243389&highlight=find+previous+records

Unfortunately the expression I created in the query gives me #Error

This expression is part of a query that has the login and logout times for call center agents.

What I want to do is: If the Agent logs out (eventType=7), grab the login time (previous record based on date/time stamp which is eventDateTime is query) for this agent.

Every logout will have a login.

Query = AgentLoggedInTimeQuery
Agents = resourceName
Logout = eventType 7

LoginTime: IIf([eventType]=7,(DLookUp("eventDateTime","AgentLoggedInTimeQuery","resourceName='" & [resourceName] & "' AND eventDateTime=#" & DMax("eventDateTime","AgentLoggedInTimeQuery","resourceName='" & [resourceName] & "'") & "#")),Null)

Any ideas on what I'm doing wrong?

Any help is appreciated.

Thanks,

Gilles

 

[SydneyGeek]

See if a subquery helps you --

LoginTime: Iif([event type]=7,(SELECT Max([eventDateTime] FROM AgentLoggedInQuery AS A WHERE A.[resourcename]=AgentLoggedInQuery.[resourcename] AND A.[eventDateTime]<AgentLoggedInQuery.[eventDateTime]),Null)

Denis

 

[gcot]

I'll give it a shot. Thanks for your help

 

[gcot]

Hi Denis,

I tried the subquery you suggested in the expression builder (not sure if that's right) but I'm getting a message that it needs a closing parenthesis or bracket. I tried several variations adding them where it seemed logical but still doesn't work.

Two questions:

1. Does the subquery go in the expression builder?
2. If yes, then what am I missing?

I'm sure it's something simple but I'm a novice at Access so it's not apparent to me.

Thanks for your help,

Gilles

See if a subquery helps you --

LoginTime: Iif([event type]=7,(SELECT Max([eventDateTime] FROM AgentLoggedInQuery AS A WHERE A.[resourcename]=AgentLoggedInQuery.[resourcename] AND A.[eventDateTime]<AgentLoggedInQuery.[eventDateTime]),Null)

Denis

 

[SydneyGeek]

Hmmm... make that 3 parentheses. Sorry, I typed straight into the response box without checking first

Try this:

LoginTime: Iif([event type]=7,(SELECT Max([eventDateTime]) FROM AgentLoggedInQuery AS A WHERE A.[resourcename]=AgentLoggedInQuery.[resourcename] AND A.[eventDateTime]))

Denis