Thank you for your reply. Please find my query below:
SELECT tblCustomer.strCustomerName, [lngAssetCode] & " " &
[strGroupDescription] AS Asset, tblJobs.strJobName, tblInvoices.InvoiceStart,
tblInvoices.InvoiceFinish, tblInvoices.InvoiceDays, tblInvoices.curUsageRate,
tblInvoices.curHireRate, tblInvoices.InvoiceHireAmount,
tblInvoices.StampDuty, tblInvoices.[Hours Worked],
tblInvoices.InvoicePeriodStart, tblInvoices.InvoicePeriodEnd,
([InvoiceHireAmount]+IIf(IsNull([UsagePayment]),0,[UsagePayment]))*[StampDuty]
AS StampDutyOnInvoice, tblInvoices.GST, [tblInvoices].[Hours
Worked]*[tblInvoices].[curUsageRate] AS UsagePayment,
[InvoiceHireAmount]+IIf(IsNull([UsagePayment]),0,[UsagePayment]) AS
SubTotalInvoice,
IIf(IsNull([gst]),0,[gst])*([SubTotalInvoice]+[StampDutyOnInvoice]) AS
GSTOnInvoice, [SubTotalInvoice]+[StampDutyOnInvoice]+[GSTOnInvoice] AS
TotalInvoice, tblAssetJobs.strPORecNum, tblAssetJobs.lngHireTypeID,
tblAssetJobs.curCostRate, tblInvoices.PaymentToOwner,
[InvoiceHireAmount]-[PaymentToOwner] AS BCSRevenue,
tblCustomer.strCustomerCode, tblCustomer.strCustomerAddress1,
tblCustomer.strCustomerAddress2, tblCustomer.strCustomerSuburb,
tblCustomer.strCustomerState, tblCustomer.strCustomerPostcode,
tblAssets.lngAssetCode, tblRateDuration.strInvoiceDuration,
tblUsers.strUserName, tblInvoices.AssetTask, tblAssetTaskTypes.SortOrder,
IIf(IsNull([tblassettasktypes.AssetTask]),True,[ShowInvoiceDur]) AS ShowDur,
tblInvoices.ParentWorkOrder, tblSites.SiteName,
IIf([tblInvoices.AssetTask]='Hire',[strAssetPWO],[ParentWorkOrder]) AS PWO,
tblAssetJobs.strAssetPWO, tblAssetJobs.strPCS_PONum,
IIf([tblInvoices.AssetTask]='Hire',[strPCS_PONum],'') AS APSHIRE,
tblSites.SiteName, tblJobs.strJobName
FROM (tblCustomer RIGHT JOIN ((tblUsers RIGHT JOIN tblJobs ON
tblUsers.lngUserID = tblJobs.lngUserID) LEFT JOIN tblSites ON
tblJobs.lngSiteID = tblSites.lngCustSiteID) ON tblCustomer.lngCustomerID =
tblJobs.lngCustomerID) RIGHT JOIN (tblAssetGroups RIGHT JOIN (((tblAssets
RIGHT JOIN (tblInvoices LEFT JOIN tblAssetJobs ON tblInvoices.lngAssetJobID =
tblAssetJobs.lngAssetJobID) ON tblAssets.AssetID = tblAssetJobs.AssetID) LEFT
JOIN tblRateDuration ON tblAssetJobs.lngInvoiceDurationID =
tblRateDuration.lngInvoiceDurationID) LEFT JOIN tblAssetTaskTypes ON
tblInvoices.AssetTask = tblAssetTaskTypes.AssetTask) ON
tblAssetGroups.lngAssetGroupID = tblAssets.lngAssetGroupID) ON
tblJobs.lngJobID = tblAssetJobs.lngJobID
ORDER BY tblCustomer.strCustomerName;
To give you few more details which may help. Uuser is entering data related
to the projects. For some projects no data is entered for some fields i.e.
Hours Worked and that is when records with some empty fields are generated. I
would like to eliminate them from report. I have tried using IIf statement in
my query and I had 0 shown in the TotalInvoice, but when I have tried to use
0 as a condition it is asking me for the elements used in calculation of the
TotalInvoice. Should I use IIf for the elements used in calculation before
entering calculation of
the TotalInvoice