Adding null Cell in Query Mode

[Juan]

Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[Guest]

Any calculation that has a Null in any of its elements returns a Null. Wrap
each element in your calculations in the Nz function.

(Nz([price], 0) * Nz([Volume], 0)) + Nz([Adjustments],0)

The version below will not work:
Nz(([price] * [Volume]) + [Adjustments],0)
That is because the calculations are taking place before the Nz is applied
and any Null values inside the Nz have not be converted.

 

[Juan]

Ok, I think this might help some more.

I have one table with just adjustments.

I started creating a query with titles like this:
ID# From Copier Table
DOC From Copier Table
# From FOAPAL Table
Fund From FOAPAL Table
ORG From FOAPAL Table
Prog From FOAPAL Table
Volume From Monthly Table
Per Click From Copier Table
Click % From COpier Table
Click [Per Click]*[Click%]*[Volume]
Per Base From Copier Table
Base % % From COpier Table
Base [Per Base]*[Base%]
Billed [Base]+[Click]
Adj. From Adjustment Table <------ Outer Join
Total {Billed]+[Adj.] <---- I am stuck here


Now the Adjustment table might have only 3 adjustment per month. So i
did an outer join so now the adjustment column shows with the amount
next to the corresponding copier. But the thing is that the copier with
no adjustment have a empty cell next to them under the Adjustment
Column.

So when I want to add across the Billed and Adj. I get blank cells
under the Adj. and the Total Column for the copiers that have no
adjustments for the month but not the ones with the Adjustments.

Where do I put that Nz function?

I hope this helps.

Any calculation that has a Null in any of its elements returns a Null. Wrap
each element in your calculations in the Nz function.

(Nz([price], 0) * Nz([Volume], 0)) + Nz([Adjustments],0)

The version below will not work:
Nz(([price] * [Volume]) + [Adjustments],0)
That is because the calculations are taking place before the Nz is applied
and any Null values inside the Nz have not be converted.

Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[John Spencer]

Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)

 

[Guest]

Put the Nz around every field you use in any calculation.
Look up the Nz function in VBA Help.
And, there are no Cells in Access. There are fields, Cells is Excel.

Ok, I think this might help some more.

I have one table with just adjustments.

I started creating a query with titles like this:
ID# From Copier Table
DOC From Copier Table
# From FOAPAL Table
Fund From FOAPAL Table
ORG From FOAPAL Table
Prog From FOAPAL Table
Volume From Monthly Table
Per Click From Copier Table
Click % From COpier Table
Click [Per Click]*[Click%]*[Volume]
Per Base From Copier Table
Base % % From COpier Table
Base [Per Base]*[Base%]
Billed [Base]+[Click]
Adj. From Adjustment Table <------ Outer Join
Total {Billed]+[Adj.] <---- I am stuck here


Now the Adjustment table might have only 3 adjustment per month. So i
did an outer join so now the adjustment column shows with the amount
next to the corresponding copier. But the thing is that the copier with
no adjustment have a empty cell next to them under the Adjustment
Column.

So when I want to add across the Billed and Adj. I get blank cells
under the Adj. and the Total Column for the copiers that have no
adjustments for the month but not the ones with the Adjustments.

Where do I put that Nz function?

I hope this helps.

Any calculation that has a Null in any of its elements returns a Null. Wrap
each element in your calculations in the Nz function.

(Nz([price], 0) * Nz([Volume], 0)) + Nz([Adjustments],0)

The version below will not work:
Nz(([price] * [Volume]) + [Adjustments],0)
That is because the calculations are taking place before the Nz is applied
and any Null values inside the Nz have not be converted.

Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[Juan]

So I would put this function under my "Total" column and under the
criteria, or under the expression?

Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)

Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[Juan]

I dont think tha Access is recognzing any null fields, given that in my
query, the values for Adjustments are those from the table "Adjustment"
that only have adjustments and values and no null fields.

So even if I put in the crtieria of Adjustment, Is Null and update the
query with 0, it is only reading the table that has no Null fields.

Put the Nz around every field you use in any calculation.
Look up the Nz function in VBA Help.
And, there are no Cells in Access. There are fields, Cells is Excel.

Ok, I think this might help some more.

I have one table with just adjustments.

I started creating a query with titles like this:
ID# From Copier Table
DOC From Copier Table
# From FOAPAL Table
Fund From FOAPAL Table
ORG From FOAPAL Table
Prog From FOAPAL Table
Volume From Monthly Table
Per Click From Copier Table
Click % From COpier Table
Click [Per Click]*[Click%]*[Volume]
Per Base From Copier Table
Base % % From COpier Table
Base [Per Base]*[Base%]
Billed [Base]+[Click]
Adj. From Adjustment Table <------ Outer Join
Total {Billed]+[Adj.] <---- I am stuck here


Now the Adjustment table might have only 3 adjustment per month. So i
did an outer join so now the adjustment column shows with the amount
next to the corresponding copier. But the thing is that the copier with
no adjustment have a empty cell next to them under the Adjustment
Column.

So when I want to add across the Billed and Adj. I get blank cells
under the Adj. and the Total Column for the copiers that have no
adjustments for the month but not the ones with the Adjustments.

Where do I put that Nz function?

I hope this helps.

Any calculation that has a Null in any of its elements returns a Null. Wrap
each element in your calculations in the Nz function.

(Nz([price], 0) * Nz([Volume], 0)) + Nz([Adjustments],0)

The version below will not work:
Nz(([price] * [Volume]) + [Adjustments],0)
That is because the calculations are taking place before the Nz is applied
and any Null values inside the Nz have not be converted.


:

Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[John Spencer]

I would guess that you would replace your current expression with something
that looks like what was posted.

If you can't figure it out, perhaps you can post the SQL of your current
query.
Select View:SQL and copy the the text
Paste the text into your message and post it.

So I would put this function under my "Total" column and under the
criteria, or under the expression?

Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)

Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[Juan]

SELECT Copiers.[ID#], [FOAPAL Numbers].[DOC #], [FOAPAL Numbers].[#],
[FOAPAL Numbers].FUND, [FOAPAL Numbers].ORG, [FOAPAL Numbers].PROG,
[FOAPAL Numbers].ACCT, [Recount Volume June 2006].[Total Montly
Volume], [Billing Table].[Customer Click Charge], [FOAPAL
Numbers].[Percent of Maintenance], [Billing Table]![Customer Click
Charge]*[Recount Volume June 2006]![Total Montly Volume]*[FOAPAL
Numbers]![Percent of Maintenance] AS [Total Click Charge], [Billing
Table].[Customer Base Charge], [FOAPAL Numbers].[Percent of Base],
[Billing Table]![Customer Base Charge]*[FOAPAL Numbers]![Percent of
Base] AS [Total Customer Base], [Total Customer Base]+[Total Click
Charge] AS [Total Billed], [Adjustments for June 2006].Amount


FROM [Document Number] INNER JOIN ([Adjustments for June 2006] RIGHT
JOIN ([Recount Volume June 2006] INNER JOIN (([Billing Table] INNER
JOIN Copiers ON [Billing Table].[ID#] = Copiers.[ID#]) INNER JOIN
[FOAPAL Numbers] ON ([Billing Table].[ID#] = [FOAPAL Numbers].[ID#])
AND (Copiers.[ID#] = [FOAPAL Numbers].[ID#])) ON [Recount Volume June
2006].[Equip ID] = [FOAPAL Numbers].[ID#]) ON [Adjustments for June
2006].ID = Copiers.[ID#]) ON [Document Number].[DOC #] = [FOAPAL
Numbers].[DOC #];

I would guess that you would replace your current expression with something
that looks like what was posted.

If you can't figure it out, perhaps you can post the SQL of your current
query.
Select View:SQL and copy the the text
Paste the text into your message and post it.

So I would put this function under my "Total" column and under the
criteria, or under the expression?

Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)


Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[Juan]

Sorry I put everything from the SQL

Here is the last three column which I am trying to work with


[Total Customer Base]+[Total Click Charge] AS [Total Billed],
[Adjustment Table].Amount,
[Total Billed]+[Amount] AS Banner

This is a quick sample of what I get

Total Billed Amount Banner
212.48
11.55
310.90 140.80 451.70

I would guess that you would replace your current expression with something
that looks like what was posted.

If you can't figure it out, perhaps you can post the SQL of your current
query.
Select View:SQL and copy the the text
Paste the text into your message and post it.

So I would put this function under my "Total" column and under the
criteria, or under the expression?

Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)


Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[Juan]

Sorry I put everything from the SQL

Here is the last three column which I am trying to work with


[Total Customer Base]+[Total Click Charge] AS [Total Billed],
[Adjustment Table].Amount,
[Total Billed]+[Amount] AS Banner

This is a quick sample of what I get

Total Billed Amount Banner
212.48
11.55
310.90 140.80 451.70

And when I put the NZ function in first of Amount I get an error of
Complie Error

I would guess that you would replace your current expression with something
that looks like what was posted.

If you can't figure it out, perhaps you can post the SQL of your current
query.
Select View:SQL and copy the the text
Paste the text into your message and post it.

So I would put this function under my "Total" column and under the
criteria, or under the expression?

Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)


Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[Juan]

Sorry I put everything from the SQL

Here is the last three column which I am trying to work with


[Total Customer Base]+[Total Click Charge] AS [Total Billed],
[Adjustment Table].Amount,
[Total Billed]+[Amount] AS Banner


This is a quick sample of what I get


Total Billed Amount Banner
212.48
11.55
310.90 140.80 451.70


And when I put the NZ function in first of Amount I get an error of
Compile Error

I would guess that you would replace your current expression with something
that looks like what was posted.

If you can't figure it out, perhaps you can post the SQL of your current
query.
Select View:SQL and copy the the text
Paste the text into your message and post it.

So I would put this function under my "Total" column and under the
criteria, or under the expression?

Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)


Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan

 

[John Spencer]

You might try something like the following.

SELECT Copiers.[ID#]
, [FOAPAL Numbers].[DOC #]
, [FOAPAL Numbers].[#]
, [FOAPAL Numbers].FUND
, [FOAPAL Numbers].ORG
, [FOAPAL Numbers].PROG
, [FOAPAL Numbers].ACCT
, [Recount Volume June 2006].[Total Montly Volume]
, [Billing Table].[Customer Click Charge]
, [FOAPAL Numbers].[Percent of Maintenance]
, Nz([Billing Table]![Customer Click Charge]
*[Recount Volume June 2006]![Total Montly Volume]
*[FOAPAL Numbers]![Percent of Maintenance],0) AS [Total Click Charge]
, [Billing Table].[Customer Base Charge]
, [FOAPAL Numbers].[Percent of Base]
, Nz([Billing Table]![Customer Base Charge]
*[FOAPAL Numbers]![Percent of Base],0) AS [Total Customer Base]
, [Total Customer Base]+[Total Click Charge] AS [Total Billed]
, [Adjustments for June 2006].Amount

FROM [Document Number] INNER JOIN ([Adjustments for June 2006] RIGHT
JOIN ([Recount Volume June 2006] INNER JOIN (([Billing Table] INNER
JOIN Copiers ON [Billing Table].[ID#] = Copiers.[ID#]) INNER JOIN
[FOAPAL Numbers] ON ([Billing Table].[ID#] = [FOAPAL Numbers].[ID#])
AND (Copiers.[ID#] = [FOAPAL Numbers].[ID#])) ON [Recount Volume June
2006].[Equip ID] = [FOAPAL Numbers].[ID#]) ON [Adjustments for June
2006].ID = Copiers.[ID#]) ON [Document Number].[DOC #] = [FOAPAL
Numbers].[DOC #];

Sorry I put everything from the SQL

Here is the last three column which I am trying to work with

[Total Customer Base]+[Total Click Charge] AS [Total Billed],
[Adjustment Table].Amount,
[Total Billed]+[Amount] AS Banner

This is a quick sample of what I get

Total Billed Amount Banner
212.48
11.55
310.90 140.80 451.70

And when I put the NZ function in first of Amount I get an error of
Compile Error

I would guess that you would replace your current expression with something
that looks like what was posted.

If you can't figure it out, perhaps you can post the SQL of your current
query.
Select View:SQL and copy the the text
Paste the text into your message and post it.

So I would put this function under my "Total" column and under the
criteria, or under the expression?


John Spencer wrote:
Use the NZ function on the adjustment in the calculation to force a zero
value for nulls.

[Total Amount] + NZ([Adjustments],0)


Hi,
I had a quick question about queries.

I have a query going right now that gives me results that are
calculated from other cell (i.e. Volume*price= Total Amount). Now once
in a while I have some adjustment to the final number (Total Amount).
Now I have a table on the side that has the adjustment on it. I did an
outer join link, so now I have two columns (Total Amount and
Adjustments). I want to add across to get the final number but Access
only adds across for those rows that have an adjustment not the other
ones. How do I get that? or at least have Access read the null value in
"Adjustment" column as a zero so it would add across. Thanks.

Juan