Nonexistent values: how do I set a value to zero if there is no record.

[ConfusedA]

My problem:

I have a set of reports that are created by a lot of queries. These reports collect together a bunch of information to create a totals section, my current problem is that there will not always be values for every field for each of the specific ways in which data needs to be connected (for example, 3 of my main categories are: full-time labour, part-time labour, and contract labour) If I have an expression to total all the labour on a certain job it would like like [fulltimelabour] + [parttimelabour] + [Contractlabour]. If any of the values does not contain a record, my totals bar will remain empty on both my query and my report. Is there a way to set these values to zero if there is not a number associated with them?

Thanks ahead of time,
ConfusedA

 

[pbaldy]

Try

Nz([fulltimelabour], 0) + Nz([parttimelabour], 0) + Nz([Contractlabour], 0)

 

[ConfusedA]

Thanks for the extremely quick response.
I attempted that and I'm still not getting a value.

I believe that the problem lies with the lack of records, NZ only works when there is an empty field doesn't it? I'm trying to account for situations in which the record doesn't exist at all.

Thanks again for responding so quickly and for the idea, I'm trying to manipulate it to see if I can work with it. I'm not sure if what I am asking is even possible, but I feel like it has to be. There must be lots of situations in which certain records do not exist after select queries but still are required to show as a 0 for calculation purposes.

-Confused

 

[pbaldy]

This type of thing?

http://www.mvps.org/access/forms/frm0022.htm

You didn't say you were getting an error, so I didn't consider it. Maybe you can post the db, or a sample of it?

 

[ConfusedA]

Well it's too big to post and there are a lot of small connections that allow it to run, so it would be difficult to post just a piece of it. I can't manage to get my printscreen pictures up on here for some reason either, but I'll try to explain it from the overall picture to see if that can help. This is probably about as muddled as they come so if you don't understand what I'm talking about let me know.

The overall goal of my project is to be able to calculate expenses on a project in a given month: so I have 3 main sets of values: Labour (split similarly to my example above, but all that is needed from this is: hours worked, and cost), Truck work (only needs hours and cost as well), and inventory spent (only requires cost).

I have a query that is designed to add up the totals for each of these 3 sub-categories and is then supposed to be shown in a sub-report. But If any of the values are lacking (for example if no inventory is used on a specific job) then the total value in the final totals of the report remain empty.

My example...
County Plowing.
If there are 7 hours recorded for an employee using a truck to plow county roads February. The output should show: the number of hours the employee worked, the cost of having the truck out (on an hourly basis) and the cost of the employee's wage. If they do not use any inventory in this job (as they wouldn't have to when plowing the road) then the total hours and expenses are not recorded. So I'm not getting an error, it's just that there isnt any data for the inventory for that job in that period, and as a result theres nothing to be calculated. It's closer to the link you posted, but I don't really understand how that solution works.

Thanks again.
A

 

[ConfusedA]

Oh...and inventory, truck work and employee hours are all input seperately so they do not necessarily all appear for each job, because each has its own separate table that is used for calculating certain things.

 

[ConfusedA]

I'm trying to decide if the easiest way to do this is just to place a null value for every possible option in to my tables and queries, but the problem is i will have to go through like 60 queries to ensure that it can recognize a null value when needed without wrecking any calculations.

 

[DCrake]

What do you mean each has its own separate table that is used for calculating certain things. Do you actually mean queries?

David

 

[ConfusedA]

I just mean like i have multiple tables of data; 3 main ones (one for employee work, one for when trucks are used and one for when inventory is used) they share 'job type' information as well as date information. Each is input in a different form, and not all jobs will include all three types (Some jobs may just have an employee working without a truck or recorded inventory) but my final calculation is based off adding the employee-cost + truck-cost + inventory-cost.

And currently if it's lacking one (say no inventory is used):
employee-cost + truck-cost + <blank> = <blank>
But I want it to be:
employee-cost + truck-cost + 0 = whatever that sum would be
without including every possibility where I could have a <blank result> as a table entry with a 0 (which is my current thought on how to ensure that I don't have blanks)

 

[ConfusedA]

Essentially what I need is an NZ() function that will work when a record doesn't exist from a query.

Employee-cost, Truck-cost, and inventory-cost are all calculated in separate queries and sorted in to a job and 'month/year' basis. If there isn't a record for a cost associated with the month/year or job that is being searched for, I need a 0 to be created. My other solution is just to ensure that theres a record for each possible month/year and any other specific value that is calculated by.

Is the primary solution I am after possible? I know if i try:
NZ(employee-cost) + NZ(truck-cost) + NZ (inventory-cost) or even
NZ([employee-cost], 0) + NZ([truck-cost], 0) + NZ ([inventory-cost], 0)
I do not get any value reported to me.

 

[ConfusedA]

Ok, so I have an idea for a solution, but I need to know if this is possible. If I have a value, is there a way to associate it with a set of values (sort of like a wildcard, but limited to a set of values given to it) I.E. for "Job" i would want it to be able to take up any possible used value of jobs within the database. For example...I create a table with one long record, all the numerical values are 0, and all the required values would have to be able to circulate through all their possible options to create the one relevant to the other information.

So if i had a field Job: with a value (could be plowing, shovelling or salting)
When I do a search for plowing, it would represent plowing, but if i did a searh for shovelling it would represent that as well.