Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    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.

    For instance:
    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.

    Anyone know what is going on?

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well - I've never seen a query make an error.....so it is something else...

    try importing the spread sheet into an access table - - then run the query on this table.... you'll discover something along the way....

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    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.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    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.

  5. #5
    Join Date
    Nov 2008
    Posts
    26
    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:
    Code:
    Sub AppQry_D()
    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
    
    With cn
    .CommandTimeout = 0
    .Provider = "Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = "Data Source=" & dbPath & dbName
    .Open
    End With
    
    With cm
    
    .CommandText = "qryappPO_DETAILS"
    .CommandType = adCmdStoredProc
    .ActiveConnection = cn
    .Execute
    '
    End With
    
    cn.Close
    End Sub
    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.

  6. #6
    Join Date
    Oct 2009
    Posts
    340
    you wrote: "if I try to use that number in the criteria field to only show that row, and only returns blanks."

    one potential cause of this is that the table can display a value different than actually in the table; usually one sees this if the table's field is defined as a lookup field.

    on the otherhand it may be as simple as bad syntax in your criteria; is it a text field? are you doing: like "123" instead of just: 123 which only works if it is a number field...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •