VBA not reading decimal values in Access Table

[Raybert]

Hello everyone!

I'm hoping someone can help me solve this problem.

I have a VBA code which is set to retrieve values from a table in Access. When it retrieves this data, if it is a small number (0.00713 for instance) the recordset returns a value of 0. When i change the value to 10, it works properly. It seems to only be reading integers.

The table is formatted as Number / General Number and the variable I am saving the value into is Long.

I've copied a section of my code. If anyone could shed some light on this it would be greatly appreciated!

Ray

CODE:

Dim rst As New ADODB.Recordset
Dim PINC as Long

Do

If yearcount < 2015 Then
rst.Open "SELECT * FROM [2015] WHERE [ID] = 'Gasoline';", ConnectDB, adOpenStatic

ElseIf 2015 < yearcount < 2025 Then
rst.Open "SELECT * FROM [2025] WHERE [ID] = 'Gasoline';", ConnectDB, adOpenStatic

ElseIf 2025 < yearcount Then
rst.Open "SELECT * FROM [2035] WHERE [ID] = 'Gasoline';", ConnectDB, adOpenStatic

End If

PINC = rst(projectiontype)

MsgBox (PINC)


yearcount = yearcount + 1

'Close rst
rst.Close: Set rst = Nothing

Loop Until yearcount > (LCStart - 1)

[HiTechCoach]

What is the data type for the field in the table?

Sounds like it might be integer or long (the default) which has no decimal places. If so try, changing the data type to Double.

[hansup]

I have a VBA code which is set to retrieve values from a table in Access. When it retrieves this data, if it is a small number (0.00713 for instance) the recordset returns a value of 0. When i change the value to 10, it works properly. It seems to only be reading integers.

The table is formatted as Number / General Number and the variable I am saving the value into is Long.
If the data type of your variable is Long (long integer), it will store only whole numbers ... with no decimals.

It will operate the same as this (copied from the Immediate Window):

Debug.Print CLng(0.00173)
0
You should change your variable to a data type which accommodates float values. Maybe:

Dim PINC As Double
Your choice should be determined by the data type of the field the value is coming from. General Number is a format, not a data type ... it determines only how the data is displayed, not how it is stored.