Find Match Between 2 Times

[aftabn10]

Hi, i am looking for some help in regards to writing a query based on date and time values.

I currently have 2 tables with different data and the only thing that matches between the two tables is the agents_id.

Within Table 1 I have a Job Start and Job Finish Date and TimeStamp and within Table 2 I have a logdate and a separate logtime field. What I am trying to do is find the matches for all agents for logtimes that fall between the Job Start and Job Finish Fields and at present I have written the following:

=IIf([LogTime] Between [JOB_STARTED] And [JOB_FINISH])

but when I try to run this I get an expression error (the expression you entered has a function containing the wrong number of arguments)

Any ideas? If somebody could please advise, would really appreciate it.

Thanks in advance.

 

[MarkK]

The IIF() function requires three arguments. Your code passes one. Syntax should be ...

IIF([I]expression, truepart, falsepart[/I])

... where expression is a boolean expression. The function returns the value of truepart if the expression is true, and the function returns the value of falsepart if the expression is false.
Try ...

=IIf([LogTime] Between [JOB_STARTED] And [JOB_FINISH], LogTime, 0)

 

[aftabn10]

Thanks lagbolt for your response. I have tried running that query but it returns no values. Could this be because the Job Started and Job Finish both have dates and times together whereas the LogTime only has the Time Value?

 

[aftabn10]

lagbolt, i have managed to sort it. I added the LogDate and LogTime as one field and ran the query on a new field and this gives my set of results.

Thanks for all your help.