Need Help with the format of a field

[HockeyFan]

On a form I have a calculated field which divides a couple of fields and produces a number which represents a percentage.
I then have another field which the control source points to a field that is in my table( I'm using a query as the forms Record source).
I use a 'me.temp field = field in my table' to put the calculated result into my table to store it. The reason I do this is becuase I need that figure on a report also.

On the temp field, it performs the calculation correctly and displays the result as a percentage. The problem is that on the field that stores the result into my table, it keeps rounding the result and doesn't store the exact same result as the temp field.

I have tried changing the field type of the permanent field in the table to fixed, general number, decimal, etc. I can't seem to get it to store the exact same result of the calculated field.

Any ideas why this is happeing?

 

[dhookom]

These are not "Field Types": fixed, general number, decimal. They are formats for display and have no affect on what gets stored in your table.

The field type would be numeric long, double, single, byte, etc.

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

I'm sorry, i used the wrong terminology, however I did try different field types as well. I can't seem to get it to store the same result as the calculated field.
I know one solution would be to do the calculation on the report, however I was really hoping to have it stored into the table.

 

[dhookom]

Can you expand on "I use a 'me.temp field = field in my table' "?

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

Yes, it is simply a way that on an event on the form, I set the value to the field in my table, from the value of the calculated field.
Also I previously wrote it backawards. It is written as
me.field in my table = the calculated field value.

Of course, those aren't the field names, that is just what they represent.

 

[dhookom]

I mostly understood this already. I was hoping you would provide some code. Do you know how to set break points to check what is going on in your code?

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

I do, however my code is actually that simple. see below.



Private Sub DisplayrecsBtn_Click()
On Error GoTo Err_DisplayrecsBtn_Click

Dim stDocName As String

' sets the fields to the value of the calculated fields.

Me.TotDays = TempTotDays
Me.PercentA = tempPercentA

Exit_DisplayrecsBtn_Click:
Exit Sub

Err_DisplayrecsBtn_Click:
MsgBox Err.Description
Resume Exit_DisplayrecsBtn_Click
End Sub

 

[dhookom]

What are TempTotDays and tempPercentA? This shouldn't compile unless they are controls or fields in your form. If this is the case, you should follow through with your naming of prefixing with "Me."

When you put a break point on the Me.TotDays and hover your mouse over TempTotDays, what do you see? How about over tempPercentA?

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

TotDays is the field on the form which is also a field in my table. TempTotDays is the calculated field. With my code, I move the value of the calculated field into the field which is part of the table.

The calculations work fine, and it does move values into my table fine. The display of the temp fields are completly accurate. A number and 2 decimal digits of accuracy. The problem is in the display and storage of the fields in the table, it is not retaining the format and precision of accuracy.

I have changed the field type and every different attribute on the fields in the form, and the field in the table as well.

 

[dhookom]

What makes you think "it is not retaining the format and precision of accuracy"?

You have never told us the data type of TotDays and/or PercentA.

Format is a display attribute and has nothing to do with what is actually stored in the table.

What precision of accuracy do you expect? What are some sample values?

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

In a form the temp(calculated) field may display 84.3, the field that is part of the table will just say 80. It seems to be rounding automatically. instead of 1.21 for the temp field, the permanant field may say 1

 

[dhookom]

You have never told us the data type of TotDays and/or PercentA.

When you put a break point on the Me.TotDays and hover your mouse over TempTotDays, what do you see? How about over tempPercentA?

I'm noticing a pattern here. You need to understand that we can't see your tables or your screens or anything else. We have to rely on what you reveal to us. If you don't reveal significant information, we have a hard time providing assistance.

If you don't understand a question, let us know.

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

I have chosen to put the calculations in the report in addition to on the form. I was trying to avoid that, but due to time restraints I had to take the easy way out.