By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,635 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Delaut values in a query if no record in table

P: 1
I am using a select query to sum 2 fields in another select query, but if their are no records and the first select is empty I get a blank. I would like to get a zero if this happens. is this possible?

I am using Access 2007

select query 1
SELECT Trans.[LABOR$], Trans.[LBR-COST$], Trans.[Close-Date]
FROM Trans
WHERE (((Trans.[Close-Date])=Date()-1) AND ((Trans.[LBR-TYPE])="CCB"));


select query 2
SELECT Sum(BSLaborDaily.[LABOR$]) AS [SumOfLABOR$], Sum(BSLaborDaily.[LBR-COST$]) AS [SumOfLBR-COST$]
FROM BSLaborDaily;
Aug 4 '07 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
I am using a select query to sum 2 fields in another select query, but if their are no records and the first select is empty I get a blank. I would like to get a zero if this happens. is this possible?

I am using Access 2007

select query 1
SELECT Trans.[LABOR$], Trans.[LBR-COST$], Trans.[Close-Date]
FROM Trans
WHERE (((Trans.[Close-Date])=Date()-1) AND ((Trans.[LBR-TYPE])="CCB"));


select query 2
SELECT Sum(BSLaborDaily.[LABOR$]) AS [SumOfLABOR$], Sum(BSLaborDaily.[LBR-COST$]) AS [SumOfLBR-COST$]
FROM BSLaborDaily;
Hi!

Try to use the following SQL for the 2nd query

SELECT Nz(Sum(BSLaborDaily.[LABOR$]),0) AS [SumOfLABOR$], Nz(Sum(BSLaborDaily.[LBR-COST$]),0) AS [SumOfLBR-COST$]
FROM BSLaborDaily;
Aug 4 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.