ACCESS VBA to get column single to two decimals.

I have code that creates a temp table, but I need the single column to go to two decimals.  I inherited this and that is what the original programmer had in the temp table, but this thing have over 100 tables and I am trying to create and destroy temp tables as they are needed rather than clutter up the object window.  The statement to create the table, create the compound key and then set the property is:
        dbs.Execute "CREATE TABLE " & strTableOne & " (FNSType CHAR, QNeed CHAR, QAverage Single)"
        Call CreateCompountKey(strTableOne, "FNSType", "QNeed") 'Creates primary key
        Call CreateProperty(strTableOne, "QNeed")

The below is not doing what I think it should be and not sure why as all the samples I have Googled point to this
as being correct.
      Public Sub CreateProperty(strTableName As String, strFieldName As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property

  Set dbs = CurrentDb
  Set tdf = dbs.TableDefs(strTableName)
  Set fld = tdf.Fields(strFieldName)

  With fld
    Set prp = .CreateProperty("DECIMALPLACES", dbSingle, 2)
    .Properties.Append prp
  End With

End Sub
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I also think the code is correct....have you closed the objects ?
tdf,dbs

Open in new window

PatHartmanCommented:
Single precision numbers are floating point.  The database allows up to the max (I don't know what this is but it is a veeeeeeeeeeerrrrrrrrrrrry small number).  Fixed point data types such as Decimal allow you to specify a fixed number of decimal positions.  So use the decimal data type and use 2 as the Scale value
Sandra SmithRetiredAuthor Commented:
Yes, did close the db.  I think it is the scale.  I inherited this mess, it has had five other developers over the years with various skills, some is really well done, others are, let's just say, not as well done.  I changed the data type to decimal and now I am getting all kinds of errors.  To be continued tomorrow.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PatHartmanCommented:
Keep in mind, you asked for a physical solution.  That requires a change in the data type.  

You could simply change your queries to apply a format that only SHOWS two decimal places.  It won't affect how many are stored.  Just how many you see when you view a query or form.  That setting also appears on controls.  

Do NOT under any condition set that property on a table because it will keep you from seeing what is actually stored.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I would avoid dropping and adding tables a lot unless you regularly compact teh database. Otherwise you will have issues. That is probably why the previous person just emptied the tables each time.  That is how I would recommend it.

As pointed out, setting he display format in the table to two decimal place does not change the actual data, I agree that you should avoid any formatting at the table level. It will save you many headaches. You could easily format the data when your read it.

If you want the data to have only two decimal places then I round it best to round the data to two decimal places before it is  saved/append into the table.

TIP: If you only need two decimal place then use the Currency data type! I use it a lot.


In my accounting systems I use this to round to two place:

Public Function Round2(NumIn As Variant) As Variant

   Round2 = Int((NumIn * 100) + 0.5) / 100

End Function

Open in new window


If you must create the tables then check ut: http://allenbrowne.com/func-DAO.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Thank you for the explanation of the temp tables.  When I spoke with the users, I found out that in 15 years, the database had never been compacted, they had no idea what I was talking about and none of the other developers ever mentioned it, it was the first question I asked!  So, will take your suggestion and do it at the data-entry level, which makes sense.

As for keeping the temp tables permanently in the database, they are only used when reports are done once a month so I don't think it will be a problem.  The first thing I am going to do is put them on a regular compact/repair schedule.
PatHartmanCommented:
I would avoid dropping and adding tables a lot unless you regularly compact that database. Otherwise you will have issues. That is probably why the previous person just emptied the tables each time.  That is how I would recommend it.
I agree that leaving the tables there permanently is a better solution since you can set the properties once and forget them (in which case, the Decimal data type that I suggested would have given you EXACTLY WHAT YOU ASKED FOR without any query changes).  But keep in mind that both methods cause database bloat.  Access will not recover space from a deleted table or deleted rows until it is compacted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.