Query refuses big slice of date differences

[Jerry Maiapu]

I have this parameter query that collect its parameter from a Form text box (Start date and Endate)

When a small datediff is specified (eg. from 02/02/2010 to 10/02/2010)the query runs but when a bigger date diff is specified, it refuses to run (eg. 02/02/2010 to 03/03/2010). What is the main cause.

Facts; No null values
Query involves date/time calculations with some aggregate functions: Error message" This expression is typed incorrectly, or is too complex to be evaluated..."

Because the error is generated within the query the parameter form freezes leaving behind only option is to close using TASK MANGER.
Have someone come around such scenerio? Help needed

Ta..

[jimatqsi]

It might be helpful for you to past the SQL code from the query here for us to see. You can get that by going into SQL view mode from the query editor.

Note that in your example, what you are calling a bigger datediff is actually a smaller date range than the "small datediff." Probably just a typo on your part.

Jim

[Jerry Maiapu]

jimatqsi, actually my dates were in the format dd-mm-yy so I think I was right anyway. my query is quite long so I decided not post post it as it might scare people off but since you ask I'll do so.

Here is the SQL:

SELECT [Summary 2weeks].Salutation, 
[Summary 2weeks].[Full Name],
 [Summary 2weeks].[Days Worked], 
IIf((WorkingDays([Forms]![Parameter Collector]![Oyear],
[Forms]![Parameter Collector]![Ndate]))-([Days Worked])<0,0,
(WorkingDays([Forms]![Parameter Collector]![Oyear],
[Forms]![Parameter Collector]![Ndate]))-([Days Worked])+1) AS [Days Absent], 
IIf(IsNull([Overtime Summary.CountOfDateWorked]),0,
[Overtime Summary.CountOfDateWorked]) AS [Overtime Days],
 [Summary 2weeks].SumOfHrtMintNoAOT,
 [Summary 2weeks].SumOfHrtMintAOT,
 IIf(IsNull([Overtime Summary].[Sum Of Hours]),0,
[Overtime Summary].[Sum Of Hours]) AS [Overtime Hrs], 
IIf(IsNull([Overtime Summary].[Sum Of Minutes]),0,
[Overtime Summary].[Sum Of Minutes]) AS [Overtime Mins],
Credits_All_Total.SumOfFinalResetedCredit, 
FirstnLAstEmployess.AbsentCredit, 
[SumOfFinalResetedCredit]-[AbsentCredit] AS OverallCredit
FROM FirstnLAstEmployess INNER JOIN ([Overtime Summary]
 RIGHT JOIN (Credits_All_Total INNER JOIN [Summary 2weeks] ON
 Credits_All_Total.EmployeeID = [Summary 2weeks].EmployeeID) ON 
[Overtime Summary].EmployeeID = Credits_All_Total.EmployeeID) ON
 FirstnLAstEmployess.Employees_EmployeeID = Credits_All_Total.EmployeeID
ORDER BY [Summary 2weeks].[Full Name];

More Infor:

The joins are not based on tables but on Query: The parameter query is called Alltwoweek. ok Summary 2weeks above (bolded in lines 123)is a summary query based on Alltwweek query:

So in actuall fact the parameter query is participating indirectly with the posted query above.

Summary 2weeks and Alltwoweek when run is ok but the above query poses the problem with long date ranges like I mentioned.


Thanks so much.

For clearity I will edit my db containing the SQl and post it within the next 30 to 40 minutes time.

[jimatqsi]

Yes, maybe posting the db will be necessary.

One of the things I do in such a case is to copy my query and delete columns from my query, one by one, until the error goes away. But since your program is locking up, maybe I would add the columns one by one until the error occurs. Then I would at least know what column is causing the error to occur.

Jim

[Jerry Maiapu]

jimatqsi , thanks maybe I should try that first.