DON´T SHOW COLUMN IF EMPTY

[fsweb2002]

Hi
I have a query which returns 100+ records.
The thing is that I need columns not to show on the query if ALL the records are empty for that column.
For example, if all records are empty on COLUMN5, then don´t show that column on the result.
I am exporting the query to EXCEL once finished, and I have between 3-8 columns showing with no records.
Obviously I could simply hide those columns HOWEVER sometimes they do have valus, so I need them to be displayed.

Is there a way of doing this?

Thanks in advance.

 

[Quehay]

If you're selecting a field it's going to appear as a column. You could process the results further in VBA before/during export to Excel to get this result.

This is a very general sketch:

Get query into recordset.

Check if each record has a value for a given field. If a value is found set a boolean flag variable to TRUE.

If after processing recordset the flag for a given field is still FALSE then get rid of that field.

This would be tedious coding, and pretty extensive processing if it's a big recordset.

*Someone else may come up with a more economical approach though! Jeff Roberts

RenaissanceData.com

 

[fsweb2002]

thanks Jeff
Your idea was great.
I thought about it and did come up with what I needed with VBA code!
I was looking for an "easier" way, liy via the query builder or something but VBA is good too!

 

[Quehay]

Hey what does your code look like? Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[fsweb2002]

Hi Jeff

Thanks for your interest in helping me. I finally sorted out after many hours of heavy work.
I am not sure if is the best way, but it works.

Here is the code, maybe you see something I shouldn´t be doing (or a shorter, faster way to achieve it)

' x(1), x(2), x(3), x(4), x(5) are variables which represent the 5 different columns. It first did a search to see if any field of each column there was value. If NULL then the variable = "Use column" else stays empty.


If x(1) <> &quot;&quot; Then
massql1 = &quot;[Global Buying Rates].Freight1, [Global Buying Rates].Freight6,&quot;
sort1 = &quot;+nz([Global Buying Rates].[Freight1])&quot;
sort6 = &quot;+nz([Global Buying Rates].[Freight6])&quot;
End If
If x(2) <> &quot;&quot; Then
massql2 = &quot; [Global Buying Rates].Freight2, [Global Buying Rates].Freight7,&quot;
sort2 = &quot;+nz([Global Buying Rates].[Freight2])&quot;
sort7 = &quot;+nz([Global Buying Rates].[Freight7])&quot;
End If
If x(3) <> &quot;&quot; Then
massql3 = &quot; [Global Buying Rates].Freight3, [Global Buying Rates].Freight8,&quot;
sort3 = &quot;+nz([Global Buying Rates].[Freight3])&quot;
sort8 = &quot;+nz([Global Buying Rates].[Freight8])&quot;
End If
If x(4) <> &quot;&quot; Then
massql4 = &quot; [Global Buying Rates].Freight4, [Global Buying Rates].Freight9,&quot;
sort4 = &quot;+nz([Global Buying Rates].[Freight4])&quot;
sort9 = &quot;+nz([Global Buying Rates].[Freight9])&quot;
End If
If x(5) <> &quot;&quot; Then
massql5 = &quot; [Global Buying Rates].Freight5, [Global Buying Rates].Freight10,&quot;
sort5 = &quot;+nz([Global Buying Rates].[Freight5])&quot;
sort10 = &quot;+nz([Global Buying Rates].[Freight10])&quot;

End If


massql = massql1 & massql2 & massql3 & massql4 & massql5
sortby20total = &quot;nz([20Freight])+nz([20BAF])&quot; & sort1 & sort2 & sort3 & sort4 & sort5
sortby40total = &quot;nz([40Freight])+nz([40BAF])&quot; & sort6 & sort7 & sort8 & sort9 & sort10
sortby20total = sortby20total & &quot;- ([Global Buying Rates].FAC * ([Global Buying Rates].[20Freight] / 100)) - [Global Buying Rates].Incentive &quot;
sortby40total = sortby40total & &quot;- ([Global Buying Rates].FAC * ([Global Buying Rates].[40Freight] / 100)) - [Global Buying Rates].Incentive &quot;

sqlline = &quot;SELECT [Global Buying Rates].ShippingID, [Global Buying Rates].ToDate, [Global Buying Rates].POL, [Global Buying Rates].POD, &quot;
sqlline = sqlline & sortby20total & &quot; AS 20Total,&quot; & sortby40total & &quot; AS 40Total,&quot;
sqlline = sqlline & &quot;[Global Buying Rates].[20Freight], [Global Buying Rates].[40Freight], [Global Buying Rates].[20BAF], [Global Buying Rates].[40BAF],&quot;
sqlline = sqlline & massql
sqlline = sqlline & &quot;[Global Buying Rates].Commodity, [Global Buying Rates].Service, [Global Buying Rates].Frequency,[Global Buying Rates].FAC, [Global Buying Rates].Incentive INTO ExporttoExcel &quot;

sqlline = sqlline & &quot; FROM [Global Buying Rates] &quot;
sqlline = sqlline & &quot; WHERE ((([Global Buying Rates].RouteNo)='NAE') AND (([Global Buying Rates].Type)='F'))&quot;
sqlline = sqlline & &quot;ORDER BY [Global Buying Rates].POL, [Global Buying Rates].POD, &quot; & sortby20total
DoCmd.RUNSQL sqlline