Create Table Code Doesn't Insert Data with Decimals

[PSSMargaret]

I did not create the code below and need to make a change to it so that the TotalPoints value (including two decimals) is inserted into the TmpUnfundedTable. Currently, it is only insert the values to the left of the decimal. Any help would be appreciated.

Public Sub CreateTmpUnfundedTable()
Dim dbs As Database
Dim strSql As String

Set dbs = CurrentDb
strSql = "DELETE * FROM tblUnfundedTEMP"
dbs.Execute strSql
    
strSql = "INSERT INTO tblUnfundedTEMP " & _
    "SELECT qryRank1.ProjectID, qryRank1.CategoryID, qryRank1.CurrentPhaseID, qryRank1.TotalPoints " & _
    "FROM qryRank1 " & _
    "WHERE (((qryRank1.CurrentPhaseID)=1));"
dbs.Execute strSql

End Sub

 

[Minty]

You would need to change the design of tblUnfundedTEMP

It will have total points set as integer data type I suspect.

 

[theDBguy]

Hi. Looks like you're inserting into an existing table. If so, have you gone to the table's Design View and check the Data Type of the problem field?

Edit: Too slow...

 

[Minty]

Edit: Too slow...

Doesn't happen very often

 

[PSSMargaret]

You would need to change the design of tblUnfundedTEMP

It will have total points set as integer data type I suspect.

I did try that prior to posting which I should have mentioned but it didn't change the results.

 

[PSSMargaret]

I did try that prior to posting which I should have mentioned but it didn't change the results.
View attachment 85521
View attachment 85522

I also tried the Decimal setting.

 

[Gasman]

Well that shows you have a Integer type field, and they do not have decimals?

 

[Gasman]

I also tried the Decimal setting.

View attachment 85525View attachment 85526

It is not going to affect what is already there?
Enter a value directly into the table to see what it will accept.

 

[PSSMargaret]

Ok, the only thing that work was setting field size to Single. All good now.

 

[PSSMargaret]

Thanks for you help.

 

[Gasman]

Ok, the only thing that work was setting field size to Single. All good now.

Not so, Double will work just as well, especially if the number will be large?
Plus I have just added a field as Decimal with Scale set to 2?

 

[PSSMargaret]

Yes, you are correct, Double work as well. These number won't be large so I am going to stick with Single. Thanks again.

 

[pbaldy]

As an FYI, single and double are both floating point types, and can cause odd issues where 2+2<>4. I'd use currency or decimal.

 

[PSSMargaret]

They are points so Currency doesn't apply. Decimal did not work and I also found this website that states to avoid using the Decimal field size.

Avoid Using Decimal Field Sizes in Microsoft Access Tables

Use Double rather than Decimal for Microsoft Access number field sizes

www.fmsinc.com

 

[pbaldy]

The values don't have to be currency to use the type. The formatting issue is easily overcome. In any case, keep the floating point thing in mind if you ever have an issue with values that look like they should match but don't. Allen discusses the issue here:

Microsoft Access tips: Rounding numbers in Access

How to round numbers in Microsoft Access: to the nearest number, rounding up, rounding down, to the nearest 5 cents, rounding dates and times, bankers rounding, and handling floating point errors

www.allenbrowne.com

 

[PSSMargaret]

I sure will and thanks again.

 

[Isaac]

Or, qryRank1.TotalPoints isn't actually returning that value that you want..in which case table (destination of insert) design changes won't matter.

 

[pbaldy]

Or, qryRank1.TotalPoints isn't actually returning that value that you want..in which case table (destination of insert) design changes won't matter.

Note post 9.

 

[arnelgp]

it will Work on Decimal.
the problem on post#6 is maybe you just change the datatype and did not attempt to
Re-run your append code.

 

[Isaac]

Note post 9.

Yeah ... which to me reinforces what I mentioned, IF I understand what OP is saying correctly and the overall situation.
The solution was either redesign the table to match what the query was returning (apparently a Single), or, redesign the query to match what was really desired (which seems to have been double). Anyway...if they are happy somehow I am happy.