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

 

[scriverb]

They all have spaces in their names. This is a design No-No. Access can't figure out when the name begins and where it ends. Bracket all three fieldnames. ( i.e. [Customer ID], [Assembly #],

etc.)

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

OOPS forgot to tell you I have these in the criteria line of the query vs sql

Like [FORMS]![Parameter]![Customer ID] & "*"
Like [FORMS]![Parameter]![Assembly #] & "*"
Like [FORMS]![Parameter]!

& "*"

SO when the code runs it gives the error message does it need somekind of where statement.

 

[scriverb]

Well this WHERE statement needs to be added to the SQl build. The code I provided so far shouldn't have an error in it as I had tested it and it ran properly without error . You must have added something or changed something for that to have happened. What was it.

Now the new WHERE criteria can be added but we need to know if these should be combined with AND or OR conditions:

db.QueryDefs("qryBuildYourQuery").SQL = strSQL & ", [Vendor Name], [Stock], [Comments] FROM yournewquery WHERE (([Customer ID] Like [FORMS]![Parameter]![Customer ID] & '*') [RED]AND[/RED] ([Assembly #] Like [FORMS]![Parameter]![Assembly #] & '*') [RED]AND[/RED] ([quote=#] Like [FORMS]![Parameter]![quote=#] & '*')) ORDER BY [Customer ID], [Assembly #], [quote=#], [Line #];"

Change the RED [RED]AND[/RED]'s to OR's if necessary. this is for you to determine what you criteria should be but the technique is displayed here.

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

Hi Bob: It is all working know. I had the parameters info in the criteria line on the query grid and it was interfearing with your code. Took it out and worked great. Do you have any suggestions for speeding up this one since it is running 2 queries maybe that is the way it is suppose to be. When I run the code it takes about 8 sec for the parameter box to pop up. Does that sound right to you? Other than that I can move on with the dev and again thanks for all your help and patience with me on this its been a great leaning experience.

thanks

Larry

 

[scriverb]

That I believe is what you are going to have to live with. You have a lot of data here and the code must perform a summing operation on each of the target fields to see if data exists. There is a lot going on here and 8 seconds doesn't seems like a lot.

Good luck with your project. Glad that I could help you.

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

Hi Bob: Now that I have this dynamic query that changes sizes for every quote. Do you have a suggestion how I can put this in a form under datasheet view? I want to put some command buttons to do some things so I need a form around it. Can I put this in some unbound object? Direction???

thanks

 

[scriverb]

I supposed you could create an unbound form with a subform object. You could use this query in the subform which would give you a scrollable screen to display this query. This would also allow for buttons on the main form to do whatever you needed.

Just a thought here.

[COLOR=006633]Bob Scriver[/color]

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

 

[dhookom]

The Query By Form at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

has a datasheet subform that accepts and displays any record source with up to 30 different fields.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Basshopper]

HI Bob: I have actually started down the road you described but have not tested it yet with a upside potential of 75 fields. So I will keep plowing through it.

thanks

 

[Basshopper]

Hi Bob: Can you tell me how to add code to your orginal code. I want to count the number of records compare it to a number if equal the no problem if less than or greater than create an error message that tell the user that the final count is not correct. the fields are count the [Line #] get a total compare to [# of lines]

thanks

 

[scriverb]

You can perform a DCount function using your modified new query as the recordsource. This will give you a rowcount of the recordset being returned from your query.

This should get you what you want.

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

Hi Bob: as usual you got me back on track and it is working well. I am very close to launching this beast. Thanks again for all your help and expertise.

Larry

 

[Basshopper]

Hi Bob: not sure if you will get this its been a while. I have 2 problems now that it has been launched and the tables are getting full of data.

1) it takes 4 to 5 min to loop. Any speed enhancing suggestions

2) The code loops the entire table (which could take all the time) and lists the most qty quoted.Then the query asks for the parameters. My goal with this code was to loop only the data the parameters called for whether it be Customer ID or Assembly # or Quote # can the code be resequenced to only loop the data called for in the 3 parameters above. Here is my current code

Larry


Private Sub Openbomexcess_Click()
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("t_tblFieldList", dbOpenDynaset)
strSQL = "Select [Customer ID], [Assembly #],

, [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") & "]", "q_Quote Bom Excess") > 0 Then
strSQL = strSQL & ", [" & rs("FldName") & "]"
End If
rs.MoveNext
Next i
db.QueryDefs("q_Final BOM Excess").SQL = strSQL & ",[Min LT], [Max LT], [Min Qty], [Pkg Size], [Vendor], [Stock], [Comments] FROM [q_Quote BOM Excess] WHERE (([Customer ID] Like [FORMS]![Parameter]![Customer ID] & '*') AND ([Assembly #] Like [FORMS]![Parameter]![Assembly #] & '*') AND (

Like [FORMS]![Parameter]!

& '*')) ORDER BY [Customer ID], [Assembly #],

, [Line #];"
rs.Close
db.Close
DoCmd.OpenQuery "q_Final BOM Excess"
DoCmd.ShowToolbar "Main Menu", acToolbarYes
End Sub

 

[PHV]

Basshopper, simply use the 3rd argument of the DSum function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Basshopper]

Can you explain this more. What change will give me what result???

thanks