Query Not Visible When Connecting to Excel

[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.

 

[Aboo]

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.

Yes it is in excel I am getting error. Any work sound in Access?

 

[CJ_London]

You could trying running the query in access to update excel (if that is what you are doing) or in excel perhaps open an instance of access to run your query.

 

[Gasman]

Yes it is in excel I am getting error. Any work sound in Access?

Create your own NZ function?

 

[sonic8]

Create your own NZ function?

Will not change the situation.
The problem is that only functions natively implemented in the JET/ACE-Engine are available in queries from external applications (Excel in this case). Any function in an external library or written in VBA cannot be "seen" by the query engine when not run in Access directly.

 

[CJ_London]

Excel doesnt recognise null - in cells you would use empty and I suspect you would not be able to use an excel function in a sql statement. But always worth a try

 

[Gasman]

Excel doesnt recognise null - in cells you would use empty and I suspect you would not be able to use an excel function in a sql statement. But always worth a try

Well I tried my own function and the query was not offered in Excel?
Perhaps a query from another query which uses the NZ() ?

Edit: Nope, that does not work either.

 

[KitaYama]

Option 1: Usining IIF instead of Nz

I made this query in Access:

SELECT tblComboHistory.*
FROM tblComboHistory
WHERE ((Not (Nz([frm],"Empty")) Is Null));

as stated Excel can not see the query because of Nz

I changed my query to :

SELECT tblComboHistory.*
FROM tblComboHistory
WHERE ((Not (IIf(IsNull([frm]), "Empty", [frm])) Is Null));

Now Excel can see the query.


Option 2:Alternatively, the query can be changed to make table. And Excel be linked to the table instead of the query.

Option 3 : You can also programmatically import Access queries using DAO or ADO references in Excel VBA, bypassing the UI limitation.
I have to run now. If nobody give you a sample, I'll show something later.

 

[Gasman]

OK this works. Query is qryPressureTest
First record has Null in Heart field which is Expr1003.

SELECT tblPressure.PressureDateTime AS [DateTime], tblPressure.Systolic, tblPressure.Diastolic, IIF(ISNULL(tblPressure.Heart),0,tblPressure.Heart), tblPressure.Comment, tblMonitor.MonitorMake
FROM tblMonitor INNER JOIN tblPressure ON tblMonitor.MonitorID = tblPressure.MonitorIDFK
WHERE (((tblPressure.PressureDateTime)>#1/1/2025#) AND ((tblMonitor.MonitorID)=1))
ORDER BY tblPressure.PressureDateTime;

 

[Aboo]

Thx. How would I use your solution to my situation?

 

[June7]

Can force Excel to recognize Nz() by setting a VBA Editor reference to Microsoft Access x.x Object Library.

Your expression using IIf:

Sum(IIf([Amount] Is Null, 0, [Amount]))

 

[Gasman]

Thx. How would I use your solution to my situation?

Use the IIF() in place of the NZ(), thought @June7's suggestion is better.