Nulls to Zeros for graphing

[Guest]

I've already spent a good 3 hours or more reading posts and help topics and
trying everything I can think of to solve this. What I am trying to
accomplish is to graph DaysOld for 8 different backlogs. I have 8 queries to
accomplish the backlog reports and that is all fine. I added a DaysOld
expression in each query, "Date()-[criteriafield]" which lists days old for
each record in the backlog. I then created another query to pull all the
DaysOld fields into and average them. I works great when there are records
in each backlog, but if any of them have no backlog, then all fields are
blank. I cannot figure out how to replace a null with a zero. I've tried:
DaysOld: IIf([AwaitVendorDate] is null,0,Date()-[AwaitVendorDate])
but to no avail. Any ideas?
One other thing I need to accomplish is to round the average days. I can do
it with another query, but I should be able to do it in the same query I am
averaging in. Thoughts?
Thanks for your help!

 

[Guest]

Check out the NZ() function.

 

[Guest]

I already did. Tried it several different ways and in different places. I
still get a blank in that field, most likely since I have no record to NZ. I
don't know how to go about using NZ in the query that combines all of them.
Somehow, I need it to show zero for no record, else there is no way to graph
all backlogs or in this case any of them.

Check out the NZ() function.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I've already spent a good 3 hours or more reading posts and help topics and
trying everything I can think of to solve this. What I am trying to
accomplish is to graph DaysOld for 8 different backlogs. I have 8 queries to
accomplish the backlog reports and that is all fine. I added a DaysOld
expression in each query, "Date()-[criteriafield]" which lists days old for
each record in the backlog. I then created another query to pull all the
DaysOld fields into and average them. I works great when there are records
in each backlog, but if any of them have no backlog, then all fields are
blank. I cannot figure out how to replace a null with a zero. I've tried:
DaysOld: IIf([AwaitVendorDate] is null,0,Date()-[AwaitVendorDate])
but to no avail. Any ideas?
One other thing I need to accomplish is to round the average days. I can do
it with another query, but I should be able to do it in the same query I am
averaging in. Thoughts?
Thanks for your help!

 

[Guest]

Aha! I'm getting a clue. The problem is that you are probably using a left
join and not getting all the records that you need. Please post a sample of
the SQL for your queries and maybe we can show you a trick or two to present
zeros when there are no records.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I already did. Tried it several different ways and in different places. I
still get a blank in that field, most likely since I have no record to NZ. I
don't know how to go about using NZ in the query that combines all of them.
Somehow, I need it to show zero for no record, else there is no way to graph
all backlogs or in this case any of them.

Check out the NZ() function.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I've already spent a good 3 hours or more reading posts and help topics and
trying everything I can think of to solve this. What I am trying to
accomplish is to graph DaysOld for 8 different backlogs. I have 8 queries to
accomplish the backlog reports and that is all fine. I added a DaysOld
expression in each query, "Date()-[criteriafield]" which lists days old for
each record in the backlog. I then created another query to pull all the
DaysOld fields into and average them. I works great when there are records
in each backlog, but if any of them have no backlog, then all fields are
blank. I cannot figure out how to replace a null with a zero. I've tried:
DaysOld: IIf([AwaitVendorDate] is null,0,Date()-[AwaitVendorDate])
but to no avail. Any ideas?
One other thing I need to accomplish is to round the average days. I can do
it with another query, but I should be able to do it in the same query I am
averaging in. Thoughts?
Thanks for your help!

 

[Guest]

Here you go, Jerry. This is one of my 8 queries:

SELECT Table1.QNumber, Table1.QDate, Table1.RMANumber, Table1.RxDate,
Table1.TechUPDDate, Table1.AwaitingVendorRepair, Table1.CatNumber,
Table1.AwaitVendorDate, NZ(Date()-[AwaitVendorDate]) AS DaysOldVendor
FROM Table1
WHERE (((Table1.QDate) Is Not Null) AND ((Table1.RxDate) Is Not Null) AND
((Table1.TechUPDDate) Is Null) AND ((Table1.AwaitingVendorRepair)=Yes));

This is the query to pull all the data for the chart, with only the first
four queries:

SELECT Avg(QueueQDate.DaysOld) AS AvgOfDaysOld, Avg(QueueTech.LowestDaysOld)
AS AvgOfLowestDaysOld, Avg(QueueCS.DaysOldCS) AS AvgOfDaysOldCS,
Avg(QueueAwaitVendor.DaysOldVendor) AS AvgOfDaysOldVendor
FROM QueueQDate, QueueTech, QueueCS, QueueAwaitVendor;

Thanks for your help!

Aha! I'm getting a clue. The problem is that you are probably using a left
join and not getting all the records that you need. Please post a sample of
the SQL for your queries and maybe we can show you a trick or two to present
zeros when there are no records.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I already did. Tried it several different ways and in different places. I
still get a blank in that field, most likely since I have no record to NZ. I
don't know how to go about using NZ in the query that combines all of them.
Somehow, I need it to show zero for no record, else there is no way to graph
all backlogs or in this case any of them.

Check out the NZ() function.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I've already spent a good 3 hours or more reading posts and help topics and
trying everything I can think of to solve this. What I am trying to
accomplish is to graph DaysOld for 8 different backlogs. I have 8 queries to
accomplish the backlog reports and that is all fine. I added a DaysOld
expression in each query, "Date()-[criteriafield]" which lists days old for
each record in the backlog. I then created another query to pull all the
DaysOld fields into and average them. I works great when there are records
in each backlog, but if any of them have no backlog, then all fields are
blank. I cannot figure out how to replace a null with a zero. I've tried:
DaysOld: IIf([AwaitVendorDate] is null,0,Date()-[AwaitVendorDate])
but to no avail. Any ideas?
One other thing I need to accomplish is to round the average days. I can do
it with another query, but I should be able to do it in the same query I am
averaging in. Thoughts?
Thanks for your help!

 

[Guest]

Hey Jerry,

I am backing up a step on this idea as it takes forever to run the query
from 8 separate queries. Also, I have 8 backlogs I was wanting to graph, but
Access will only allow 6. I would still like to know how to create the zero
from no record as I may try to create my chart using Crystal Reports later.
Any other ideas of how I might start from scratch to end up with my final
results would be much appreciated. I know that in a "Normalized" DB, I
should not have multiple date fields, but I cannot find any other way to
track how long something has been in a particular queue. We technically have
a 15 day turnaround time with the returns and I need to be able to see who is
holding up the show through 8 different areas. I also have to be able to
lock the date in place once it shows to keep them from fudging results by
reclicking the same checkbox and updating the date to today and end up zero
days old. Just a little background on why I'm doing it this way. Though
I've built several DBs, I am still a little wet behind the ears and greatly
appreciate any wisdom from the folks on this site! Thanks again!

Here you go, Jerry. This is one of my 8 queries:

SELECT Table1.QNumber, Table1.QDate, Table1.RMANumber, Table1.RxDate,
Table1.TechUPDDate, Table1.AwaitingVendorRepair, Table1.CatNumber,
Table1.AwaitVendorDate, NZ(Date()-[AwaitVendorDate]) AS DaysOldVendor
FROM Table1
WHERE (((Table1.QDate) Is Not Null) AND ((Table1.RxDate) Is Not Null) AND
((Table1.TechUPDDate) Is Null) AND ((Table1.AwaitingVendorRepair)=Yes));

This is the query to pull all the data for the chart, with only the first
four queries:

SELECT Avg(QueueQDate.DaysOld) AS AvgOfDaysOld, Avg(QueueTech.LowestDaysOld)
AS AvgOfLowestDaysOld, Avg(QueueCS.DaysOldCS) AS AvgOfDaysOldCS,
Avg(QueueAwaitVendor.DaysOldVendor) AS AvgOfDaysOldVendor
FROM QueueQDate, QueueTech, QueueCS, QueueAwaitVendor;

Thanks for your help!

Aha! I'm getting a clue. The problem is that you are probably using a left
join and not getting all the records that you need. Please post a sample of
the SQL for your queries and maybe we can show you a trick or two to present
zeros when there are no records.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I already did. Tried it several different ways and in different places. I
still get a blank in that field, most likely since I have no record to NZ. I
don't know how to go about using NZ in the query that combines all of them.
Somehow, I need it to show zero for no record, else there is no way to graph
all backlogs or in this case any of them.

:

Check out the NZ() function.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I've already spent a good 3 hours or more reading posts and help topics and
trying everything I can think of to solve this. What I am trying to
accomplish is to graph DaysOld for 8 different backlogs. I have 8 queries to
accomplish the backlog reports and that is all fine. I added a DaysOld
expression in each query, "Date()-[criteriafield]" which lists days old for
each record in the backlog. I then created another query to pull all the
DaysOld fields into and average them. I works great when there are records
in each backlog, but if any of them have no backlog, then all fields are
blank. I cannot figure out how to replace a null with a zero. I've tried:
DaysOld: IIf([AwaitVendorDate] is null,0,Date()-[AwaitVendorDate])
but to no avail. Any ideas?
One other thing I need to accomplish is to round the average days. I can do
it with another query, but I should be able to do it in the same query I am
averaging in. Thoughts?
Thanks for your help!