Crosstab: Hide rows with empty values (1 Viewer)

canonquiche · Apr 27, 2016

I have a crosstab query that retrieves the last 3 months of data and finds the average for that period.

However, the query returns many rows where the Current Total is zero or blank. How do I hide records for which the entire date range consists of zero/blank values (i.e., all three months are zero/blank)?

I tried "Is Not Null" and <>0 in the criteria for Current Total, but Access said the value field in a crosstab can't accept a criteria...

Here's the SQL:

Code:

TRANSFORM Sum([Current Total]) AS [SumOfCurrent Total]
SELECT [Category], [CLIN], [Description], Avg([Current Total]) AS [3-Month Average]
FROM query_FFP_Charges
WHERE (((([Invoice Month]) Between DateSerial(Year(Date()),Month(Date()),0) And DateSerial(Year(Date()),Month(Date())-3,1)) 
GROUP BY [Category], [CLIN], [Description]
PIVOT [Invoice Month];

arnelgp · Apr 27, 2016

use a form to hide those column.

canonquiche · Apr 27, 2016

arnelgp said:
use a form to hide those column.

I want to hide rows, not columns.

See attached for example -- rows with no values in all three months should be hidden.

arnelgp · Apr 27, 2016

create a form based on the query, use datasheet form.

on form's load event:

private sub form_load()
dim rs as dao.recordset
dim i as integer
dim strSQL as string
dim strWhere as string
strsql = "select * from " & me.recordsource & " where "
set rs=me.recordsetclone
'we are interested in column 4 to the last row if it is all blank

for i=3 to rs.fields.count-1
strwhere = strwhere & "Trim(" & rs.fields(i).name & " & '')<>'' And "
next i
strwhere = left(strwhere, len(strwhere)-4)
strsql = strsql & strwhere
set rs=nothing
me.recordsource = strsql
end sub

canonquiche · Apr 27, 2016

arnelgp said:
create a form based on the query...

I tried this and got a '3075' run-time error:

Syntax error (missing operator) in query expression 'Trim(3-Month Average & ")<>" And Trim(01/01/2016 & ")<>" And Trim(02/01/2016 & ")<>" And Trim(03/01/2016 & ")<>""

arnelgp · Apr 28, 2016

forget about the form.
you can still filter your crosstab query by adding more column:

Field: [Current Total]
Total: Where
Criteria: <> 0

canonquiche · Apr 28, 2016

arnelgp said:
forget about the form.
you can still filter your crosstab query by adding more column:

Field: [Current Total]
Total: Where
Criteria: <> 0

That was exactly what I needed, thanks.

Thanks also for introducing me to datasheet forms.

Crosstab: Hide rows with empty values (1 Viewer)

canonquiche

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

canonquiche

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

canonquiche

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

canonquiche

Registered User.

Similar threads

Users who are viewing this thread