Query Not Visible When Connecting to Excel (1 Viewer)

[Aboo]

I have a query which I connected to the Excel and it was working fine. Recently I noticed that one field EstCost was not showing value for one field. So I changed it to the formula with NZ function. It works well in Access but the query is showing an error, (Expression Error, the key did not match any rows in the table). I tried to recreate a new file but this query is not visible altogether when I try to connect. My SQL is as under:

SELECT Vehicles.VehicleID, Vehicles.[VIN Number], Vehicles.Make, Vehicles.Model, Vehicles.Year, Vehicles.[Price (JYP)], Vehicles.[Price (AUD)], Sum(tblExpenses.Amount) AS SumOfAmount, [Price (AUD)]+Nz(Sum([Amount]),0) AS [Est Cost], Vehicles.[Rec No], Vehicles.SStatus, Vehicles.[Purchase Mileage], Vehicles.Rego
FROM Vehicles LEFT JOIN tblExpenses ON Vehicles.VehicleID = tblExpenses.VehicleID
WHERE (((Vehicles.SStatus)<>"Added to Fleet" And (Vehicles.SStatus)<>"Sold" And (Vehicles.SStatus)<>"Cancelled"))
GROUP BY Vehicles.VehicleID, Vehicles.[VIN Number], Vehicles.Make, Vehicles.Model, Vehicles.Year, Vehicles.[Price (JYP)], Vehicles.[Price (AUD)], Vehicles.[Rec No], Vehicles.SStatus, Vehicles.[Purchase Mileage], Vehicles.Rego;

Will appreciate if you could let me know where I am going wrong.

 

[tvanstiphout]

> [Price (AUD)]+Nz(Sum([Amount]),0) AS [Est Cost]
In the SumOfAmount expression you're using tblExpenses.Amount; you should do that here too. Being explicit about which table the Amount should be read from is a good idea. Same for [Price (AUD)], which in a previous expression you explicitly state to be in the Vehicles table.
If that does not help, then temporarily add tblExpenses.Amount as a column, so you can sort both Price and Amount up and down, and eyeball the values for anomalies. Also inspect the data types these fields in the linked tables; perhaps they are not Currency as they should be.

 

[Aboo]

Thx. I changed it.
SELECT Vehicles.VehicleID, Vehicles.[VIN Number], Vehicles.Make, Vehicles.Model, Vehicles.Year, Vehicles.[Price (JYP)], Vehicles.[Price (AUD)], Sum(tblExpenses.Amount) AS SumOfAmount, [Price (AUD)]+Nz(Sum([tblExpenses]![Amount]),0) AS [Est Cost], Vehicles.[Rec No], Vehicles.SStatus, Vehicles.[Purchase Mileage], Vehicles.Rego
FROM Vehicles LEFT JOIN tblExpenses ON Vehicles.VehicleID = tblExpenses.VehicleID
WHERE (((Vehicles.SStatus)<>"Added to Fleet" And (Vehicles.SStatus)<>"Sold" And (Vehicles.SStatus)<>"Cancelled"))
GROUP BY Vehicles.VehicleID, Vehicles.[VIN Number], Vehicles.Make, Vehicles.Model, Vehicles.Year, Vehicles.[Price (JYP)], Vehicles.[Price (AUD)], Vehicles.[Rec No], Vehicles.SStatus, Vehicles.[Purchase Mileage], Vehicles.Rego;

But the excel spreadsheet still has the same error. Expression Error, the key did not match any rows in the table

I wish I could attach the spreadsheet.

 

[CJ_London]

Where are you running this query? In getting the impression it is in excel. If it is then be aware that the nz function is not supported in excel.