How can I get the total number of lines in a query? (the figure that appears at the bottom right of the screen)\nI need to export (automatically) this number to Excel. That is, I need to create a link from Excel to this number of lines in Access... How can this be done?\n\nE.g.\nI have a table in Access with 73421 lines.\nI need that this figure (73421) automatically appears in a cell in Excel.\nHow can this be done?
216891
try this query: select count(*) from tablename\nit will returns 73421.
508501
I know Excel inside & out...and I would love to help you, but I really cant understand what you are asking for.\n\nSo - you are trying to find the picture. OK. Pictures are handled as graphics on a different layer. They are not tied directly to cells or formulas. It should be clearly visible\n\nTry this: select all (Ctrl-A) go to the edit menu, select Clear > Formats. Now, in the format menu,  row > unhide, row > autofit, and Column > unhide, Column > autofit.\n\nThat should open up your entire worksheet. If something is there, you should see it.
254103
File.. Get External Data.. Link Tables .. browse to the xls file    would set up a permanent link to the excel data and give the spreadsheet a kind of table name. Then it can be queried. You want to use several columns over time and you can use an 'Append query' to append data to a table; but experiment with a crosstab query. In that way, your Access table would always have 2 fields plus maybe a ColumnNumber field where you could have a default value of 1 the first week, 2 the next week, etc.. A crosstab query would present that data as several several columns (one for each ColumnNumber value). When your Access skills increase, you might be very glad that you used a small number of fields because it makes the querying of the data simpler to maintain, in my opinion.\nAs for VLOOKUP, you can research "query table joins" and see how a query in design view lets you choose the field which 2 tables have in common. One table is really the excel spreadsheet presented as if it is a table; the other table is the real access table.
493705
2 ways you can do it, maybe 3. fastest would be type the first 3 numbers in a row, like A1=60000 A2=60001 A3=60002 then select those 3 numbers and put your mouse at the bottom right corner of the selection and (there should be a small black box there) and the cursor should look like a + then click/hold mouse and drag down 500 rows and it will auto increment. other way would be to write a macro.
453945
ok I give some hints\nIf you want to refer to a database from Excel you need to add the ADO 2.8 objects Library. \nHow to proceed?\nFrom the Visual basic editor, click on tools (menu bar) and then reference. look for Microsoft Activex Data objects 2.8 library and tick it.\nNow you should have access to ADO objects.\nTo learn how to use these objects try the msdn microsoft website, full of advice and templates\n\nIf you want an example from me, give me a valid email address where i can send you an excel workbook and small access data base for it takes too long to explain
450206
You don't even have to move to each cell.  Try this.\nIts unclear what you mean by grab the data but I'll put the values into an array.\n\nsub FindOpenCell()\ndim VarArray(10000)\nrowcounter = 1\ndo until cells(rowcounter,1) = "" \nVarArray(rowcounter) = cells(rowcounter,1)\nrowcounter = rowcounter + 1\nloop\ncells(rowcounter, 1).activate\n\nEnd Sub
549455
You use limit in conjunction with 1 or 2 numbers. If you use 1 number like this:\n\nselect * from myTable limit 5;\n\nthen MySQL will grab the first 5 results from the table. However, if you use if like this:\n\nselect * from myTable limit 5,5;\n\nthen MySQL will grab 5 results, starting from record number 5. So it's\n\nlimit [startPos], [numberOfRecords]\n\nEnjoy -
642597
This add-in may be what you need.\n\nhttp://www.cpearson.com/excel/RowLiner.htm
446109
To make the PIN number show up as a wildcard string:\n\nSelect the desired cells (Column A) right click and select "Format Cells", Select the "Number" tab, and go to "Custom".\n\nIn the cell that now says "General" type:\n"****";;;@\nexactly as shown\n\nAs for the other part, I think your best idea is to use an additional column and use the LOOKUP function.  Don't forget that you can always hide the extra columns by right clicking and selecting "Hide"\n\nHope this helps!\n\nPS  If none of this works, or if you have additional questions, email me at ThuckingSplut@yahoo.com, RE: Yahoo Answers - Excel.  I'll do my best to help you out!
36921
You can import the Excel spreadsheet into an Access table using the DoCmd.TransferSpreadsheet function.  Once the spreadsheet is in an Access Table, you can either run an update query (using DoCmd.RunSQL) or if you need something more complicated you'll have to open the table and process it row by row.
591289
<%\n' Selected constants from adovbs.inc\nConst adOpenStatic = 3\nConst adLockPessimistic = 2\n\nDim cnnExcel\nDim rstExcel\nDim I\nDim iCols\n\n' This is all standard ADO except for the connection string.\n' You can also use a DSN instead, but so it'll run out of the\n' box on your machine I'm using the string instead.\nSet cnnExcel = Server.CreateObject("ADODB.Connection")\ncnnExcel.Open "DBQ=" & Server.MapPath("xl_data.xls") & ";" & _\n"DRIVER={Microsoft Excel Driver (*.xls)};"\n\n' Same as any other data source.\n' FYI: TestData is my named range in the Excel file\nSet rstExcel = Server.CreateObject("ADODB.Recordset")\nrstExcel.Open "SELECT * FROM TestData;", cnnExcel, _\nadOpenStatic, adLockPessimistic\n\n' Get a count of the fields and subtract one since we start\n' counting from 0.\niCols = rstExcel.Fields.Count\n%>\n<table border="1">\n<thead>\n<%\n' Show the names that are contained in the first row\n' of the named range.  Make sure you include them in\n' your range when you create it.\nFor I = 0 To iCols - 1\nResponse.Write "<th>"\nResponse.Write rstExcel.Fields.Item(I).Name\nResponse.Write "</th>" & vbCrLf\nNext 'I\n%>\n</thead>\n<%\nrstExcel.MoveFirst\n\n' Loop through the data rows showing data in an HTML table.\nDo While Not rstExcel.EOF\nResponse.Write "<tr>" & vbCrLf\nFor I = 0 To iCols - 1\nResponse.Write "<td>"\nResponse.Write rstExcel.Fields.Item(I).Value\nResponse.Write "</td>" & vbCrLf\nNext 'I\nResponse.Write "</tr>" & vbCrLf\n\nrstExcel.MoveNext\nLoop\n%>\n</table>\n\n<%\nrstExcel.Close\nSet rstExcel = Nothing\n\ncnnExcel.Close\nSet cnnExcel = Nothing\n%>
205401
Create a query and paste this into it's SQL sheet:\n\nSELECT tblData.TICKER, tblData.DATE, tblData.CLOSE, (SELECT Sum([CLOSE])/Count([ticker]) AS Expr1 FROM tblData AS sub WHERE \n(((sub.TICKER)=[tblData]![Ticker]) AND ((sub.DATE) Between [tbldata]![date]-19 And [tbldata]![date]));) AS avg20 FROM tblData;\n\nIt will show the 20-day average for each stock for each date, but if there are fewer days of data available for a stock, it will show a real average for those days.\n\nI don't know what's causing the "..." in the middle of the SQL, but just replace it with "er]) "\n\nThis is what's called a Subquery
457854
Yes and I can think of several ways of doing it of the top of my head. One simple way is to create every cell's text as a label and search the Text attribute of that label. A faster way, performance wise, will be to create a data grid or an XML document to hold all the cell's text and then just filter it through an XSL sheet and redisplay on a new ASP page.
377469
Depending on where you are (the main form or the subform) and what you need to access, there are several different ways of doing this.  Check out the forum archives at:\n\nhttp://www.access-programmers.co.uk/forums/archive/index.php/t-77746.html\n\nto see how to do this.  I grabbed the text and saved it on my hard drive so I could get to it later; this cheat sheet really helps!\n\nPS: This is a MS-Access question, not a VB 6 question.  MS-Access uses "VBA" (vb for applications), VB6 has a compiler and it's own form editor.
505312
Two SQL statements, which you can enter in SQL view in the Access query designer.\n\nIn these statements, col1 is the greater column, col2 is the lesser column, and table is the name of your table.\n\nSELECT col1, col2, (col1 - col2) AS profit FROM table\n\nFor the sum:\n\nSELECT SUM(col1) AS col1sum FROM table
508910
to display the records from Sales table and Purchase table, do like Monalisa said, but he/she missing 'WHERE' query.\n\nassume :\nCreate Table Sales (Sales_Date date, total integer);\nCreate Table Purchase (Purchase_Date, total integer);\n\nSelect Sales_Date, Total from SALES where\nSales_Date>='10/03/2006' and Sales_Date<='10/09/2006'\nunion all\nSelect Purchase_Date, Total from PURCHASE where\nPurchase_Date>='10/03/2006' and Purchase_Date<='10/09/2006'\n\nNotes :\n'UNION' will eliminate the same records from PURCHASE table.\nto get all records we must use 'UNION ALL' .\n\nTo sum sales and purchase in 3rd to 9th oct  :\n\nSelect sum(A.Total) as TotalSales, sum(B.Total) as TotalPurchase from SALES as A, PURCHASE as B where A.Sales_Date>='10/03/2006' and A.Sales_Date<='10/09/2006'\nand B.Purchase_Date>='10/03/2006' and B.Purchase_Date<='10/09/2006'\n\nps :\nmy query format maybe have a little different with ms Access query format, so if my query can't run in ms Access, try to change it to ms Access query format.
76343
GETPIVOTDATA does get in the way...\n\nEither type the cell reference directly, or select another cell outside the table then click and drag it into the pivot table to the cell you want to refer to.  No other way round it I'm afraid!