This expression is typed incorrectly...

[outersha]

Hi All,

I have a user which is complaining about the above error inconsistently when trying to key in start and end date parameters to generate a report. The date range can be for a month, three months or a 6 month span. Sometimes the report will run successfully and sometimes not. Here complete error message…

“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 the expression to variables.”

OS: 2000/XP
Access version: 2002

Also here is the SQL view. Any help on this would be great!

SELECT qryNewRegistrantsByRange.RegDate, qryNewRegistrantsByRange.ReActDate, qryNewRegistrantsByRange.Live, qryNewRegistrantsByRange.Surname, qryNewRegistrantsByRange.Othernames, qryNewRegistrantsByRange.Age, qryNewRegistrantsByRange.Sex, qryNewRegistrantsByRange.JobType, qryNewRegistrantsByRange.JobClass, qryNewRegistrantsByRange.EmploymentStatus, tblJobClass.JobClass AS Description
FROM qryNewRegistrantsByRange LEFT JOIN tblJobClass ON qryNewRegistrantsByRange.JobClass = tblJobClass.JobClassID
GROUP BY qryNewRegistrantsByRange.RegDate, qryNewRegistrantsByRange.ReActDate, qryNewRegistrantsByRange.Live, qryNewRegistrantsByRange.Surname, qryNewRegistrantsByRange.Othernames, qryNewRegistrantsByRange.Age, qryNewRegistrantsByRange.Sex, qryNewRegistrantsByRange.JobType, qryNewRegistrantsByRange.JobClass, qryNewRegistrantsByRange.EmploymentStatus, tblJobClass.JobClass
HAVING (((qryNewRegistrantsByRange.RegDate) Between [Forms]![Employment Registry]![txtStartDate] And [Forms]![Employment Registry]![txtEndDate]))
ORDER BY qryNewRegistrantsByRange.RegDate;

 

[lupins46]

What is the point of the grouping? You aren't using any function that requires grouping.

 

[ByteMyzer]

Try:
[tt]
SELECT qryNewRegistrantsByRange.RegDate, qryNewRegistrantsByRange.ReActDate, qryNewRegistrantsByRange.Live, qryNewRegistrantsByRange.Surname, qryNewRegistrantsByRange.Othernames, qryNewRegistrantsByRange.Age, qryNewRegistrantsByRange.Sex, qryNewRegistrantsByRange.JobType, qryNewRegistrantsByRange.JobClass, qryNewRegistrantsByRange.EmploymentStatus, tblJobClass.JobClass AS Description
FROM qryNewRegistrantsByRange LEFT JOIN tblJobClass ON qryNewRegistrantsByRange.JobClass = tblJobClass.JobClassID
GROUP BY qryNewRegistrantsByRange.RegDate, qryNewRegistrantsByRange.ReActDate, qryNewRegistrantsByRange.Live, qryNewRegistrantsByRange.Surname, qryNewRegistrantsByRange.Othernames, qryNewRegistrantsByRange.Age, qryNewRegistrantsByRange.Sex, qryNewRegistrantsByRange.JobType, qryNewRegistrantsByRange.JobClass, qryNewRegistrantsByRange.EmploymentStatus, tblJobClass.JobClass
HAVING (((qryNewRegistrantsByRange.RegDate) Between CDate([Forms]![Employment Registry]![txtStartDate]) And CDate([Forms]![Employment Registry]![txtEndDate])))
ORDER BY qryNewRegistrantsByRange.RegDate;
[/tt]

 

[outersha]

Sorry, I should have clarified early. For the SQL view above, let’s called this qry #2 and within the process qry #2 uses qry #1. Qry #2 is based over qry #1 and a second table. The report uses qry #2 to produce the final output. The grouping is performed in qry#2 only.

 

[outersha]

Thanks ByteMyzer for the prompt response, but no go...

 

[lupins46]

Sorry but I have repeat the point.
The grouping in this query is doing nothing other than sorting the records. (Unless, I suppose, you have lots of duplicate records in which case use Distinct instead) And when it goes into the report the query sort sequence is irrelevant.

 

[KenReay]

Hi

I have sometimes seen this error presented when a column or criteria has an invalid value

since you say the error is intermitent, could it be that it is dependant on the criteria entered by the user (and/or the data in the table(s)).

So possible causes:

Does you application check that user has actually entered a start and end date?, could it be that user is sometimes leaving one or more dates blank causing a Null value in the query

Are the text box controls txtStartDate and txtEndDate set with Formats which allow only valid dates to be entered, or could user be entering an invalid date format?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK