Report between dates - allows 2006 dates but not 2005

[sabrina30]

I have a report. I set it so that it opens a form to select the dates, such as 01/01/06 - 12/31/06 and that works fine. But, if I select 01/01/05 - 12/31/05 or 01/01/05 - 12/31/05 I get "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of th eexpression to variables." My response is HUH.

When I run the query using 01/01/05 - 12/31/06 it works fine. I thought maybe it was because I was sorting on[TermDate], so I changed it to sort on name. Same problem.

I have another report that work similarly..using a form to select the dates and it works fine. I tried comparing them to find what is different and can't figure it out.

In the query for the report that works I am using
"Between [Forms]![frmSelectDates]![txtStartDate] And [Forms]![frmSelectDates]![txtEndDate]"
In the query for the report that is not working properly I am using
"Between [Forms]![frmSelectDatesExit]![txtStartDate] And [Forms]![frmSelectDatesExit]![txtEndDate]"

Hope this makes sense and someone can help me with this.

 

[rjoubert]

I had the same exact response as you...HUH??? After a little googling, I found this article that deals with this issue...give it a shot.

http://www.stoneedge.com/help/defau...C/troubleshooting/error/error-toocomplex.html

Good Luck!

 

[sabrina30]

Thank you for the tip, but unfortunately that did not fix the problem.

 

[sabrina30]

Ok, the problem was caused because one of the records had a question unansered. For some reason if none of the Checkboxes are selected in each question, then an error occurs for that field and the report will not run. I will do some research on bypassing this. How to not get an error in a field that us Null.

 

[lameid]

Isnull() Function returns true if the value is null.

You can limit your results by using

Is Not Null

for criteria for the problem field.

Or use

NZ(Value,ValueIfNull)

To replace the value with something that wont create an error in your calculated field.

 

[sabrina30]

Thank you. I ended up regrouping the 2 fields that were giving me trouble and setting a default to the field. That way all fields have an answer of some kind.

I do however, have one other issue. My boss wants a report of what the average answer is. The problem I think is the way I have the fields set up. Always = 4, Frequently = 3, Occassionally = 2, Never = 1, N/A = 0.

I have one report that adds the totals and one that is supposed to give me the average answer. So, that if 20 people respond to the survey and 7 select Frequently, 3 select Always, and 10 select N/A. I want "3" to display on the report. However the I don't know how to make it ignore responses of N/A so that is not counted in the Average.

 

[lameid]

Aggregate functions ignore nulls. So if the value was null instead of 0 you would get 3.3 instead of 1.65.

You might also try something like

Sum(Field)/Sum(IIF(Field = 0,0,1))

That should also give you 3.3. If you really want the integer result, you might try integer division instead... Use \ instead of / for the division (backslash instead of forward slash).

 

[sabrina30]

Interesting, Thank you. I have it set to no Decimals. So I get 3 instead of 3.3, which is less confusing for my boss. So, in an Option Group, if I simply do not put a value to the N/A would that be considered as Null?

 

[sabrina30]

Well, I can't edit my previous post. You have to put a value to each field in an option group. I will stick with the formula you provided, lameid, thank you