Unanswered: Query doesn't find certain record: MS Query
I'm using an Excel sheet to append a table in Access. I've got a query setup off of this table and I refresh it within the sheet. However, certain records aren't returned through MS Query even though there are no parameters set to prevent them.
Records 6022.004, 6022.005, 6022.006 all return fine
But if I want only 6022.007 it is not returned even though it shows up in the table. I've also noticed that any number w/ the last 3 as .003 won't work either. If I set the criteria to All they show up but if I type in the individual value of 6022.007 it acts like it can't find it. I originally thought it was just formatting in my spreadsheet but when I attempt it in the MS Query interface, I get the same result.
I've done a lot of testing this morning and still running into an error. If I manually take copy the Excel row and append it to the table everything works right. But if I run the normal process which is an Append query that dumps the Excel linked table into the Access table then I'm not able to recall that record information in the query. I see the record number and all the data in the query but if I try to use that number in the criteria field to only show that row, and only returns blanks.
I've checked all my formatting and it all seems to be correct between the linked table and the access table. Could something be happening during the Append process and if so, is there a way to check that? I don't see any extra characters in the numbers.
I agree with NTC, if you are continually updaing the spreadsheet then the best method is to LINK the spreadsheet to a table in your database or use code to import the spreadsheet. Otherwise you will have to copy and paste each time.
The spreadsheet is linked. Here is my current setup:
Linked Excel sheet (in Access) -->Append query -->Access table
I actually run the append process through some code in Excel. This is it:
Dim cn As ADODB.Connection
Dim cm As ADODB.Command
dbPath = "\\xxx-xxx\Secure_Common$\Paper\DataTables\"
dbName = "TableNameHere.mdb"
Set cn = New ADODB.Connection
Set cm = New ADODB.Command
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & dbPath & dbName
.CommandText = "qryappPO_DETAILS"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
But some record numbers once appended to the table can't be retrieved by the query. I've checked all of my formatting but can't see a problem anywhere.