If the Query Column is empty how to Hide it.

[Basshopper]

Hi Have a large query with many fields. That get exported to excel. I would like to hide the columns that are empty when the query runs. Can anybody help.

Here is the code.

SELECT [t_Customer Profile].[Customer ID], [q_Quote BOM].[Assembly #], [q_Quote BOM].

, [q_Quote BOM].[Ascentron #], [q_Quote BOM].Rev, [q_Quote BOM].Ref, [q_Quote BOM].[Line #], [q_Quote BOM].[Customers #], [q_Quote BOM].Description, [q_Quote BOM].MFG, [q_Quote BOM].[MFG #], [q_Quote BOM].[U/M], [q_Quote BOM].[Qty Per], [q_Quote BOM].[Cust Cost], [t_Quote Qty]![Qty 1]*[q_Quote BOM]![Qty Per] AS [Qty 1 Ext], [q_Quote BOM].[Unit 1], [Qty 1 Ext]*[q_Quote BOM]![Unit 1] AS [Unit 1 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 1 Ext]=0,0,IIf([Qty 1 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 1 Ext])*[q_Quote BOM]![Unit 1]),IIf(([Qty 1 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 1 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 1 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 1 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 1]))) AS [XCS 1 Ext], [t_Quote Qty]![Qty 2]*[q_Quote BOM]![Qty Per] AS [Qty 2 Ext], [q_Quote BOM].[Unit 2], [Qty 2 Ext]*[q_Quote BOM]![Unit 2] AS [Unit 2 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 2 Ext]=0,0,IIf([Qty 2 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 2 Ext])*[q_Quote BOM]![Unit 2]),IIf(([Qty 2 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 2 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 2 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 2 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 2]))) AS [XCS 2 Ext], [t_Quote Qty]![Qty 3]*[q_Quote BOM]![Qty Per] AS [Qty 3 Ext], [q_Quote BOM].[Unit 3], [Qty 3 Ext]*[q_Quote BOM]![Unit 3] AS [Unit 3 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 3 Ext]=0,0,IIf([Qty 3 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 3 Ext])*[q_Quote BOM]![Unit 3]),IIf(([Qty 3 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 3 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 3 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 3 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 3]))) AS [XCS 3 Ext], [t_Quote Qty]![Qty 4]*[q_Quote BOM]![Qty Per] AS [Qty 4 Ext], [q_Quote BOM].[Unit 4], [Qty 4 Ext]*[q_Quote BOM]![Unit 4] AS [Unit 4 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 4 Ext]=0,0,IIf([Qty 4 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 4 Ext])*[q_Quote BOM]![Unit 4]),IIf(([Qty 4 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 4 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 4 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 4 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 4]))) AS [XCS 4 Ext], [t_Quote Qty]![Qty 5]*[q_Quote BOM]![Qty Per] AS [Qty 5 Ext], [q_Quote BOM].[Unit 5], [Qty 5 Ext]*[q_Quote BOM]![Unit 5] AS [Unit 5 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 5 Ext]=0,0,IIf([Qty 5 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 5 Ext])*[q_Quote BOM]![Unit 5]),IIf(([Qty 5 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 5 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 5 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 5 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 5]))) AS [XCS 5 Ext], [t_Quote Qty]![Qty 6]*[q_Quote BOM]![Qty Per] AS [Qty 6 Ext], [q_Quote BOM].[Unit 6], [Qty 6 Ext]*[q_Quote BOM]![Unit 6] AS [Unit 6 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 6 Ext]=0,0,IIf([Qty 6 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 6 Ext])*[q_Quote BOM]![Unit 6]),IIf(([Qty 6 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 6 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 6 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 6 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 6]))) AS [XCS 6 Ext], [t_Quote Qty]![Qty 7]*[q_Quote BOM]![Qty Per] AS [Qty 7 Ext], [q_Quote BOM].[Unit 7], [Qty 7 Ext]*[q_Quote BOM]![Unit 7] AS [Unit 7 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 7 Ext]=0,0,IIf([Qty 7 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 7 Ext])*[q_Quote BOM]![Unit 7]),IIf(([Qty 7 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 7 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 7 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 7 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 7]))) AS [XCS 7 Ext], [t_Quote Qty]![Qty 8]*[q_Quote BOM]![Qty Per] AS [Qty 8 Ext], [q_Quote BOM].[Unit 8], [Qty 8 Ext]*[q_Quote BOM]![Unit 8] AS [Unit 8 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 8 Ext]=0,0,IIf([Qty 8 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 8 Ext])*[q_Quote BOM]![Unit 8]),IIf(([Qty 8 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 8 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 8 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 8 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 8]))) AS [XCS 8 Ext], [t_Quote Qty]![Qty 9]*[q_Quote BOM]![Qty Per] AS [Qty 9 Ext], [q_Quote BOM].[Unit 9], [Qty 9 Ext]*[q_Quote BOM]![Unit 9] AS [Unit 9 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 9 Ext]=0,0,IIf([Qty 9 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 9 Ext])*[q_Quote BOM]![Unit 9]),IIf(([Qty 9 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 9 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 9 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 9 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 9]))) AS [XCS 9 Ext], [t_Quote Qty]![Qty 10]*[q_Quote BOM]![Qty Per] AS [Qty 10 Ext], [q_Quote BOM].[Unit 10], [Qty 10 Ext]*[q_Quote BOM]![Unit 10] AS [Unit 10 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 10 Ext]=0,0,IIf([Qty 10 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 10 Ext])*[q_Quote BOM]![Unit 10]),IIf(([Qty 10 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 10 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 10 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 10 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 10]))) AS [XCS 10 Ext], [t_Quote Qty]![Qty 11]*[q_Quote BOM]![Qty Per] AS [Qty 11 Ext], [q_Quote BOM].[Unit 11], [Qty 11 Ext]*[q_Quote BOM]![Unit 11] AS [Unit 11 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 11 Ext]=0,0,IIf([Qty 11 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 11 Ext])*[q_Quote BOM]![Unit 11]),IIf(([Qty 11 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 11 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 11 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 11 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 11]))) AS [XCS 11 Ext], [t_Quote Qty]![Qty 12]*[q_Quote BOM]![Qty Per] AS [Qty 12 Ext], [q_Quote BOM].[Unit 12], [Qty 12 Ext]*[q_Quote BOM]![Unit 12] AS [Unit 12 Ext], IIf([q_Quote BOM]![Min Qty]=0,0,IIf([Qty 12 Ext]=0,0,IIf([Qty 12 Ext]<[q_Quote BOM]![Min Qty],(([q_Quote BOM]![Min Qty]-[Qty 12 Ext])*[q_Quote BOM]![Unit 12]),IIf(([Qty 12 Ext]/[q_Quote BOM]![Pkg Size])=Int([Qty 12 Ext]/[q_Quote BOM]![Pkg Size]),0,[q_Quote BOM]![Pkg Size]-(((([Qty 12 Ext]/[q_Quote BOM]![Pkg Size])-Int([Qty 12 Ext]/[q_Quote BOM]![Pkg Size]))*[q_Quote BOM]![Pkg Size])))*[q_Quote BOM]![Unit 12]))) AS [XCS 12 Ext], [q_Quote BOM].[MIN LT], [q_Quote BOM].[MAX LT], [q_Quote BOM].[Min Qty], [q_Quote BOM].[Pkg Size], [q_Quote BOM].[Lot Charge], [q_Quote BOM].[Lot Qty], [q_Quote BOM].NRE, [q_Quote BOM].ETF, [q_Quote BOM].Tooling, [q_Quote BOM].[Other NRE], [q_Quote BOM].[Vendor Name], [q_Quote BOM].Stock, [q_Quote BOM].Comments
FROM [t_Customer Profile] INNER JOIN ([t_Quote Qty] INNER JOIN [q_Quote BOM] ON ([t_Quote Qty].[Assembly #] = [q_Quote BOM].[Assembly #]) AND ([t_Quote Qty].

= [q_Quote BOM].

)) ON ([t_Customer Profile].[Customer ID] = [t_Quote Qty].[Customer ID]) AND ([t_Customer Profile].[Customer ID] = [q_Quote BOM].[Customer ID])
WHERE ((([t_Customer Profile].[Customer ID]) Like [FORMS]![Parameter]![Customer ID] & "*") AND (([q_Quote BOM].[Assembly #]) Like [FORMS]![Parameter]![Assembly #] & "*") AND (([q_Quote BOM].

) Like [FORMS]![Parameter]!

& "*"))
ORDER BY [t_Customer Profile].[Customer ID], [q_Quote BOM].[Assembly #], [q_Quote BOM].

, [q_Quote BOM].[Line #]
WITH OWNERACCESS OPTION;

thanks for your help

 

[Basshopper]

Bob:Its moved past the last error and has a new one getting closer to the end here is the line and message


If DSum(rs("FldName"), "yournewquery") > 0 Then

Run time Erro 3075
Syntax error(missing operator)in query expression 'Sum(customer ID)'

I don;t see that one anywhere, any suggestions???

Larry

 

[scriverb]

The red code below is just a tempoarary name and you need to insert the name of your large query above. You never stated what it is. Just delete out the red code and insert the name of the query that you created and we are analyzing column by column:

 If DSum(rs("FldName"), "[red][i]yourQuery_Name[/i][/red]") > 0 Then

Post back with results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

The name of my query is "yournewquery" so it is correct. I copied my old query and saved it as this one for working on.

Larry

 

[scriverb]

Check your query for correctness. What I mean is that I took your original query in this thread and searched for Sum(customer ID) and can't find it. The error message is saying that you have a missing operator in an expression in your query. But, you must have saved the wrong query like maybe one that you were trying to Sum up each column or something. I don't see this function in any of my code or your original query.

Let me know what you find.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

Hi Bob: I have spent about 3 hrs looking for Sum(Customer ID) and it does not exist in any queries or code in this database.I did delete a query with it in couple of days ago. Is it possible its hidden somewhere from a deleted query??? any other suggestions???

Larry

 

[scriverb]

With it stopping on the line that is using "yournewquery" I have to suspect that it is in this query. Copy and paste the query into MS Word and do a search on Sum(customer id) and see if it finds this set of characters.

Let me know what you find.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

Hi Bob: I tried that but I think I found what it is doing. Customer ID is the first name in the tlbfieldlist so I put a differnent name in and it came up in the error as Sum(assembly #) so it took the next name in the table. So the DSum is making that error happen any suggestions???

Larry

 

[scriverb]

Then it must have to do with the field name table. What name did you use for this new table and have you entered the field names into it? This is the table where we will be looping through them performing the DSum process to rollup the values for all records. There should be field names in the field FldName.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Let's try this line of modified code:

 If DSum("'" & rs("FldName") & "'", "yourQuery_Name") > 0 Then

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

I did put in the all the fields from the query into the table tblfieldlist with a field call fldname. So that is all correct. Put in the new code and changed to my query name. Error message is could not execute SQL statement because it contains a field that has an invalid data type.

Larry

 

[scriverb]

Check the data type of the fields entered in the table. We can only expect to perform DSum functions on fields that are data type Number. No strings, Dates, booleans, etc.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

Bob: So does that mean I only put in the names of the fields that I want to be hide on 0 ??? if that be the case I did it wrong and put in all of the fields. other than the unit 1-12 the rest are calculated fields but with number outputs. So will redu the table data.

Larry

 

[scriverb]

This table should only be the fields that you want to assess wether there is data available in them and should be included in the query design as a visible column. They should be numeric so that the DSum function will work. The fields that we have automatically included in the query design you can see under the initial Select portion of the query where we are starting to create the strSQL variable.

Delete all fields from the table that do not fit this profile. They would be fields that we want to include in that initial string Select.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

Ok got the table data fixed is it ok that in the table the data type is currency or does it have to be numbers. I have them set at currency. Still getting the same error message on data type mismatch

Larry

 

[scriverb]

Let's just test out the DSum function here on your table. Create a new form with a command button. Put the following code in the OnClick event procedure:

Dim x As Currency
LoopAgain:
x = DSum("[red]xxxxxxx[/red]", "yournewquery")
Stop
GoTo LoopAgain

Now change the red x's to a currency field name from your query. Open the form name and Click the button. The code will stop in the code at the stop command. Hold your mouse over the X and you will see the amount summed up for the field in the DSum function. Change the field name and using F8 walk through another execution of the code until the STOP. Do through your all your field names until you find columns that don't work. Keep track of them so we may figure out what to do with them.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

Hi Bob: I did as you said and checked all the fields in the tblFieldList all X=0 each field also had the same error as before with the Sum(Fldname). Would it be easier for me to put the tables and queries involved in a database and email it to you?? Just a thought. Thanks for hanging in there with me on this.

Larry

 

[scriverb]

Sure. See my email in my profile.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

Hi Bob: Did you get my email with the zip file???

Larry

 

[scriverb]

Okay, because your field names have spaces in them it is necessary to bracket the names with square brackets otherwise when the DSum tries to execute it doesn't recognize the name correctly because it has a space in it. Change your code to include square brackets around your field names because some of your field names have spaces and some do not. This just ensures that the fields can be read correctly.


Now as far as the code I provided, I did receive your database and have made a few modifications. Here is code that works and creates the query of only the columns that have sums greater than 0:

Dim db As DAO.Database, rs As DAO.Recordset
Dim strSQL As String, i As Integer, lngRecCount As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("tblFieldList", dbOpenDynaset)
strSQL = "Select [Customer ID], [Assembly #], [quote=#], [Ascentron #], [Rev], [Ref], [Line #], [Customers #], [Description], [MFG], [MFG #], [U/M], [Qty Per], [Cust Cost] "
rs.MoveLast
lngRecCount = rs.RecordCount
rs.MoveFirst
For i = 1 To lngRecCount
   If DSum("[" & rs("FldName") & "]", "yournewquery") > 0 Then
      strSQL = strSQL & ", [" & rs("FldName") & "]"
   End If
   rs.MoveNext
Next i
db.QueryDefs("qryBuildYourQuery").SQL = strSQL & ", [Vendor Name], [Stock], [Comments] FROM yournewquery ORDER BY [Customer ID], [Assembly #], [quote=#], [Line #];"
rs.Close
db.Close

I created a new query called qryBuildYourQuery. This query gets rebuilt each time your run the code above. It reads your query "yournewquery" and analyzes the key columns and creates a new query to read and display only the appropriate columns.

Let me know if this works for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

This works great does exactly as I wanted thanks. I added some parameters to the yournewquery and when I run the code I get an error message

the object doesn't contain the automation object Forms!parameter! Customer ID

I have 3 parameters to search on

Customer ID
Assembly #
Quote #

what am I missing??