Create Table Code Doesn't Insert Data with Decimals (1 Viewer)

PSSMargaret

Registered User.
Local time
Today, 08:32
Joined
Jul 23, 2016
Messages
70
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.


Code:
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

AWF VIP
Local time
Today, 16:32
Joined
Jul 26, 2013
Messages
7,778
You would need to change the design of tblUnfundedTEMP

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

theDBguy

I’m here to help
Staff member
Local time
Today, 08:32
Joined
Oct 29, 2018
Messages
13,710
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...
 

PSSMargaret

Registered User.
Local time
Today, 08:32
Joined
Jul 23, 2016
Messages
70
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.
Table Design.JPG
Table Results.JPG
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:32
Joined
Sep 21, 2011
Messages
7,477
Well that shows you have a Integer type field, and they do not have decimals?
 

PSSMargaret

Registered User.
Local time
Today, 08:32
Joined
Jul 23, 2016
Messages
70
Ok, the only thing that work was setting field size to Single. All good now.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:32
Joined
Sep 21, 2011
Messages
7,477
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

Registered User.
Local time
Today, 08:32
Joined
Jul 23, 2016
Messages
70
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

Wino Moderator
Staff member
Local time
Today, 08:32
Joined
Aug 30, 2003
Messages
34,812
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:32
Joined
Aug 30, 2003
Messages
34,812
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:

 

Isaac

Lifelong Learner
Local time
Today, 08:32
Joined
Mar 14, 2017
Messages
3,225
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

Wino Moderator
Staff member
Local time
Today, 08:32
Joined
Aug 30, 2003
Messages
34,812
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:32
Joined
Feb 19, 2002
Messages
30,574
Currency is both a data type and a format. The currency data type is actually a scaled integer which is why you don't encounter floating point errors. Currency is ALWAYS assumed to have FOUR decimal digits so think of that as the value 1 being stored internally as 1000. The decimal position is always ASSUMED to be to the left of the fourth digit from the right so - 1x0000. I ALWAYS use Currency whenever I don't need more than 4 digits of precision for a number. Floating point errors can leave the users questioning your arithmetic so you really want to avoid them at all costs.

The currency format formats a number of any type (single, double, currency, decimal) as currency based on your Windows defaults.

I don't have a direct link but several years ago Luke Chung wrote a great article on the problem. You can find it at www.fmainc.com in their Access papers section. Also check out the other articles while you're there as well as the excellent tools. Luke doesn't even pay me for the testimonials
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:32
Joined
May 7, 2009
Messages
11,127
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.
 

Users who are viewing this thread

Top Bottom