Question on data mismatch error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm getting an error on this of "Datayype mismatch in criteria expression". What I understand of that message tells me that the field types don't match somewhere (my understanding is rather elementary... thanks for giving me another lesson!).
How can that be when it's referencing the same table? Can anyone help me on this one?

SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate), MAX(a.OdometerRead) AS ThisMonth, MAX(b.OdometerRead) AS PreviousMonth

FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b

ON (DateSerial(Year(a.MaintDate),Month(a.MaintDate),1)=DateSerial(Year(b.MaintDate),1+Month(b.MaintDate),1))
AND (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo)

GROUP BY a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate);
 
Hi,



Is it possible that MaintDate is NOT a date_time field, but a string or
something like that? Worse, is there NULL as values? DateSerial(Null,
Null, 1) generates an error.



Hoping it may help,
Vanderghast, Access MVP



kl said:
I'm getting an error on this of "Datayype mismatch in criteria
Click to expand...
expression". What I understand of that message tells me that the field types
don't match somewhere (my understanding is rather elementary... thanks for
giving me another lesson!).
How can that be when it's referencing the same table? Can anyone help me on this one?

SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate),
Click to expand...
MAX(a.OdometerRead) AS ThisMonth, MAX(b.OdometerRead) AS PreviousMonth
 
THANK YOU!!! You pointed out another error on my part... I should have that date field as a required field!!! I had 1 stinking null value... thanks much!!! now all I have to do is have my report see the month that I'm looking for... I'll work on that! Hopefully I can figure out what I'm trying to do. Sometimes I feel like going back to 1st grade. Thanks again!
 
Back
Top