Open recordset not returning records

[anrichards22]

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

Dim d As DAO.Database
Dim dSQL As String
Dim drs As DAO.Recordset

dSQL = "SELECT * FROM [DBx45GH];"

Set d = OpenDatabase("C:\Users\X45\_DB\_DBx45.accdb")


Set drs = d.OpenRecordset(dSQL, dbOpenDynaset)

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.

Dim d As DAO.Database
Dim dSQL As String
Dim drs As DAO.Recordset


dSQL = "SELECT * FROM [DBx45GH] WHERE [DBx45GH].[RID]=201907041504102;"
Set d = OpenDatabase("C:\Users\X45\_DB\_DBx45.accdb")


Set drs = d.OpenRecordset(dSQL, dbOpenDynaset)

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

[anrichards22]

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:

with frs
   .addnew
   .FIELDS("RID")=drs.fields("RID")
   .update
end with

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?

[alansidman]

Have you checked your table design to ensure that the formatting of the field is correct.

[anrichards22]

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.