Hiding details

[Guest]

I have report based on the query with some calculated fields. For some
records there is no data entered and as a result calculated record do not
have data (other fields of the record have still have data). Is it any way to
filter details part of the report so that will not show those details records
for which field called Total will be empty ? I have tried to filter query by
adding condition but I am having problems. Can this be done in report design ?

Thanks for help.

Tony

 

[Marshall Barton]

I have report based on the query with some calculated fields. For some
records there is no data entered and as a result calculated record do not
have data (other fields of the record have still have data). Is it any way to
filter details part of the report so that will not show those details records
for which field called Total will be empty ? I have tried to filter query by
adding condition but I am having problems. Can this be done in report design ?

Well, it could be done using VBA code, BUT it's way better
to filter those records in the query. Please post a
Copy/Paste of the query's SQL and let's see if we can sort
out the problem there.

 

[Guest]

Hi Marsh,

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 ? Any other suggestions ???

Thanks for help.

Tony

 

[Marshall Barton]

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

Just use a criteria to check if [Hours Worked] is not null.

WHERE tblInvoices.[Hours Worked] Is Not Null

OTOH, if you feel you need to use an expression in the
field:
IIf(tblInvoices.[Hours Worked] Is Null, 0,
tblInvoices.[Hours Worked])

(BTW, that's easier to write as:
Nz(tblInvoices.[Hours Worked], 0)

then a criteria of >0 should work. As long as you're using
table.field names in the expression, I don't see why it
should be prompting you for anything, maybe you spelled
something wrong???