'Data Type Mismatch In Criteria expression' error when make-table query runs [1/1]
- From: st45@xxxxxxxxxxxxxxxxx
- Date: Fri, 1 Sep 2006 08:09:20 +0100
Thanks for your reply...
Of course, you are correct to ask .. why?
It was because I'm no expert with Access and I had a different problem I needed to quickly get around.
I used a 'make table' because I had no time to work out why, when I tried to use the original 'select query' with a run-time criteria selection on 'next service date' I kept getting an unexpected 2nd prompt after inputting the date range.
As I was under pressure to get some output I used the create table to get all the calculated data out and then queried that (with date selection) for the actual reports.
Your reply did give me an idea though and I set the 'make table' back to be a select one . The error still occurs.
If it's any help, the sql of the query is attached. I realise there's probably a more 'elegant' way of doing this but it worked for the first 800 vehicles (from a different data source).
Maybe you could give me a pointer regarding that error message.. Does that definitely and irrefutably mean that there is a mismatch of data types in the underlying data? Or could something else cause it?
Thanks very much for taking the time to read this and for any help you can offer.
Bob
SELECT
[Tbl Vehicles].Registration_Number,
[Tbl Vehicles].Registration_Date,
[Tbl Vehicles].Make_Model,
[Tbl Vehicles].Service_Schedule,
[Last_Known_Mileage]/([Date_of_mileage]-[Registration_Date])*365 AS Annual_Mileage,
IIf([Tbl Last Service]![Last Service Date]>1/1/2004,[Tbl Last Service]![Last Service Date],[Tbl Vehicles]![Registration_Date]) AS Calc_Date,
IIf([Annual_Mileage]>[Service_Schedule],"Miles","Time") AS ServType,
DateValue([Calc_Date]+([Service_Schedule]/([Last_Known_Mileage]/([Date_of_mileage]-[Registration_Date])))) AS 1st_service_Due_by_miles,
DateValue([Calc_Date]+365) AS 1st_service_Due_by_date,
IIf([1st_service_Due_by_miles]<[1st_service_Due_by_date],[1st_service_Due_by_miles],[1st_service_Due_by_date]) AS Next_Service,
([Last_Known_Mileage]/([Date_of_mileage]-[Registration_Date]))*([Next_Service]-[Registration_Date]) AS Estimated_Mileage_Next_Service,
[Tbl Vehicles].[MOT Due],
IIf(Abs([Next_Service]-[MOT Due])<30,"YES",IIf([MOT Due]<Now(),"YES","No")) AS [MOT Required],
[Tbl Last Service].[Last Service Date], [Tbl Last Service].[Last Service Mileage], [Tbl Rentokil Drivers 150806].Driver_Name,
[Tbl Rentokil Drivers 150806].Driver_Mobile,
[Tbl Vehicles].Branch,
[Tbl Vehicles].Client,
[Tbl Vehicles].Inspection_Scheduled_Date,
[Tbl Vehicles].Last_Known_Mileage,
[Tbl Vehicles].Date_of_Mileage,
[Tbl Vehicles].Set_To_Delete
FROM [Tbl Last Service] RIGHT JOIN ([Tbl Vehicles] LEFT JOIN [Tbl Rentokil Drivers 150806] ON [Tbl Vehicles].Registration_Number = [Tbl Rentokil Drivers 150806].Registration_Number) ON [Tbl Last Service].Registration_Number = [Tbl Vehicles].Registration_Number
GROUP BY [Tbl Vehicles].Registration_Number, [Tbl Vehicles].Registration_Date, [Tbl Vehicles].Make_Model, [Tbl Vehicles].Service_Schedule, [Tbl Vehicles].[MOT Due], [Tbl Last Service].[Last Service Date], [Tbl Last Service].[Last Service Mileage], [Tbl Rentokil Drivers 150806].Driver_Name, [Tbl Rentokil Drivers 150806].Driver_Mobile, [Tbl Vehicles].Branch, [Tbl Vehicles].Client, [Tbl Vehicles].Inspection_Scheduled_Date, [Tbl Vehicles].Last_Known_Mileage, [Tbl Vehicles].Date_of_Mileage, [Tbl Vehicles].Set_To_Delete, DateValue([Registration_date]+(([Service_Schedule]*2)/([Last_Known_Mileage]/([Date_of_mileage]-[Registration_Date])))), DateValue([Registration_date]+730), DateValue([Registration_date]+(([Service_Schedule]*3)/([Last_Known_Mileage]/([Date_of_mileage]-[Registration_Date])))), DateValue([Registration_date]+1095), DateValue([Registration_date]+(([Service_Schedule]*4)/([Last_Known_Mileage]/([Date_of_mileage]-[Registration_Date])))), DateValue([Registration_date]+1460), DateValue([Registration_date]+(([Service_Schedule]*5)/([Last_Known_Mileage]/([Date_of_mileage]-[Registration_Date])))), DateValue([Registration_date]+1825), [Tbl Vehicles].Driver_Name
HAVING ((([Tbl Vehicles].Set_To_Delete)=No));
In message <bnvef2p916hh90bk7n74hnm86emj5ugnms@xxxxxxx>, John Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> writes
On Fri, 1 Sep 2006 00:33:25 +0100, bleighfield
<bleighfield@xxxxxxxxxxxxxxxxx> wrote:
After calculations are complete a new table is output and that is used
for
subsequent reporting. The new table is overwritten each time the
calculations query is run.
Ummm...
WHY?
You can create a Select Query returning the desired 40 records.
You can base a Report on this Query. You can base a Form on this Query
(and even update it). You can export the data in this query.
I don't see *any* benefit to the extra overhead (and the problems
you're experiencing) of creating a new Table.
Just use the Query!
John W. Vinson[MVP]
- Follow-Ups:
- References:
- Prev by Date: Re: Percentage of total
- Next by Date: Re: sql query where search criteria contains a single quote e.g O'Neill
- Previous by thread: Re: 'Data Type Mismatch In Criteria expression' error when make-table query runs
- Next by thread: Re: 'Data Type Mismatch In Criteria expression' error when make-table query runs [1/1]
- Index(es):
Relevant Pages
|
