+ Reply to Thread
Results 1 to 4 of 4

Open recordset not returning records

  1. #1
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Open recordset not returning records

    If I run the following code, I get all the records in my database.

    Please Login or Register  to view this content.
    The above code returns a recordset that includes records with the RID=201907041504102

    If I run the following code it does not return any records for RID=201907041504102.

    Please Login or Register  to view this content.


    Can any provide any guidance into what I may be doing wrong?

    If I change the WHERE RID= to a different RID ...sometimes it returns records sometimes it doesn't.
    Each RID definitely has records in the database.
    FYI... the recordset has RID's with last 3 digits starting from 101 to 110.
    It seems to randomly not return any records eg RID's ending in 102,106,110

  2. #2
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Re: Open recordset not returning records

    Okay, so turns out that when adding the RID to my new table it randomly adds a 3 digit decimal to random RID's.

    In the original data source (another access table) RID is stored as a Number formatted as Decimal with 3 digits.
    If I get a recordset with the RID=201907041504102 from this table I get the desired result.

    RID is stored as Number formatted as Double in my new table.



    The RID's are added to my new table from the original table like this:

    Please Login or Register  to view this content.

    All of the RID's are ORIGINALLY integers...why would Access add a random 3 digit decimal to random RID's in the process of adding records to my new table?
    Last edited by anrichards22; 07-05-2019 at 11:36 PM.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Open recordset not returning records

    Have you checked your table design to ensure that the formatting of the field is correct.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Contributor
    Join Date
    11-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    334

    Re: Open recordset not returning records

    Thanks for the reply Alan.

    Changing the data type from decimal to double fixed the problem.

    I am still curious as to why Access would add decimals to a value that is an integer if it is is stored as a decimal. On top of that it seems to add these decimals to random records in the recordset.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] ADO Recordset Only Returning 1 Value
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2015, 02:59 AM
  2. Open another recordset within current recordset vba
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2015, 04:32 AM
  3. After opening a recordset from an ADO connection I can no longer insert new records?
    By dmasters4919 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2015, 08:12 PM
  4. CopyFromRecordset does return only 1000 records while recordset have 4000 records
    By KRUSHNAT in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2012, 05:47 PM
  5. Number of records in a recordset
    By efernandes67 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2011, 12:34 AM
  6. Recordset keeps returning EOF and shouldn't be.
    By BerkshireGuy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-23-2006, 10:33 AM
  7. problem returning a recordset from a function
    By Laurent M in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1