Query expressoin is too complex??? 1

[mward04]

I'm not sure why I'm getting this error, I'm hoping someone can help me out. I'm using the query below and when I do a date range of 3/1/06 to 3/31/06 I get an error that says the Query expression is incomplete or too complex. However, when I do the same query from 2/1/06 to 3/1/06, there is no problem. Any ideas what in the world is going on here? Thanks!

SELECT Qry_StError_ArrTrge.[Hospital Name], Qry_StError_ArrTrge.CountOfAcctNum, Qry_StError_ArrTrge.SE, Qry_StError_ArrTrge.MinOfDate, Qry_StError_ArrTrge.MaxOfDate, Qry_Times_ArrTrge.Arr2Triage AS Mean, ([Arr2Triage]-10) AS BM, ([Arr2Triage]-10)/[SE] AS StdDevs, Qry_Times_ArrTrge.HospCode
FROM Qry_Times_ArrTrge LEFT JOIN Qry_StError_ArrTrge ON Qry_Times_ArrTrge.[Hospital Name] = Qry_StError_ArrTrge.[Hospital Name]
GROUP BY Qry_StError_ArrTrge.[Hospital Name], Qry_StError_ArrTrge.CountOfAcctNum, Qry_StError_ArrTrge.SE, Qry_StError_ArrTrge.MinOfDate, Qry_StError_ArrTrge.MaxOfDate, Qry_Times_ArrTrge.Arr2Triage, ([Arr2Triage]-10), ([Arr2Triage]-10)/[SE], Qry_Times_ArrTrge.HospCode
ORDER BY ([Arr2Triage]-10)/[SE];

 

[Golom]

How are "doing a date range"? There's nothing in the query that would allow you to specify such a range.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[mward04]

I've created a form that times to the underlying query "Qry_Times_ArrTrge"

 

[dhookom]

Is Arr2Triage ever Null? When you look at the datasheet view of Qry_Times_ArrTrge and Qry_StError_ArrTrge do you see all date and numeric fields right aligned?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mward04]

No, I exclude all of the null values in either arrival or triage in the Qry_Times_ArrTrge query. Yes, all of the date and numeric fields are right aligned.

What I also notice is weird is that if I run a query from 1/1/06 to 4/1/06, that works, but the following will not work: 3/1/06 to 4/1/06 nor 3/1/06 to 3/31/06

 

[dhookom]

Is SE ever 0?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mward04]

That's it, thanks Dhookom! I prevented SE from being = to 0 and that worked! Any idea why?

 

[dhookom]

You were dividing by 0 in your SQL. I know that would be too complex for me.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]