putting a 0 when something is null (empty)

[GoneAccessCrazy]

Hi, I dunno if this thing is possible...

I first have 2 queries and their results...

****QueryRent:
SELECT Cliente.IdCliente, Cliente.Nombre, Cliente.ApePat, Cliente.ApeMat, Sum(Alquiler.MontoTPagado) AS MontoPagadoA, Cliente.MontoDeuda
FROM Cliente INNER JOIN Alquiler ON Cliente.IdCliente = Alquiler.IdCliente
GROUP BY Cliente.IdCliente, Cliente.Nombre, Cliente.ApePat, Cliente.ApeMat, Cliente.MontoDeuda;

IdCliente Nombre ApePat ApeMat MontoPagadoA MontoDeuda
1 Wendy Villanueva Cabanas 250 0
2 Evan Tapia Guiulfo 100 512.23



****QuerySale:
SELECT Cliente.IdCliente, Cliente.Nombre, Cliente.ApePat, Cliente.ApeMat, Sum([PrecioVenta]*[Cantidad]) AS MontoPagadoV
FROM Cliente INNER JOIN Venta ON Cliente.IdCliente = Venta.IdCliente
GROUP BY Cliente.IdCliente, Cliente.Nombre, Cliente.ApePat, Cliente.ApeMat;

IdCliente Nombre ApePat ApeMat MontoPagadoV
1 Wendy Villanueva Cabanas 261


and then I make a third query using both previous queries

QueryTot:

SELECT ConsAlq.IdCliente, [ConsAlq.Nombre]+' '+[ConsAlq.ApePat]+' '+[ConsAlq.ApeMat] AS Nombre, ConsAlq.MontoPagadoA, ConsVen.MontoPagadoV, [MontoPagadoA]+[MontoPagadoV] AS Total
FROM ConsAlq LEFT JOIN ConsVen ON ConsAlq.IdCliente = ConsVen.IdCliente;

IdCliente Nombre MontoPagadoA MontoPagadoV Total
1 Wendy Villanueva Cabanas 250 261 511
2 Evan Tapia Guiulfo 100

but as you can see I need to put somehow a 0 in the column of MontoPagadoV for the client 2 so I can add both MontoPagadoA + MontoPagadoV and the Total be a 100

Anybody knows how? Maybe I have to change my queries?

Thanks in advance,
GAC

 

[jsolutions]

GAC - yes this can be done. You need to convert the values being summed before you sum them.

So, instead of Sum(Alquiler.MontoTPagado), you will need to use sum(iif((isnull(Alquiler.MontoTpagodo)), 0, Alquiler.MontoTPagodo))
What this does is replaces MontoTPagodo with 0 when MottoTpagodo is null. If all of the values in MontoTPagodo are null, the sum will be zero.

You can do this for any values that potentially have a null in them. Also, you might want to set your default value for these fields to 0 to eliminate the problem completely.

Let me know if you have any questions.

Jay

 

[SBendBuckeye]

There is also a function called nz (NullZero) which returns zero or an empty string depending on the data type.

Good Luck!

 

[Guest_imported]

Good catch SBend. I totally forgot about nz replacing nulls with zeros.

GAC - SBendBuckeye's solution with nz will be easier to type than the if statement - less chance of parentheses errors too.

Jay

 

[GoneAccessCrazy]

Hey, thanks a lot!!!!
...
(not crazy anymore, at least for now ;D)

GAC

 

[rookiedev]

I am having a similar problem I have a calculated field that adds an exsisting value of "Sold" items to a field called "Total Sold" The value sold has always been a field that I manually entered. If I had 2 sold and then sold 2 more I would manually change the number to 4 in the "Sold" field. I am attempting to link the Inventory Program with Sign Out program that tells us how many items are being taken out of our inventory. Up until recently the Sign Out Program was on a stand-alone computer. Now that it is on the network I am attempting to have this information automatically deduct the # of items used (or "Sold&quot Everyting would work beautiful if the Sign Out Program was some how linked to the Transactions that occure each time we purchase a product. Then I could use the Products Form as my Main Form then Products Subform for the Transaction and of course the sign out would have a one-to-many relationship with the Products Subform and that would be the perfect setup according to Microsoft....This however is not the case...I have a one-to-many relationship with the Products Table only....

Each Product will have many purchase Transactions...and....
Each Product will have many Sign Out records.

So as you can see Microsofts solution will not work. In an attempt somehow use the data from the Signout Program.....I created a subform for the Sign Out records...I have a calculated field to Sum the Quantity.......Then on the Products Form I placed the Total Sold field that has this control source-=nz([SignoutDetails].[Form]![TotalSold]). It works fine if there are Sign Out records for a product but if there are not the field displays..#Error. I took your advice and set the default value to zero but that doesn't seem to do anything at all......Can anyone help me with this???????? I'm at my wits end

Thanks-
RookieDev

 

[cmmrfrds]

This will catch nulls but not empty or spaces. if you put the 0 in the nz function.
=nz([SignoutDetails].[Form]![TotalSold],0)

 

[rookiedev]

cmmrfrds,
The courious thing about this is that...if you look at the Northwinds Inventory Control db (which I used as a basis for my program) The Units On Hand field is just blank if there aren't any transactions. Yet another thing I noticed is that there will be a blank record showing in the Products Subform....I do not get this blank record in my sign out subform....All the setting on the subforms seem to match except for the fact that the Products form requeries the Products Sub form on On Form Activate. Here is the Event:

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me![Products Subform].Requery

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
If Err <> 2279 Then
MsgBox Err.Description
End If
Resume Exit_Form_Activate
End Sub

Is there some way to modify this event to also requery the Sign Out Details subform? I was just hoping!!!!!!! Maybe that is what's making the difference.
Thanks for your help and your quick response.

RookieDev

 

[cmmrfrds]

I took a quick look at northwinds but could not find the code you posted, but you have the syntax for the requery. Did you try what you suggested?

Me![Products Subform].Requery
Me![Sign out details Subform].Requery