"This expression is typed incorrectly, or it is too complex..." Error with Date criteria

[radstl]

Hi all,



I have a query that returns expenses from a table and summarizes them by direct costs and indirect costs.

I use an unbound form to specify the criteria, which in this case is the date range for which the expense should fall within.

My problem is some date ranges work fine and other times, I receive this error: "This Expression is typed incorrectly, or it is too complex...". For example a date range of 7/1/15 to 9/30/15 works, but 4/1/15 to 6/30/15 does not. Can anyone help? SQL below. Thanks in advance - Ryan.

SELECT qryexpense.expenseaccount, Sum(IIf([expensecode]<>"9740",[expenseamount],0)) AS directcosts, Sum(IIf([expensecode]="9740",[expamount],0)) AS indirectcosts, Sum(qryexpense.expenseamount) AS totalcosts
FROM qryexpense
WHERE ((qryexpense.expensedate)>=[Forms]![frmcosts]![start] And (qryexpense.expensedate)<=[Forms]![frmcosts]![end]))
GROUP BY qryexpense.expenseaccount;

[InsuranceGuy]

Check for null data values in your 4/1/15 to 6/30/15 date range. Also a common problem is using 6/31/15, which is an invalid date.

[radstl]

Hi Insurance Guy. I checked, and there are no null values in the fields being pulled in the query. Thanks for the suggestion.

[ssanfu]

You also might try setting the text boxes "Format" property to "Short Date" and/or set the "Input mask" property to "99/99/0000;0;_".


BTW, "Start" and "End" are reserved words in Access; these are poor words to use as object names. (not descriptive.... Start of what??)
"StartDate" and "EndDate" would be better.

List of reserved words:
http://www.allenbrowne.com/AppIssueBadWord.html

[radstl]

The names of the fields are actually frmdcidcstartdate and frmdcidcenddate but for the sake of simplicity, I shortened them in my post above

I try your suggestion. Thanks ssanfu.

[radstl]

The input mask worked, great suggestion! Thanks ssanfu.