Date()-ClosedDate - With a twist!!

[aarondewberry]

All

I have a table (tblJobs), that has a short date field called ClosedDate in it.
Next I have another table (tblAgent) with a short time field in it called SchedHours. Each agent has 7 rows each in tblAgent, one for monday, tuesday, weds.. etc..
For each day, the agents SchedHours will either be 07:12 or 00:00 (depending on whether the agent is due in or not)

eg.
Dave Sunday 00:00
Dave Monday 07:12
Dave Tuesday 07:12
.....

What I want to do is calculate the number of days between date() and ClosedDate, WHERE the agents SchedHours are >0
I will need to link the two tables by the AgentName field.

ie. if my SchedHours were >0 for mon-fri and the ClosedDate was 06/07/07, then the number of days would be 4.

Make sense?

 

[SkipVought]

Hi,

Short Date/Time? Why should that matter? It is Date/Time, which is just a number, like right now in North Texas, its, 39275.41554, format not withstanding.

Select [AgentName ], Date()-[ClosedDate] As DaysSinceClose
...
Where [SchedHours]>0

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}