Query problem

[Guest]

I have written the following query and am getting the error message " The
expression is typed incorrectly, or 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 the expression to variables"

<b>SELECT DateValue(Calls.CallDateTime) AS CallDate, Count(Calls.Subject) AS
Subject1
FROM Calls
WHERE Calls.CallType=6
GROUP BY DateValue(Calls.CallDateTime)
HAVING DateValue(Calls.CallDateTime) Between
DateValue(Forms!frmDateRange!txtStartDate) And
DateValue(Forms!frmDateRange!txtEndDate);</b>

The problem has to do with the CallType having a value of 6. The CallType
field can have a value anywhere from 1-12 and it works fine for all of them
except for 6. Does anyone have an idea what the culprit might be for this?

 

[Ken Snell \(MVP\)]

First thing I look for is the possibility of Null values in a field that is
the argument of a function that cannot accept Null argument. For example,
does Calls.CallDateTime field have Null values in it? DateValue cannot
accept Null values as its argument.

 

[Guest]

Hi Ken,

Looks like that was exactly the problem. Thank you very much!

 

[Marshall Barton]

I have written the following query and am getting the error message " The
expression is typed incorrectly, or 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 the expression to variables"

<b>SELECT DateValue(Calls.CallDateTime) AS CallDate, Count(Calls.Subject) AS
Subject1
FROM Calls
WHERE Calls.CallType=6
GROUP BY DateValue(Calls.CallDateTime)
HAVING DateValue(Calls.CallDateTime) Between
DateValue(Forms!frmDateRange!txtStartDate) And
DateValue(Forms!frmDateRange!txtEndDate);</b>

The problem has to do with the CallType having a value of 6. The CallType
field can have a value anywhere from 1-12 and it works fine for all of them
except for 6. Does anyone have an idea what the culprit might be for this?

The only thing I see wrong with it is that the HAVING clause
condition should be part of the WHERE clause. Maybe that's
causing an odd interaction with something else?

I can't tell from here, but is it possible that the date
fields do not have a value or their value is Text??