Recordset update puts double in, gets zero out, will accept numbers

[bentheimmigrant]

Recordset.update is such a foundational function that I can only think I'm getting the basics embarrassingly wrong somewhere, though I just can't see it.

I've got a table to which I just added a number field (let's call it fld1). I have a double (let's call it dbl) I wish to write to that field. I currently open the recordset (rst), add a new record, update a bunch of fields, and update the recordset, so I added dbl to the list. Yesterday I did the same thing with a couple text fields/strings, and had no issues. General code:

rst.AddNew
' A bunch of other fields that work fine
rst!fld1 = dbl ' The only line I've added to code
rst!OtherFld = OtherDouble ' Existing code, which works fine
rst.Update

Fairly standard.
However, fld1 always ends up set to 0, even though dbl is not.

Now, I set a watch for rst, and can obviously hover over to see values. If I give fld1 a default value (e.g. 1), I can see that in the recordset, but it starts at 0 (is that normal?). Otherwise it is NULL, as one would expect.

If I put a break in and step through, I can see that fld1 starts as a "Variant/Null", as does OtherFld. fld1 changes to "Variant/Long" with a value of 0 when I step through, even though I can see clear as day that dbl is in fact not zero. OtherFld becomes "Variant/Double", and takes on the correct value.

I have tried:

• Deleting fld1 and inserting a new one with a different name
• Compact and repair
• Manually opening the back end and compacting and repairing that
• Restarting Access
• changing dbl to a number (as in, replacing "dbl" with "2", so there is no variable). This... Works???

So I can directly put numbers in, but not doubles. I tried writing dbl to dbl2 (in case somehow there's some sort of holdover from the functions dbl calls?), and putting that in, but it doesn't work. I also changed OtherDouble to dbl, and this worked (though it's obviously the wrong thing to OtherFld).

I have of course triple checked that my doubles are indeed dimmed as doubles. They are. I've also checked that the field is indeed a number (which it must be, if it defaults to a value of 0).

The only other thing of relevance I can think is that once upon a time we migrated from mdb to accdb, possibly since this table was created. But like I said, I've been able to add text fields.

Edit: If I change dbl to simply be = 5, this also works, although the data type in the recordset says "Variant/Long". But if I change it to =5.21465678985417E-04, the problem reappears. For reference, dbl is between 0 and 1, and could be of the order 10^-3. /Edit

Edit2: I wrote a test to see if it's something I'm missing. The field is called "quality_out" on the table "Depress":

Sub QualityTest()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dbl As Double
Dim dbl2 As Double

dbl = 5.21465678985417E-04

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Depress")
rst.MoveLast
rst.Edit
rst!quality_out = dbl
rst.Update

dbl2 = 5
rst.MovePrevious
rst.Edit
rst!quality_out = dbl2
rst.Update
Set rst = Nothing
End Sub

Sure enough, the last record is 0, and the one above it is 5.
/Edit2

Halp.

 

[bentheimmigrant]

For the benefit of some other poor soul, it was simple. I hadn't realised that Access buries data type in design view. Clicking on the field then shows that it's defaulted to long integer (because who doesn't want that, right?). Changed to double, and now all is well with the world.

I had wondered why Access was happy with simply assigning "Number" as the data type.

... Should I thank myself?

 

[Galaxiom]

I hadn't realised that Access buries data type in design view. Clicking on the field then shows that it's defaulted to long integer (because who doesn't want that, right?)

Integers are also known as "natural numbers" so naturally they would be the default number type.