Return 0 IFNULL Some Help Please

[swanside]

Hello all.
I am in the need of some help please.

I have an SQL statement that works on a MySQL Database, but I need to get it to work on an Access Database.

The statement is,

SELECT
Job_No,
IFNULL((SELECT sum(Paying_Rate*Working_Hrs) FROM Labour WHERE Job_No = Job.Job_No),0) AS Labsubtotal,
IFNULL((SELECT sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)) FROM Material WHERE Job_No = Job.Job_No),0) AS Matsubtotal,
Labsubtotal*0.175 AS Labvat,
Matsubtotal*0.175)AS Matvat,
Matsubtotal + Labsubtotal AS subtotal,
Labsubtotal*0.175 + Matsubtotal*0.175  AS vat,
Labsubtotal*1.175 + Matsubtotal*1.175 AS TOTAL,
Job.Order_Date,
Job.File_No,
Job.Contract,
Job.Order_Site_Address,
Job.Job_Description,
Job.Invoice_Tax_Date, 
Job.CustomerRef,
Job.Customer_Name,
Customer.Billing_Address
FROM Job
LEFT JOIN Customer
ON(Job.Customer_Name=Customer.Customer_Name)

I am having trouble with the IFNULL part as it comes back with an Undefined Function.
I have searched through the forums, and found one with Nz, but I tried to substitute the code, but it wont and comes back with undefined functions.

If anybody can help, I would be greatful.
Thanks
Paul.

[Stewart Ross]

Hi. Nz should be a straightforward substitute for your IFNULL. Its syntax is
Nz([some expression], value to return), the same as the IFNULL.

I am not sure that the subqueries inside the IFNULLs will be accepted by Access, however (in that they are returning sets of values as fields within your outermost SELECT statement).

You would be able to do away with the subqueries if you joined the labour and materials tables to your query on the job_no field. It would not take you long to use the Access query editor to join your tables and select the relevant fields. Access generates the SQL for the query from the graphical query editor selections and joins on the fly.

If Nz is not recognised (there have been cases where it is treated as invalid for reasons that remain unclear) an alternative is to use an in-line if (IIF) statement with the IsNull function:

IIF(IsNull([some value]), 0, [some value])

-Stewart

[swanside]

Thanks Stewart.

I changed my statement to

SELECT
Job_No,
Nz([SELECT sum(Paying_Rate*Working_Hrs)],0) AS Labsubtotal,
Nz([SELECT sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Addi  tional_Cost/100)) ],0) AS Matsubtotal,
Labsubtotal*0.175 AS Labvat,
Matsubtotal*0.175 AS Matvat,
Matsubtotal + Labsubtotal AS subtotal,
Labsubtotal*0.175 + Matsubtotal*0.175  AS vat,
Labsubtotal*1.175 + Matsubtotal*1.175 AS TOTAL,
Job.Order_Date,
Job.File_No,
Job.Contract,
Job.Order_Site_Address,
Job.Job_Description,
Job.Invoice_Tax_Date, 
Job.CustomerRef,
Job.Customer_Name,
Customer.Billing_Address
FROM Job
inner JOIN Customer
ON Job.Customer_Name=Customer.Customer_Name
inner JOIN Labour
ON Job.Job_No=Labour.Job_No
inner JOIN Material
ON Job.Job_No=Material.Job_No

Now, I just need to find out more about inner joins, and left joins as I am getting a SYntax Error, Missing Operator in

Job.Customer_Name=Customer.Customer_Name
inner JOIN Labour
ON Job.Job_No=Labour.Job_No
inner JOIN Material
ON Job.Job_No=Material.Job_No

Thanks
Paul.

[Stewart Ross]

Hi Paul. You hadn't actually removed the subqueries (which, as I thought, Access does not like). Also, the syntax errors arose because there was no bracketing of the inner joins.

Here's a corrected version, tested on some dummy data. You will have to test it yourself to make sure all calculations work as expected.

SELECT Job.Job_no, 
Nz(Sum([Paying_Rate]*[Working_Hrs]),0) AS Labsubtotal, 
Nz(Sum([Quantity]*[Unit_Price]+([Quantity]*[Unit_Price]*+[Additional_Cost]/100)),0) AS Matsubtotal, 
Labsubtotal*0.175 AS Labvat, Matsubtotal*0.175 AS Matvat, Matsubtotal+Labsubtotal AS subtotal, Labsubtotal*0.175+Matsubtotal*0.175 AS vat, Labsubtotal*1.175+Matsubtotal*1.175 AS TOTAL, 
Job.Order_date, 
Job.File_No, 
Job.Contract, 
Job.Order_Site_Address, 
Job.Job_Description, 
Job.Invoice_Tax_date, 
Job.CustomerRef, 
Job.Customer_Name, 
Customer.Billing_Address
FROM 
((Job INNER JOIN Customer ON Job.Customer_Name = Customer.Customer_Name) 
INNER JOIN Labour ON Job.Job_no = Labour.job_No) 
INNER JOIN Materials ON Job.Job_no = Materials.Job_no
GROUP BY Job.Job_no, Job.Order_date, Job.File_No, Job.Contract, Job.Order_Site_Address, Job.Job_Description, Job.Invoice_Tax_date, Job.CustomerRef, Job.Customer_Name, Customer.Billing_Address;

I note the use of two arithmetic operators *+ in sequence in line 3 of this code (as in all versions of your code) which I suggest you check and resolve, as it will simply be equivalent to the multiplication alone at present.

-Stewart