Problem With Query and Total Time

rainman89 · Mar 6, 2007

hi all,
i am trying to create a report based on my query, and would like to split the total time up by 2 diff tasks. admin and investigative. problem i am running into is when using the total time functino i have and setting the criteria to match the task ID i am getting an error saying it is too complex. any ideas?
IT goes something like this

Code:

AdminTime: NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+(Nz([ExpenseHour]))+(Nz([ExpenseMinute]/60))
WHERE TaskID = 2

can this not be done within a single query?

MStef · Mar 7, 2007

I think you make a mistake in NZ function. Try to start with
IIf(Nz(..........
and in two last NZ you need a VALUEIFNUL.

Nz function says;

(Nz(variant[, valueifnull])

neileg · Mar 7, 2007

MStef said:
I think you make a mistake in NZ function. Try to start with
IIf(Nz(..........
and in two last NZ you need a VALUEIFNUL.

Nz function says;

(Nz(variant[, valueifnull])

If you don't specify [valueifnull] it defaults to zero, so that's not the problem.

rainman89, where is the WHERE clause in your query? I'm assuming it's not actually part of your calculated field. Can you post the full SQL?

rainman89 · Mar 7, 2007

Thanks to both of you for your replies. my query works fine, just cant get it to do what i need.

Neil,
My problem isnt with the where cause. i can get that to work. my problem is if i have a total time, calculated like the above, do i have to have 3 seperate queries one to find:
admin time where taskid=2
investigator time where taskid<>2
and then another that ties them together? or can i do this all in one query?
reason is i need to create a report that shows admin time/investigator time seeing as they are billed seperately.
Also ive tried with the 3 seperate queries but i got an error saying that there was a loop occuring.

neileg · Mar 7, 2007

I think it's a bracket issue. Try this

Code:

AdminTime: NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime])/60)+Nz([ExpenseHour])+Nz([ExpenseMinute]/60))

rainman89 · Mar 7, 2007

Not a problem with the current query it works fine!

OK im not making myself very clear i guess.
I was wondering if i could calculate 2 diff totaltimes, 1 for admin 1 for investigatvie, in the same query using diff parameters
that is. calculate

Code:

AdminTime: NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+(Nz([ExpenseHour]))+(Nz([ExpenseMinute]/60))
WHERE TaskID = 2

Code:

InvestTime: NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+(Nz([ExpenseHour]))+(Nz([ExpenseMinute]/60))
WHERE TaskID <>2

in one query! or because i use taskid for both if it needs to be done with seperate queries

neileg · Mar 7, 2007

Yes you can but I'm sure the error message relates to the syntax errors in your formulae, rather than the fact that you have two formulae.

rainman89 · Mar 7, 2007

I def dont think this is right. i believe u are correct. cant find the WHERE!!

Code:

SELECT qryCaseClientName.CaseNumber, qryCaseClientName.CaseName, qryCaseClientName.ClientName, Task.TaskID, Employee.EmployeeInitials, Task.TaskName, ApprovedTime.Notes, ApprovedTime.ExpenseDate, ApprovedTime.ExpenseAmount, NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+(Nz([ExpenseHour]))+(Nz([ExpenseMinute]/60)) AS AdminTime
FROM qryCaseClientName, Task INNER JOIN (Employee INNER JOIN ApprovedTime ON Employee.EmployeeID = ApprovedTime.EmployeeID) ON Task.TaskID = ApprovedTime.TaskID
GROUP BY qryCaseClientName.CaseNumber, qryCaseClientName.CaseName, qryCaseClientName.ClientName, Task.TaskID, Employee.EmployeeInitials, Task.TaskName, ApprovedTime.Notes, ApprovedTime.ExpenseDate, ApprovedTime.ExpenseAmount, NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+(Nz([ExpenseHour]))+(Nz([ExpenseMinute]/60)), qryCaseClientName.CaseID
HAVING (((Task.TaskID)="2") AND ((qryCaseClientName.CaseID)=[Forms]![frmSearch].[cboCaseNumber]));

Problem With Query and Total Time

rainman89

I cant find the any key..

MStef

Registered User.

neileg

AWF VIP

rainman89

I cant find the any key..

neileg

AWF VIP

rainman89

I cant find the any key..

neileg

AWF VIP

rainman89

I cant find the any key..

Similar threads

Users who are viewing this thread