Handling NULL in SQL SELECT statement

[maxflitom]

Tek-Tips,

Is there a way I can check for NULL value in a SQL SELECT statement and if NULL is TRUE then change that value from NULL to something usefull like 0.00?

For example:

SELECT Sum(Quanty * Price)AS ExtendedTotal FROM Events
...bla..bla...bla

If ExtendedTotal returns NULL, can I change ExtendedTotal to say another field value like Events.Price2 and have that be the value assigned to ExtendedTotal to be returned in the recordset?

Thank you for all your suggestions.

Tom (maxflitom)

 

[MarkSweetland]

In SQL Server you can:

SELECT isNull(Sum(Quanty * Price),0.0) AS ExtendedTotal FROM Events

Haven't tried in Access...



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[maxflitom]

I believe I found it. In SQL view in Access, I typed
SELECT Nz(Sum(Quanty * Price),'test') AS ExtendedTotal FROM Events. It seems like it works. I'll play around with it to see if I can get it to work properly.



Tom (maxflitom)

 

[maxflitom]

I worked it out I placed the code in VB and it works:

IIF(ISNULL(SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice + Events.SalesTax + Events.Grat11
+ Events.Grat7),
Events.MeetingRoomPrice + Events.SalesTax,
SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice + Events.SalesTax + Events.Grat11
+ Events.Grat7) AS GrandTotal,
IIF(ISNULL(SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice), Events.MeetingRoomPrice,
(SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice)) AS SubTotal


Tom (maxflitom)