I have a crosstab query whose results are put into a table. I created this table by running another make-table query with my crosstab query's information. So the line of information goes something like this:
This creates the table but I noticed that if i try to query from the new table (tblAging) Access will not recognize the table's format. Its as if the crosstab part was removed. Can someone explain this?
Yes- ( I referenced the wrong table before- its tblTotals) I usually export this table to Excel (manually- TOOLS:OFFICE LINKS:ANALYZE WITH MS EXCEL) The information has always gone over fine. I can see the table now with all the information i need. when I look at the design of the table- all of the fields appear the way i want it to. I was hoping to automate this procedure (transfering to Excel) with VBA. I have a line of SQL (in VBA) that I have been trying to assign to a recordset.
strSQL5 = "SELECT * FROM [tblTotals] WHERE ([tblTotals].[Division])=" & Division(i) & ";"
Set rs = db.OpenRecordset(strSQL5, 2)
It looks like it works until it gets to the first number FIELD and then i get an error that says "Item not found in this collection" however, It finds the first two FIELDS of my table. I know this because in the EXCEL spreadsheet that this code is building I can see the header line that i have assigned and then the first two fields of the first record. It looks like it has trouble with the third FIELD. and i get the error. I have used this SQL string and recordset line before with great luck. What would cause ACCESS to stop creating a recordset in mid-stream?