Results 1 to 7 of 7
  1. #1
    AbramF is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    3

    Handwritten records not found by query


    Hi,

    I have imported a table from excel. It is a list of objects with position numbers, such as "010-067-71". The field data type is Short text. I run a query with the only criteria "010-067-71", and it returns the record correctly. Now, if I change this field, save and write manually "010-067-71", save again, the query doesn't find the record. To further complicate the matter, I tried to change the criteria to Like "*067*", this resulted in finding other records that were imported from excel, all except the one that I changed manually.

    I tried to export the access table to notepad and word to search for hidden characters, but the imported records and manual records were the same, no hidden characters.

    I have the same trouble in several tables, but only with this position number (which of course happen to be the primary key for my main table, to wich all other tables refer)

    Does anyone have a clue? I have spent hours now trying to solve this issue.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps I am miss-understanding but:
    Now, if I change this field, save and write manually "010-067-71", save again, the query doesn't find the record.
    If you've changed the field why would you expect to find it again with the original criteria?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    without examining both your Excel spreadsheet and your ACCESS table it is difficult to be sure, but:

    first: during Import, ACCESS has a habit of interpreting the first record in the spreadsheet as the data type for the field rather than adhering to whatever you said it was. so I would actually look at the table in Design View and see what ACCESS says the field is.

    next: my next guess would be that the field does not just contain the text you want but also includes a delimeter character or a control character of some kind imported from Excel. when you edited a field this extra character was lost and so ACCESS no longer views this record in quite the same way. I would be tempted to run some queries asking for right([field name],1) and VarType([field name]) to confirm your belief that the field really is short text and that it contains only the text you want in there. and pay particular attention to the record giving you problems and make sure all its properties and returns are the same as the other records.

    third: if you are sure it is text, run some queries experimenting with a variety of other text functions to see if the field actually behaves the way text should. try Trim(), concatenation with &, StrComp(), InStr() and others. this will usually highlight what ACCESS thinks of the field.


    many thanks,


    Cottonshirt

  4. #4
    AbramF is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    3
    Quote Originally Posted by Bob Fitz View Post
    Perhaps I am miss-understanding but:If you've changed the field why would you expect to find it again with the original criteria?
    Hi,

    I changed the field value from "010-067-71" to "010-067-71". The only difference is that the first value is imported from excel, the other value i manually written. I acually used Copy/Paste.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by AbramF View Post
    Hi,

    I changed the field value from "010-067-71" to "010-067-71". The only difference is that the first value is imported from excel, the other value i manually written. I acually used Copy/Paste.
    OK. Thank you. Now I understand the problem.
    I would try using Trim() on the field to which you are applying the criteria, as suggested already by Cottonshirt.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    AbramF is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    3
    Quote Originally Posted by Cottonshirt View Post
    without examining both your Excel spreadsheet and your ACCESS table it is difficult to be sure, but:

    first: during Import, ACCESS has a habit of interpreting the first record in the spreadsheet as the data type for the field rather than adhering to whatever you said it was. so I would actually look at the table in Design View and see what ACCESS says the field is.

    next: my next guess would be that the field does not just contain the text you want but also includes a delimeter character or a control character of some kind imported from Excel. when you edited a field this extra character was lost and so ACCESS no longer views this record in quite the same way. I would be tempted to run some queries asking for right([field name],1) and VarType([field name]) to confirm your belief that the field really is short text and that it contains only the text you want in there. and pay particular attention to the record giving you problems and make sure all its properties and returns are the same as the other records.

    third: if you are sure it is text, run some queries experimenting with a variety of other text functions to see if the field actually behaves the way text should. try Trim(), concatenation with &, StrComp(), InStr() and others. this will usually highlight what ACCESS thinks of the field.


    many thanks,


    Cottonshirt

    Thanks for your reply,

    I checked the design view, it says Short Text. I also ran the query VarType and got 8 for all records, so I guess its correct. But when I ran Right() and Left() in a query, it became very interesting

    For all records imported from excel, the expected values appeared. "010-067-71" returned Right() = 0 and Left()=1, so no problem.

    But for my manual edited fields, I got answers that was strange. For the example above I got Right()=2 and Left()=2. After playing around I figured out that it returns the ID of a related table:

    I have a table; Objects, with primary key Position. It also has an autonumber ID.
    In the second table; Links, I have two fields refering to Position as secondary keys, to show that these two objects are depending on each other. My query on Links.position returns Objects.ID for Links.position that I have entered manually, but not the imported ones from excel. So, in my example; "010-067-71" in Links-table has ID=2 in Objects-table, hence Right()=2 and Left()=2.

    So, that explains the numbers, but I don't understand why this happens, or how to fix it.

    The lookup row source for the Links fields are:
    SELECT [Objekt].[ID], [Objekt].[Position] FROM Objekt ORDER BY [Position];

    Yeees!
    That solved it! In my SELECT row above, it seem access is showing .position, but holding the value .ID. The imported data wasn't treated this way because I imported the data before I set the Lookup-rule.

    I have now changed to SELECT [Objekt].[Position], [Objekt].[Position] FROM Objekt ORDER BY [Position];

    And it now works! :-)


    Many thanks to you Cottonshirt, you made my day!

    Best regards,
    Abram

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sounds like you are using lookups in your table - they can cause this sort of confusion

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-26-2015, 09:33 AM
  2. Replies: 12
    Last Post: 04-25-2013, 01:32 PM
  3. Replies: 3
    Last Post: 02-17-2012, 04:04 AM
  4. Need some help with a No Records Found query
    By kilosierra71 in forum Queries
    Replies: 1
    Last Post: 08-19-2011, 02:56 PM
  5. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 03:34 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums