Data type mismatch with crosstab

[EDenzer]

I have a problem with a query that I have been working on for a couple of
days now. My problem is that in my query there is a date constraint and when
I change that date to any month besides the current one I get a 'Data Type
Mismatch' error. Why do I get this and how can I fix it.

Here is my query:
TRANSFORM (Sum(NZ(ENG_TIME_TRACKER.BILL_SETUP_HRS)) +
Sum(NZ(ENG_TIME_TRACKER.BILL_RUN_HRS))) & " " &
FIRST(IIF(ENG_TIME_TRACKER.DAY_COMPLETE,"YES","NO"))
SELECT ENG_TIME_TRACKER.TRANSACTION_DATE
FROM ENG_TIME_TRACKER
WHERE ((ENG_TIME_TRACKER.TRANSACTION_DATE BETWEEN #8/1/2008# AND
#8/15/2008#) AND (Not IsNull(ENG_TIME_TRACKER.WORKORDER_BASE_ID) OR
ENG_TIME_TRACKER.DAY_COMPLETE=YES) AND
(ENG_TIME_TRACKER.TASK_DESCRIPTION='JOB' OR
IsNull(ENG_TIME_TRACKER.TASK_DESCRIPTION)))
GROUP BY ENG_TIME_TRACKER.TRANSACTION_DATE
ORDER BY ENG_TIME_TRACKER.TRANSACTION_DATE
PIVOT ENG_TIME_TRACKER.LAST_NAME;

Thanks.

 

[Jerry Whittle]

Often it is best to create a select query with and criteria and parameters to
gather up the needed records. Then use this query as the record source for
the crosstab.

Is the DAY_COMPLETE a text or a boolean Yes/No field? If boolean, the Yes
and No shouldn't have quotations marks around them.

 

[EDenzer]

Thanks. I knew it would be something simple that I was overlooking. The
Day_Complete field is a Boolean and that fixed the problem.

 

[EDenzer]

Sorry spoke too soon. I am still having the same problem. I am working on
splitting up the query.

 

[EDenzer]

Ok so now I have a new error showing up. If I enter in a date that is not in
the current month (August) like '7/1/2008' I get the error: 'This expression
is typed incorrectly, or it is too complex to be evaluated...'

Now if I enter a date like '8/1/2008' it works fine. Why is this?

Here it is:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM (Sum(NZ(ENG_TIME_TRACKER.BILL_SETUP_HRS)) +
Sum(NZ(ENG_TIME_TRACKER.BILL_RUN_HRS))) & " " &
FIRST(IIF(ENG_TIME_TRACKER.DAY_COMPLETE,"YES","NO"))
SELECT ENG_TIME_TRACKER.TRANSACTION_DATE
FROM ENG_TIME_TRACKER
WHERE ((ENG_TIME_TRACKER.TRANSACTION_DATE BETWEEN [Start Date] AND [End
Date]) AND (Not IsNull(ENG_TIME_TRACKER.WORKORDER_BASE_ID) OR
ENG_TIME_TRACKER.DAY_COMPLETE=YES) AND
(ENG_TIME_TRACKER.TASK_DESCRIPTION='JOB' OR
IsNull(ENG_TIME_TRACKER.TASK_DESCRIPTION)))
GROUP BY ENG_TIME_TRACKER.TRANSACTION_DATE
ORDER BY ENG_TIME_TRACKER.TRANSACTION_DATE
PIVOT ENG_TIME_TRACKER.LAST_NAME;