Unexpected results

[Guest]

Hi all

I am analysing some customers data and it has a totals value in a header
file and split by labour, parts etc in other tables. I have performed
calculations and done a comparison query to make sure the totals compute. I
have compared the totals field with my calculated totals in the query by
using <> [Totals] in the criteria but it displays totals that are the same?

I thought maybe it was due to decimal places and rounding up but the data
seems to be 2 decimal places so I don't understand what is happening? Anyone
any ideas?

Thanks
Sue

 

[Joseph Meehan]

hughess7 wrote:
...

the
data seems to be 2 decimal places

Thanks
Sue

Access can display data in many different ways and just looking at the
displayed data is not a good way of determining how Access is actually
storing or using data. Check the table definition of the field(s) and tell
us what field types they are.

 

[Guest]

The stored value is Double, 2 decimals (also tried changing to fixed), the
other comparitive value was in a calculated control in a query definition.

Just for the purposes of this exercise I created another Totals field in the
table with same field definition but still did not work.

Very odd...

 

[Guest]

The two decimal points are what it shows - not actually what is stored. Also
you mentioned a calculated control. Sometimes they do not return exactly what
you would expect. Access, and many other programs, have a floating point math
problem. Here's an infamous example:

Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not the same as
-0.01.

Try formatting the calculated control in the query to show only two
decimals. Something like:

<> Format([Totals], "0.00")

 

[Jamie Collins]

Sometimes they do not return exactly what
you would expect.
Indeed...

Access, and many other programs, have a floating point math
problem. Here's an infamous example:

Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not the same as
-0.01.

Be careful here.

The OP alluded to a calculation in a query, which may give different
results to VBA.

For example, in VBA:

Debug.Print IIf(3.1 - 3.11 = -0.01, "Y", "N")

returns 'N', whereas the equivalent in SQL:

SELECT IIf(3.1 - 3.11 = -0.01, "Y", "N") FROM AnyTable;

returns 'Y'.

The reason is revealed when you do

TYPENAME(3.1 - 3.11 = -0.01)

in both environments i.e. in VBA you get 'Double' whereas in SQL you
get 'Decimal'.

Jamie.

--

 

[Jamie Collins]

The reason is revealed when you do

TYPENAME(3.1 - 3.11 = -0.01)

Oops, I meant

TYPENAME(3.1 - 3.11)

which returns 'Double' in VBA and 'Decimal' in SQL.

Jamie.

--

 

[Guest]

Thanks tried that also already (formatting calc field to 2 dec in query).
Normally in a table if you click in a field it changes to show you the
decimals stored rather than just displayed. In this instance they both appear
the same and no trailing decimals which is what is confusing me.

Thanks in advance for any help.
Sue

The two decimal points are what it shows - not actually what is stored. Also
you mentioned a calculated control. Sometimes they do not return exactly what
you would expect. Access, and many other programs, have a floating point math
problem. Here's an infamous example:

Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not the same as
-0.01.

Try formatting the calculated control in the query to show only two
decimals. Something like:

<> Format([Totals], "0.00")
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The stored value is Double, 2 decimals (also tried changing to fixed), the
other comparitive value was in a calculated control in a query definition.

Just for the purposes of this exercise I created another Totals field in the
table with same field definition but still did not work.

Very odd...

 

[Jamie Collins]

Thanks tried that also already (formatting calc field to 2 dec in query).
Normally in a table if you click in a field it changes to show you the
decimals stored rather than just displayed.

You should consider changing the data type to DECIMAL(n, 2), where n is
a suitably large enough value to accommodate values to the left of the
decimal point. In other words, make it incapable of *storing* any more
than two decimal places.

Jamie

--

 

[Guest]

thanks, will give this a try

Sue

You should consider changing the data type to DECIMAL(n, 2), where n is
a suitably large enough value to accommodate values to the left of the
decimal point. In other words, make it incapable of *storing* any more
than two decimal places.

Jamie

 

[Guest]

Changing the field types to decimal worked thanks

Sue