If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
How do you dump the contents of a query into an Excel speadshe
Take a look at my July, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for a sample of how to use Automation to format a spreadsheet. You can download the column (and sample database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Jeff" wrote in message ... Thanks Doug- That did the trick!!! All I need to figure out now is how to have access "auto size to fit" the columns and center all the fields in my spread sheets and I will be off the the races. Thank you to everyone who contributed... your help is appreciated! "Douglas J. Steele" wrote: If AEEG is supposed to be the value for which you're looking, that should be SELECT * FROM Product_tbl WHERE (((Product_tbl.ProdFam)='AEEG')); That means you need strSQL = "SELECT * FROM Product_tbl WHERE " & _ "ProdFam= '" & rstProduct!product.Value & "'" Exagerated for clarity, that's strSQL = "SELECT * FROM Product_tbl WHERE " & _ "ProdFam= ' " & rstProduct!product.Value & " ' " -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Jeff" wrote in message ... Thanks for the info Doug... This worked and I then changed this to the following in order to name each worksheet by the name of the Product: strProd = Nz(DLookup("ProdFam", "Product_tbl", "Product = '" & rstProduct!product.Value & "'"), rstProduct!product.Value) Also, I found that in order to get any data from my product table that I needed to change the following (where "ProdFam" now replaces "Product"): strSQL = "SELECT * FROM Product_tbl WHERE " & _ "ProdFam= " & rstProduct!product.Value & ";" There still is one thing to resolve though as my query is not pulling the data that I want it to for the following reason: When the following line of code is executed: qdf.SQL = strSQL The query called AEEG (Prodfam of the first record found) is created with the following SQL code: SELECT * FROM Product_tbl WHERE (((Product_tbl.ProdFam)=[AEEG])); The problem is that I need the "WHERE" parameter to be created without the brackets Like this: SELECT * FROM Product_tbl WHERE (((Product_tbl.ProdFam)=AEEG)); But I am unsure how to correct this... Here is a portion of my code: ----- Dim qdf As DAO.QueryDef Dim dbs As DAO.Database Dim rstProduct As DAO.Recordset Dim strSQL As String, strTemp As String, strProd As String Const strFileName As String = "Product Part Numbers" Const strQName As String = "zExportQuery" Set dbs = CurrentDb strTemp = dbs.TableDefs(0).Name strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;" Set qdf = dbs.CreateQueryDef(strQName, strSQL) qdf.Close strTemp = strQName strSQL = "SELECT DISTINCT Product FROM Productname_tbl;" Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly) If rstProduct.EOF = False And rstProduct.BOF = False Then rstProduct.MoveFirst Do While rstProduct.EOF = False ' the following line of code specifies the names of worksheets ProdFam strProd = Nz(DLookup("ProdFam", "Product_tbl", "Product = '" & rstProduct!product.Value & "'"), rstProduct!product.Value) strSQL = "SELECT * FROM Product_tbl WHERE " & _ "ProdFam= " & rstProduct!product.Value & ";" Set qdf = dbs.QueryDefs(strTemp) qdf.Name = strProd strTemp = qdf.Name qdf.SQL = strSQL qdf.Close Set qdf = Nothing ----- any ideas how to get those brackets out of the query criteria? This is getting so close....thanks again -------------------------------- "Douglas J. Steele" wrote: That means that there isn't a value in the table corresponding to the value for which you're searching. When no record is found, DLookup returns Null, and String variables (which I'm assuming strProd is because of its name) cannot store Null values. The only data type that can store Null values is the Variant. Try: strProd = Nz(DLookup("ProdFam", "Product_tbl", _ "Product = '" & rstProduct!product.Value & "'"), "Not found") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Jeff" wrote in message ... Thanks John- I tried both: strProd = DLookup("ProdFam", "Product_tbl", "Product = '" & rstProduct!product.Value & "'") and strProd = DLookup("ProdFam", "Product_tbl", "Product = " & Chr(34) & rstProduct!product.Value & Chr(34)) and received a run time error '94' Invalid Use of Null. What am I doing wrong? "John Spencer" wrote: Your third argument (as posted) is missing an apostrophe before the quote. strProd = DLookup("ProdFam", "Product_tbl", "Product = '" & rstProduct!product.Value & "'") You could write that this way - using the Chr(34) to add the needed quote marks. strProd = DLookup("ProdFam", "Product_tbl", "Product = " & Chr(34) & rstProduct!product.Value & Chr(34)) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === Jeff wrote: I still can't figure out why this is giving me a 3075 syntax error on the line: strProd = DLookup("ProdFam", "Product_tbl", "Product = " & rstProduct!product.Value & "'") When I paste the SQL code that I am using for rstProduct (SELECT DISTINCT Product FROM Productname_tbl into a query I get the following result: Product AEEG CEEG LTM NEEG VEEG A couple of entries from the Product_tbl table a ProdFam Part No Description Type AEEG 1234 widget 1 1 CEEG 2344 widget 2 3 and the error that I am receiving is: Run-time error '2075'; Syntax error in string in query expression 'Product = AEEG". I have verified that there are no additional spaces in the fields of either table but am not sure what else I can do to solve this problem... My intent is to pull all records out of the Product_tbl that match the Product field from the ProductName_tbl and then dump each record into an individual worksheet in excel (that is named by the Product field) I am at my wit's end on this.... Any ideas? thanks ======================= Here is my code which I modified from your example: Private Sub Command0_Click() Dim qdf As DAO.QueryDef Dim dbs As DAO.Database Dim rstProduct As DAO.Recordset Dim strSQL As String, strTemp As String, strProd As String Const strFileName As String = "Product Part Numbers" Const strQName As String = "zExportQuery" Set dbs = CurrentDb strTemp = dbs.TableDefs(0).Name strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;" Set qdf = dbs.CreateQueryDef(strQName, strSQL) qdf.Close strTemp = strQName strSQL = "SELECT DISTINCT Product FROM Productname_tbl;" Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly) If rstProduct.EOF = False And rstProduct.BOF = False Then rstProduct.MoveFirst Do While rstProduct.EOF = False strProd = DLookup("ProdFam", "Product_tbl", "Product= " & rstProduct!product.Value & "'") strSQL = "SELECT * FROM Product_tbl WHERE " & _ "Product= " & rstProduct!product.Value & ";" Set qdf = dbs.QueryDefs(strTemp) qdf.Name = "q_" & strProd strTemp = qdf.Name qdf.SQL = strSQL141 qdf.Close Set qdf = Nothing DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ strTemp, "C:\" & strFileName & ".xls" rstProduct.MoveNext Loop End If rstProduct.Close Set rstProduct = Nothing dbs.QueryDefs.Delete strTemp dbs.Close Set dbs = Nothing End Sub =============================== "Ken Snell (MVP)" wrote: The error that you're getting suggests that the ' delimiters are not in the code step that uses the DLookup function -- notwithstanding what you've posted. Check your code again to be sure. And then copy the code from the procedure and post it here so that we can review it. |
Thread Tools | |
Display Modes | |
|
|