condition in queary

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have report based on the following query:

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;

It is working fine but it is giving me records with empty TotalInvoice I
would like to eliminate. I am trying to use all different variations of the
condition but nothing works for me. How I can modify this query to eliminate
those empty records.

Thanks for help.

Tony
 
Tony

Nulls propagate when you try to do math on them. It appears you are trying
to add fields together to get TotalInvoice. If any of the components are
null, you get null.

What's in your data?

Good luck

Jeff Boyce
<Access MVP>

Tony said:
I have report based on the following query:

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]))*[StampDut
y]
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;

It is working fine but it is giving me records with empty TotalInvoice I
would like to eliminate. I am trying to use all different variations of the
condition but nothing works for me. How I can modify this query to eliminate
those empty records.

Thanks for help.

Tony
Click to expand...
 
Hi Jeff,

User is entering data related to the projects. For some projects no data is
entered and that is when empty records are generated. I would like to
eliminate them from report. I have tried using IIf statement 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 ? Any other suggestions ???

Thanks for help.

Tony

Jeff Boyce said:
Tony

Nulls propagate when you try to do math on them. It appears you are trying
to add fields together to get TotalInvoice. If any of the components are
null, you get null.

What's in your data?

Good luck

Jeff Boyce
<Access MVP>

Tony said:
I have report based on the following query:

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]))*[StampDut
y]
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;

It is working fine but it is giving me records with empty TotalInvoice I
would like to eliminate. I am trying to use all different variations of the
condition but nothing works for me. How I can modify this query to eliminate
those empty records.

Thanks for help.

Tony
Click to expand...
Click to expand...
 
Tony

Take a look at the Nz() function. This allows you to convert a null to,
say, zero, so you can still "use" the field in a calculation without complex
IIF() calculations.

Good luck

Jeff Boyce
<Access MVP>

Tony said:
Hi Jeff,

User is entering data related to the projects. For some projects no data is
entered and that is when empty records are generated. I would like to
eliminate them from report. I have tried using IIf statement 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 ? Any other suggestions ???

Thanks for help.

Tony

Jeff Boyce said:
Tony

Nulls propagate when you try to do math on them. It appears you are trying
to add fields together to get TotalInvoice. If any of the components are
null, you get null.

What's in your data?

Good luck

Jeff Boyce
<Access MVP>

Tony said:
I have report based on the following query:

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,
Click to expand...
([InvoiceHireAmount]+IIf(IsNull([UsagePayment]),0,[UsagePayment]))*[StampDut
y]
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 =
Click to expand...
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;

It is working fine but it is giving me records with empty TotalInvoice I
would like to eliminate. I am trying to use all different variations
Click to expand...
of
the
condition but nothing works for me. How I can modify this query to eliminate
those empty records.

Thanks for help.

Tony
Click to expand...
Click to expand...
Click to expand...
 
Back
Top