Query problem

[Spira]

this has been giving me a headache. if there is no profit for a particular date then nothing appears. i have tried the Nz() function but keep on getting errors
i have attached the D.B
the query concerned is QryProfitMonth
after this is completed i would like to output the result to the dates table to view the profits for each separate month in a year,

PLLLLLLLLLLEEEEEEASSSSSSEEEEEE HEEEEEEELLLLLLPPPPP !!!!!!!!

 

[pdx_man]

You already have your Date formatted. You don't need to do it in your criteria:

SELECT Format([Date],"mm/yyyy") AS ProfitMonth, Sum(tblJob.Profit) AS SumOfProfit
FROM tblJob
WHERE (((tblJob.Date)=[Enter Date]))
GROUP BY Format([Date],"mm/yyyy");

 

[Spira]

You already have your Date formatted. You don't need to do it in your criteria:

SELECT Format([Date],"mm/yyyy") AS ProfitMonth, Sum(tblJob.Profit) AS SumOfProfit
FROM tblJob
WHERE (((tblJob.Date)=[Enter Date]))
GROUP BY Format([Date],"mm/yyyy");

Thanks but this still doesnt resolve the blank results!!!!!!!

 

[Mile-O]

Something to try

SELECT Format([Date],"mm/yyyy") AS ProfitMonth, Nz(Sum(tblJob.Profit),0) AS SumOfProfit
FROM tblJob
WHERE (((tblJob.Date)=[Enter Date]))
GROUP BY Format([Date],"mm/yyyy");

 

[Jon K]

I have added a table tblMonths and a series of two queries in your database.

In the two versions of Query2 (one for the full year 2004, the other up to the current month), I used Left Join and Nz() to return the months with no jobs.

You can run Query2 to see if it is what you wanted.

 

[Spira]

I have added a table tblMonths and a series of two queries in your database.

In the two versions of Query2 (one for the full year 2004, the other up to the current month), I used Left Join and Nz() to return the months with no jobs.

You can run Query2 to see if it is what you wanted.

THANKS JON !!!!!!!!!!!!!!!!!!!!!!!! PROBLEM SOLVED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!