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]

This query is monster right? You probably have a list of fields that you always want to include? Conversely you have a list of fields you may or may not want to include? Provide a list of the fields(because it is tedious to figure all of your columns from the SQL. Identify which ones you always want included and which you want analyzed. Also, the ones to be analyzed incidate the field type(number, text, date, etc)

I will give you an example of how I would do this.

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

Hi Bob: Yes this is a monster it took me a long time to get it to work right. This is the last item to get done on it.

thanks for your help.

This is the list of fields that always need to be visable.

Customer ID Assembly # Quote # Ascentron # Rev Ref Line # Customers # Description MFG MFG # U/M Qty Per Cust Cost

The rest of these fields are totally data dependant as to hide or visiable. No Data Hide them.

Qty 1 Ext Unit 1 Unit 1 Ext XCS 1 Ext Qty 2 Ext Unit 2 Unit 2 Ext XCS 2 Ext Qty 3 Ext Unit 3 Unit 3 Ext XCS 3 Ext Qty 4 Ext Unit 4 Unit 4 Ext XCS 4 Ext Qty 5 Ext Unit 5 Unit 5 Ext XCS 5 Ext Qty 6 Ext Unit 6 Unit 6 Ext XCS 6 Ext Qty 7 Ext Unit 7 Unit 7 Ext XCS 7 Ext Qty 8 Ext Unit 8 Unit 8 Ext XCS 8 Ext Qty 9 Ext Unit 9 Unit 9 Ext XCS 9 Ext Qty 10 Ext Unit 10 Unit 10 Ext XCS 10 Ext Qty 11 Ext Unit 11 Unit 11 Ext XCS 11 Ext Qty 12 Ext Unit 12 Unit 12 Ext XCS 12 Ext MIN LT MAX LT Min Qty Pkg Size Lot Charge Lot Qty NRE ETF Tooling Other NRE Vendor Name Stock Comments

 

[scriverb]

One more thing here. Starting with "Qty 1 Ext", which of these fields are numeric and can be summed up to see if there sum is > 0 as this would tell us if there is any values in any of the records for that field. In addition, which are numeric and which are text. Are the numerics default filled with 0 or are there null values in them.

With that I can give you an example to get started with your analysis and the building of your final query.

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

Hi Bob:Other than Vendor Name and Comments (Text) rest it numeric. I currently have the fields set to nulls meaning empty but I could easily put default 0 in the fields if that is easier. Either way is fine with me. Also any fields with numbers in as in 1-12 are mostly what I need hid with nulls. Even if just qyt 1 has data the other fields outside of the 1-12 will have data in them. Hope that clarifies.

thanks

Larry

 

[scriverb]

It's late here and I am going to bed. Will work on this in the morning.

[COLOR=006633]Bob Scriver[/color]

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

 

[scriverb]

We are going to build another query from the bottom up using code. The input to this new query will be your existing query. First create a saved query with any code in it you want. Just a simple select using the current query as input. We just need a saved query to begin the process. Let's just name it [red]qryyournewquery[/red]. You can change it to whatever you want later but just update the below code with the correct name.

Create a table(tblFieldList) with a single field called FldName as Text(15). Enter all of the fields that need to be analyzed.

Put this code behind a command button and it will analyze your query results and make a new query that just selects out the columns with data:

Dim db as DAO.Database, rs as Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("[red]tblFieldList[/red]", dbOpenDynaset)
db.QueryDefs([b][red]qryyournewquery[/red][/b]).SQL = "Select [Customer ID], [Assembly #], [quote=#], [Ascentron #], [Rev], [Ref], [Line #], [Customers #], [Description], [MFG], [MFG #], [U/M], [Qty Per], [Cust Cost] "
For i = 1 to rs.RecordCount
   rs.MoveFirst
   If DSum(rs("FldName"), "[i][red]yourqueryname[/red][/i]") > 0 then 
      db.QueryDefs([b][red]qryyournewquery[/red][/b]).SQL = db.QueryDefs([b][red]qryyournewquery[/red][/b]).SQL + ", [" & rs("FldName") & "]"
   End If
Next i
db.QueryDefs([b][red]qryyournewquery[/red][/b]).SQL = db.QueryDefs([b][red]qryyournewquery[/red][/b]).SQL + ", [Vendor Name], [Stock], [Comments] [blue]ORDER BY . . .[/blue]"
rs.close
db.close

Now just update the above with your ORDER BY code as you know better here than I. This code creates a whole new SQL string in your Saved query. I only checked these values for summing up greater than 0. If there is a different expression to consider you can change that but this should give you the starting point for analyzing all of the target columns. If the DSum function creates a very slow process we can change it to a recordset looping routine to look for empty columns also. Tune this code up and give it a try and then look at the resulting SQL in your new query.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

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

 

[scriverb]

I was just thinking of another approach that would also work. Create a query that utilizizes the queries Totaling capabilities to perform a Sum function on each of the numeric columns that you need to analyze. We would then open this query as a recordset and and analyze each column similar as we did above for a value greater than 0 or whatever criteria that you wish. The building of the query would be similar as the technique that I supplied.

Example of the summing query:

Select Sum([Qty 1 Ext]) as [Qty 1 Ext], Sum([Unit 1]) as [Unit 1],. . .[i][green]continue with all the fields you want to sum [/green][/i]
FROM [i][red]yourqueryname[/red][/i];

When you initialize this query as a recordset you can then access each of the columns and analyze them for value. Then you can build the new query as we did above using the cancatenation technique.

Good luck and post back if you have questions.


[COLOR=006633]Bob Scriver[/color]

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

 

[dhookom]

What? No comments yet on normalizing the table structure? Repeating groups of fields and expressions could be avoided entirely if the table was normalized. Then, if you need to push the results to Excel then just create a crosstab. The crosstab will not include columns that are derived from data values.

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

 

[Basshopper]

Hi Bob: did as you said and getting a run time error, Not in this collection on this line

db.QueryDefs(qryyournewquery).SQL = "Select [Customer ID], [Assembly #],

, [Ascentron #], [Rev], [Ref], [Line #], [Customers #], [Description], [MFG], [MFG #], [U/M], [Qty Per], [Cust Cost] "

I am doing this in access 97 if that matters.

Larry

 

[scriverb]

Did you create a new saved query like I said and name it. I indicated to call it qryyournewquery but if you name it something more appropriate then change it all the way through the code.

Yes, Duane you are right but I am just trying to help with a specific need here. The normalization was something I was going to address after we had this issue up and running.

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

Yes I checked that already and it is the same. I used everything just the way you explained. Any other suggestions??

Larry

 

[dhookom]

There is a saying "Don't throw good money after bad". I think the same is true here. I bet that spending the effort to create a union query of the Qty, Unit, and possibly other fields would simplify this to a minimal amount of effort and maintenance.

If you are looking for a good exercise in progamming and querydefs then continue on and good luck.

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

 

[scriverb]

My mistake. Put double quotes around the query name within the parens.

Duane, please feel free to jump in here and demonstrate what you are suggesting. I don't quite see it this evening. Maybe a little clearer tomorrow morning. Been a long weekend.

[COLOR=006633]Bob Scriver[/color]

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

 

[dhookom]

Without attempting to decipher the huge SQL:
1) Consider at table with ID and 5 pairs of Qty and Unit fields
[tt]
ID Qty1 Unit1 Qty2 Unit2 Qty3 Unit3 Qty4 Unit4 Qty5 Unit5
1 2 31 2 3 4 1 3 3
2 3 2 5 7 3 4 3 4 2 9
3 7 8 3 6 4 7
4 5 5 6 3 3 4
[/tt]
2) create a union query as follows:

SELECT ID, 1 AS Item, Qty1 AS Qty, Unit1 AS Unit
FROM tblWide
WHERE Qty1 Is Not Null
UNION all
SELECT ID, 2 , Qty2 , Unit2
FROM tblWide
WHERE Qty2 Is Not Null
UNION all
SELECT ID, 3, Qty3 , Unit3
FROM tblWide
WHERE Qty3 Is Not Null
UNION all
SELECT ID, 4, Qty4 , Unit4
FROM tblWide
WHERE Qty4 Is Not Null
UNION ALL SELECT ID, 5, Qty5 , Unit5
FROM tblWide
WHERE Qty5 Is Not Null;
3) Creating a crosstab like
TRANSFORM Sum(quniItemQtyUnit.Qty) AS SumOfQty
SELECT quniItemQtyUnit.ID
FROM quniItemQtyUnit
WHERE (((quniItemQtyUnit.ID)>2))
GROUP BY quniItemQtyUnit.ID
PIVOT quniItemQtyUnit.Item;
This will only display columns where there are values.

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

 

[Basshopper]

Hi Bob; I tried the quotes still hanging on the same line

db.QueryDefs("qryyournewquery").SQL = "Select [Customer ID], [Assembly #],

, [Ascentron #], [Rev], [Ref], [Line #], [Customers #], [Description], [MFG], [MFG #], [U/M], [Qty Per], [Cust Cost]"

As far a Duane's idea looks like it could work also. Since I am pretty new to SQL and it took me weeks to get this far as those formulas are complex. If he would want to convert the whole code to this I would be glad to put in a query and use it. I am running out of time know for me to get the learning curve on that one. So I either keep pushing forward where I am at or get some help. Thanks for all your expertise both of you.

Larry

 

[scriverb]

Basshopper: I don't see anything wrong with the code as you displayed it. You must have dimentioned the db as DAO.Database and Set it as the CurrentDB for this to work.

What exactly is the error message when the code debugger highlights this line?

[COLOR=006633]Bob Scriver[/color]

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

 

[Basshopper]

The message say Run Time error 3141

the select statement includes a reserved word or argument word that is misspelled or missing,or the punctiation is incorrect.

db.QueryDefs("qryyournewquery").SQL = "Select [Customer ID], [Assembly #],

, [Ascentron #], [Rev], [Ref], [Line #], [Customers #], [Description], [MFG], [MFG #], [U/M], [Qty Per], [Cust Cost]"

Larry

 

[PHV]

No FROM clause ?

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

 

[scriverb]

PHV is correct in that we needed to use a string variable to build the entire SQL string and then assign to the query .SQL property:

Dim db as DAO.Database, rs as Recordset
Dim strSQL as String
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] "
For i = 1 to rs.RecordCount
   rs.MoveFirst
   If DSum(rs("FldName"), "[red]yourqueryname[/red]") > 0 then
      strSQL = strSQL & ", [" & rs("FldName") & "]"
   End If
Next i
db.QueryDefs([red]qryyournewquery[/red]).SQL = strSQL & ", [Vendor Name], [Stock], [Comments] FROM [i][red]yourqueryname[/red][/i] ORDER BY . . ."
rs.close
db.close

[COLOR=006633]Bob Scriver[/color]

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