Setting $0 for a month with no amount specified 1

[leicklda]

I have a line graph that charts Gross Profit $ earned each month. In some months, there was no GP reported, so technically the value would be "$0". But since nothing was entered for the $0 months, the value is just skipped over in the chart.

My data is like the following example:

DATE GP
10/3/03 $7,506
10/6/03 $4,321
10/30/03 $1,245
12/4/03 $5,634
12/17/03 $232

I am running a Totals query as the basis for my chart (using the datepart function for month and year) to get the following summary:

FirstofDATE SumofGP GroupbyDPmonth GroupbyDPyear
10/3/03 $13,072 10 03
12/4/03 $5,866 12 03

So the chart would show a line going from $13,072 in October to $5866 in December - but would show no mark at all in November. I want it to show $0 in November.

Basically I want to add a value like ...

First of DATE Sumof GP
11/1/03 $0 ....

to the query for every month with no data entered.

I thought about throwing dummy $0's in each month in the actual data table, or making a table with each month and year and doing a join query showing all values from the month/year table and only matching values from the GP table - then filling in null GP values with a 0. But both seem sloppy, long-winded ways of accomplishing this. I think the solution may lie somewhere in the DateSerial concept, but I'm not familiar with it.

Anyone have any suggestions???

 

[Frink]

Hallo,

I've come across this problem before and found the simplest way was to do either of your sloppy/long-winded solutions.
If only one or two historical values were missing but new ones would be ok, then a union query including the missing values explicitly would work, but wouldn't work in future.
You could write a bit of code to insert any missing values so that it was automated.

- Frink

 

[willir]

Use Nz() function.

=Nz(YourNumericVariable, 0)

You can event use it outside a function...

=Nz(DSum("[YourField]", "YourTable", "Month([YourDate]) = " & YourSelectedMonth), 0)

Here, I used three functions
- Nz
- DSum
- Month
...inside each other

Richard

 

[leicklda]

Thanks for the comments. I ended up just creating a new temp table and throwing all the relevant chart data in there, as well as one date for the first of each month. Then I run a totals on that temp table as a basis for my chart. Works really well. Here's the code if it can help anyone else:
Private Function SetSalesChart() As Integer
Dim dte As Date
Dim cnt As Integer
Dim tdf As DAO.Recordset
Dim tdfOpp As DAO.Recordset

Set tdf = CurrentDb.OpenRecordset("chttblSales")
Set tdfOpp = CurrentDb.OpenRecordset("Opportunities")
tdfOpp.Filter = "(Percentage >= 1) AND (Owner like '" & Me.SalesOwner & "') AND ([expected close] between date()-395 and date())"
Set tdfOpp = tdfOpp.OpenRecordset

DoCmd.SetWarnings (False)
DoCmd.RunSQL ("delete * from chttblsales")
DoCmd.SetWarnings (True)

Do Until tdfOpp.EOF
tdf.AddNew
tdf!Date = tdfOpp![expected close]
tdf!GP = tdfOpp![gross profit]
tdf.Update
tdfOpp.MoveNext
Loop

dte = Date - 395
For cnt = 0 To 12
dte = DateSerial(Year(dte), Month(dte) + 1, 1)
tdf.AddNew
tdf!Date = dte
tdf!GP = 0
tdf.Update
Next
Me.chtSales.Requery
End Function