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;

 

[Jerry Whittle]

Do you have data for '7/1/2008' ? Actually that shouldn't make a difference.

I suggest simplifying the query. Maybe make it a plain select query and not
a crosstab. Or maybe not a Totals query. See when the error goes away and
build it back up.

Actually you might want to make another query that does the parameters and
then base the crosstab on it. I've seen where parameters can be a problem in
crosstabs. You have the PARAMETERS set up properly; however, it might be a
problem within the crosstab.

 

[EDenzer]

Ok I have now found out that I can enter a dates between '7/2/2008' up to
'7/31/2008' but I cannot enter the date '7/1/2008'. So when I enter the
date '7/1/2008' I still get a 'Data type mismatch error.' Why would I get
this it makes no sense.

 

[John Spencer]

My guess is that it is a data-driven error of some type

I would look closely at the data generated by the transform clause

For instance, you did not specify the NZ result. Not required, but best to do
so. Although what you may want is


TRANSFORM NZ(Sum(ENG_TIME_TRACKER.BILL_SETUP_HRS),0) +
NZ(Sum(ENG_TIME_TRACKER.BILL_RUN_HRS),0) & " " &
FIRST(IIF(ENG_TIME_TRACKER.DAY_COMPLETE,"YES","NO"))

What type of fields are Bill_SetUp_Hrs and Bill_Run_Hrs?
Are they number fields? If they are text fields which contain number values,
then you will get a data type mismatch error if any data that is returned
contains a value that cannot be interpreted as a number.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[EDenzer]

OK I figured it out. There was a problem with the data in the db. Not sure
how it got entered that way but that is a problem for another day.


Thanks for all the help.