Format calculated query field

[harringg]

I've converted a 2003 DB to a 2007 DB that had a form which used a calculated query result based on two values from the underlying table, but doesn't write that result to the table. It just shows up in the form.

In Access 2007 that field shows ##################

I've gone to the underlying query and set the properties of that calculation to General Number and 2 decimal places. When I run the query, it returns the value as two decimals, ie. 2.31

I save the changes, and when I view in the form, it's still the ##################.

I then changed the properties of that field on the form to General and Decimal = 2 and same thing.

Did something 'break' in the conversion to 2007? In 2003 that field returned a readable number. Something obvious I'm overlooking?

How about the underlying format of the number field in the table? It's set as Single, General Number, 2 in the table. Should that be another value?

The field is 1" wide, so a resulting value of 2.31 shouldn't be too long.

It's a simple ratio calculation.

Ratio: [mA]/([uA]/1000)

 

[boblarson]

Have you tried setting the text box's Format property to match?

 

[harringg]

Have you tried setting the text box's Format property to match?

Is this the setting you are referring to?

On the Form in Design View,

Right-Click on Text Box (with the resulting calculation from the Query)>Properties

Under the Format Tab>Format>General Number
Under the Format Tab>Decimal Places>2
Under the Format Tab>Visible>Yes

That's what it is set as and still shows the ############# in Form View.

 

[boblarson]

Instead of General Number try using STANDARD. And if you go to the VBA Window, and to the Immediate Window when the form is open and displaying this, try typing in:

?Forms!YourFormNameHere.YourTextBoxNameHere

and hit enter (remember to change the form name and text box name in the code above to your real form name and text box name) to see what is currently displayed.

 

[harringg]

Instead of General Number try using STANDARD. And if you go to the VBA Window, and to the Immediate Window when the form is open and displaying this, try typing in:

?Forms!YourFormNameHere.YourTextBoxNameHere

and hit enter (remember to change the form name and text box name in the code above to your real form name and text box name) to see what is currently displayed.

Changing it to standard fixed the view issue. It resorted to a two decimal number as I was expecting.

Thank you for that help.

As a side, when I ran the ?Forms!..... in the Immediate window it returned (for a random record I had open at the time)

1.98846382369882

With your helpful comments, I did some more digging and when I expanded the Text box width to a randomly larger value, 4 inches, it showed all the numbers, 1.98846382369882, set to General Number and was 1.99 when set to Standard.

So was it as simple as General Number ignores the Decimal setting and in my case, the number was too long for the box, and as a result it showed the ###...###? Must be a "feature" of 2007?

It was a good learning tool none the less, your advice about the Immediate window. I learned something for future troubleshooting.

Thanks!