Access 2002 SQL Select Statement with NULL Fields

[maxflitom]

Hello Tek-Tips. I left this same question in the VB Database forum and got some feedback but I still cannot resolve my dilema.

I have the following SQL statement on an Access database. The only issue I have is when there is no entry in the EventDetail Table matching the Events.EventID field, the aggregate functions (GrandTotal & SubTotal) return a null even though there is an entry in the MeetingRoomPrice field. The GrandTotal & SubTotal fields return a value as long as there is a matching EventDetail.EventID to the Events.EventID field. I tried creating another field name and adding the two together but when I add a number to a NULL field it just returns NULL.

Is there a way I can use an IF statement in the SELECT statement to test for a NULL and replace it with another field name that I create?

I am all out ideas. Any suggestions will be greatly appreciated.

p.s. When I attempt to use RIGHT JOIN I get an access error stating that this JOIN type is not supported.

Tom

SELECT Customers.CustomerName, Customers.ContactName,
Customers.Address1, Customers.Address2,
Customers.Phone1, Customers.Phone2, Customers.Fax1,
Customers.eMail, Customers.City & ",
" & States.StateShort & " " & Customers.Zip AS CityStateZip,
Events.EventName, Events.EventID, Events.BookingDate,
Events.EventDate, Events.EventStartTime,
Events.EventEndTime, Events.GTD,
Events.MeetingRoomPrice, Events.OnSiteContact,
Events.PaymentDetail, Events.CCExpDate, Events.Notes,
Events.SalesTax, MeetingRooms.MeetingRoomName,
Events.Grat11 + Events.Grat7 AS ServiceCharge,
SetupTypes.SetupType, PaymentTypes.PaymentType,
SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice + Events.SalesTax + Events.Grat11
+ Events.Grat7 AS GrandTotal,
SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice AS SubTotal
FROM ((((((Events INNER JOIN
Customers ON Events.CustomerID = Customers.CustomerID)
INNER JOIN
Employees ON
Events.EmployeeID = Employees.EmployeeID) INNER JOIN
MeetingRooms ON
Events.MeetingRoomID = MeetingRooms.MeetingRoomID)
INNER JOIN
SetupTypes ON
SetupTypes.SetupTypeID = Events.SetupTypeID) INNER JOIN
PaymentTypes ON
PaymentTypes.PaymentTypeID = Events.PaymentTypeID)
INNER JOIN
States ON States.StateID = Customers.StateID) LEFT JOIN
EventDetail ON EventDetail.EventID = Events.EventID
WHERE Events.EventID = ?
GROUP BY Customers.CustomerName, Customers.ContactName,
Customers.Address1, Customers.Address2,
Customers.Phone1, Customers.Phone2, Customers.Fax1,
Customers.eMail, Customers.City, Customers.Zip,
Events.EventDate, Events.EventName, Events.EventID,
Events.BookingDate, Events.EventDate,
Events.EventStartTime, Events.EventEndTime, Events.GTD,
Events.MeetingRoomPrice, Events.OnSiteContact,
Events.PaymentDetail, Events.CCExpDate, Events.Notes,
Events.SalesTax, MeetingRooms.MeetingRoomName,
Events.Grat11, Events.Grat7, SetupTypes.SetupType,
PaymentTypes.PaymentType, States.StateShort

 

[Eupher]

Have you looked at the Nz() function? It will test for a null value and replace it with any value of your choosing (usually 0).

Ken S.