How can I set the # of Dps for a field using vb code?

[33216CLC]

Hi,

I am aware that the number of decimal places for currency, integer, etc, are automatically set to 4 and 2, etc. However, I would like to know how I can set this field property to whatever I need it to be. I find that in cases where the dp is set to auto, I have rounding problems.

Thanks for any assistance.

 

[JoeMiller]

You can use the format function to change the number of decimals. This is a display only function though, it does not change the data if that's what you are looking for.

HTH Joe Miller
joe.miller@flotech.net

 

[PamelaG]

Hi 3321!

It sounds to me like you are wanting to change the actual storage method of the field periodically. That is not a good idea! You need to think about what sort of data is going to be stored in the field and then pick the appropriate field size for the number. The field size property in number fields dictates the amount of storage and the range of values that can be stored in a given field.

Have a look at "number field size" in the Help to see what your options are and which one best fits your data.

If you are wanting to round or otherwise manipulate values in multiple ways before they get stored, then you are better off having the user enter the value in an unbound control and then write a procedure to evaluate and manipulate it before storing it in the field.

Also, if your values are so different and need to be handled differently, then I am wondering if they all belong in the same field?

Pamela

http://www.accessinsight.com

 

[33216CLC]

Joe,
Thank you for your suggestion. I presently use that method to store my currency values.


Pamela,
I define the field attributes once, when I create the tables using vb code. As I said to Joe, I store the currency values using the format function. This function is great and works fine for me, but when I consider that fact that I would have to add this function to every line of code that stores currency values, I conclude that it would be more efficient to have the field defined properly when it is created. What do you think? I use Access 97 format and Vb6.

Thank you both,

Cooleen

 

[PamelaG]

Hi Cooleen!

Yes, you want to set the field size property at the table level to start with!

Pamela

 

[33216CLC]

Hi Pamela,

Here is the code I presently use:

table.Fields.Append .CreateField("field1", dbCurrency)

Do you know of a way to refine this line of code so that the dp is set to 2. I also tried:

table.Fields.Append .CreateField("field1", dbCurrency, 2)

but the field still comes out as auto. Does this mean that one will have to physically open the database and set the dp size?

Thanks

 

[MichaelRed]

I'm somewaht lost/confused. Currency, as a data type, is somewhat different from general numbers. It is SPECIFIED as having four (4) decimal places. Although you can 'set' the number of decimal places as a property of the field, my understanding is that this would have NO effect.

After traversing the thread, I am left with issues.

1[tab]What are the problems in 'rounding' using the currency data type. I, for one, would like to see an example of the operation, including actual and expected results with a brief explination of the context (Form/Reoprt/query ...).

2[tab]Cooleen, you are generating the field dynamically. But why? A brief explination of the porcess/application which forces you generate a field (table?) dynamically might be useful.

3[tab]As to te actual setting of hte number of decimal places for a numeric field, this is a PROPERTY of the field.

When I have done the "exercise", it was to create a NEW db for a commercial APP. Each installation required a NEW db/tables ... I would place the tables/fields/properties in a db on the dist disc and PROGRAMATICALLY create the db on the user's target media. Creating the properties depends on the object type, for tables, these are generally as part of the field creation, in the form of:

Set prp = obj.CreateProperty(strName, varRetval, varValue)
obj.Properties.Append prp

Where the Obj represents the fuiled in the table, prp is the property, strname is the field name, retval is the field type and varValue is the property's value to be set.

this looks (and is) somewhat confusing, however it was documented (poorly - but DOCUMENTED) in '97 help. the TRICK here is understand that you are setting the PROPERT of the field, not the FIELD itself.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[33216CLC]

Hi Michael,

I am writing an upgrade for a payroll system. The database for the current system was created in Access (not using vb code) by another programmer. When I took over the program it was discovered that some currency amounts were rouding properly. For example, overtime rate is $15.03 and Overtime Hours is 1.5 hours. When you multiply these figures you get 22.545. If the format function is not used on this result before storage to the database, this figure will be stored. This by itself "may" be okay, but consider the result being 22.5445. If the latter was the result, This amount could be rounded to 22.54 (if rounded to 2dps) or 2.545 (if rounded to 3dps). If the already rounded figure is then rounded again, the result is 2.55, a 1 cent difference from the latter result. Also if a sum must be taken of several records, depending on how many, the result could be 1 cent * 1 million records.

To change the amount of decimal places on the currency fields, I had to open the database in Access and set the decimal place property to 2 instead of auto for all currency fields. In the upgade I use code to add new fields to existing tables and to create new tables. I do not want the currency fields to be auto, but 2.

I hope this clears your confusion. Please let me know if it doesn't.

Thanks

 

[MichaelRed]

Well, not really. I have done applications for one of the largest banks in the U.S., generating reports summarizing activity of commercial banking from the "banker" through seven levels of administration., with each level requiring the summation of currency values over the sub-groups. The reports were all "cross-footed" within the report and summary values (totals) validated aginst independent calculations done by other departments using other softeware / methods. We 'often' disagreed by a "few" pennies (I DO MEAN PENNIES) from totals in the tens to hundreds of millions of dollars, whithout ever resorting to any artificial rounding. I no loonger rember specific numbers of entites, but there were many thousand individual bankers reporting through approx 1300 "branches".

You can readily understand that the banking industry does not regard the processing ov "invntory" as a casual exercise. I have had individual bankers several states removed from my location seek me out to get an "expliniation" (read chew on me) for a difference of $0.03 in their booking activity.

So, again, I do not really "understand" the issue. It may be (is?) necessary to be careful in how calculations are done, but this is more a matter of accounting rules than data base / program design. In many businesses, there is an accounting department. It usually has a standard set of accounting rules & practices for the firm. These rules tell you (anyone) exactly how to do a specific calculation. Get teh rules. Follow them. Refer questions re the results to the accounting department. This MAY include you (your department) - or it may not. Doesn't matter. If you have followed the departmant practices / guidelines it is THEIR issue, not yours. When I did the banking thing, the only question I ever had to answer came from accounting, it did NOT concern the ammount, but where I got the "rules". All other questions were simply frwarded to them (accounting). Accounting DID correct me on occassion. MOtly, the corrections regarded changes in the rules (which I belatedly found happened sporadically - and often not through the banks' choice). It "appears" that banks (and most large businesses) generally follow a specific "Accounting Practice" (which has a name) and the Federal Gov. often issues "clarifications" to the various nammed practices. Many businesses subscribe to various publications which keep track of the rules and issue any changes to the subscribers through formal corresponeance. The businesses then review their specific implementation and decide if a process or calculation needs to be chaged. If it decides to implement the change, some have an internal distribution to notify individuals and groups of the change. This is where I "got caught", as I did nnot know of or subscribe to this distribution - but just used a copy I found on the internal network. Eventually (six weeks), A rule changed, I did NOT change the calculation, the BANKER checked my result aginst his expectation, found the discrepancy --- BANG! Accounting said it was a "Minor" error and was 'happy' to include me in the distribution of the rule changes. My immediate boss was only mildly "upset" - it "only" rquired re-doing a 7,000 page report - but that was after the entire report was run through a V&V type test.

Hmmmmmmmmmm, LOTS more than you wanted to know?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[33216CLC]

That's Okay. More, but informative. Aaaaand I conclude that dps' on currency fields cannot be set to any value other than auto (4), using vb code.

Thanks