Exporting data changes absolute values...

[Arthur Dent]

Hi all...

I have a db with a view in it. The view has a calculated column in it which
is just the difference of two other columns.
Now, when i view the query in Access... it works fine... e.g.
ColA = 0
ColB = 0
ColC = 0 (computed off the difference of A and B).

However, when i export the query to a spreadsheet, using the following code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"CycleReportSource", "s:\desktop\cyc.xls", True
I get something very weird...
ColA = 0
ColB = 0
ColC = -0 < (NEGATIVE zero)

anyone seen this before, or know why it might be?
columns A and B are both of type Number(Decimal)
column C is defined as follows:
ColC: Nz([ColA],0)-Nz([ColB],0)

I am using Access 2007.
Thanks in advance!

 

[Jeff Boyce]

What are the datatypes of your three columns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Robert Morley]

I do remember seeing -0 in exports before, but it's been a long time, and I
can't remember for sure what the cause was. I *think* it was because I used
something like -(A > 4). I remember being highly amused by it (okay, I'm a
math geek AND a computer geek...strange things amuse me).

In your case, though, I would try converting the output of Nz() to a
long/double/whatever...in other words, change your formula to:

CLng(Nz([ColA],0)-Nz([ColB],0)) 'or
CDbl(Nz([ColA],0)-Nz([ColB],0))

Even when data types are implied by the ValueIfNull value, I've seen Nz()
return unexpected string results under some circumstances, or do other
strange things. See if that works.



Rob

 

[Arthur Dent]

thats in the post. A & B are Number(Decimal), and C is calculated off A - B

What are the datatypes of your three columns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all...

I have a db with a view in it. The view has a calculated column in it
which is just the difference of two other columns.
Now, when i view the query in Access... it works fine... e.g.
ColA = 0
ColB = 0
ColC = 0 (computed off the difference of A and B).

However, when i export the query to a spreadsheet, using the following
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"CycleReportSource", "s:\desktop\cyc.xls", True
I get something very weird...
ColA = 0
ColB = 0
ColC = -0 < (NEGATIVE zero)

anyone seen this before, or know why it might be?
columns A and B are both of type Number(Decimal)
column C is defined as follows:
ColC: Nz([ColA],0)-Nz([ColB],0)

I am using Access 2007.
Thanks in advance!

 

[Arthur Dent]

Hmm... sounded like a possibility - Thanks Robert, for the idea!
Unfortunately, it didnt work.

Just in case --- anybody else, please don't say "just use ABS"; this is an
inventory management system, and say the difference is 2, there IS a
difference between "2" and "-2" with regards to inventory cycles.

I do remember seeing -0 in exports before, but it's been a long time, and I
can't remember for sure what the cause was. I *think* it was because I
used something like -(A > 4). I remember being highly amused by it (okay,
I'm a math geek AND a computer geek...strange things amuse me).

In your case, though, I would try converting the output of Nz() to a
long/double/whatever...in other words, change your formula to:

CLng(Nz([ColA],0)-Nz([ColB],0)) 'or
CDbl(Nz([ColA],0)-Nz([ColB],0))

Even when data types are implied by the ValueIfNull value, I've seen Nz()
return unexpected string results under some circumstances, or do other
strange things. See if that works.



Rob

Hi all...

I have a db with a view in it. The view has a calculated column in it
which is just the difference of two other columns.
Now, when i view the query in Access... it works fine... e.g.
ColA = 0
ColB = 0
ColC = 0 (computed off the difference of A and B).

However, when i export the query to a spreadsheet, using the following
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"CycleReportSource", "s:\desktop\cyc.xls", True
I get something very weird...
ColA = 0
ColB = 0
ColC = -0 < (NEGATIVE zero)

anyone seen this before, or know why it might be?
columns A and B are both of type Number(Decimal)
column C is defined as follows:
ColC: Nz([ColA],0)-Nz([ColB],0)

I am using Access 2007.
Thanks in advance!

 

[Robert Morley]

It's unlikely to be any different, but just to be thorough, try CDbl around
both of the Nz()'s instead of the entire formula.

Also, if you get really desperate, try multiplying the whole thing by 1, or
by -1 twice, and see if either of those makes any difference.


Rob

Hmm... sounded like a possibility - Thanks Robert, for the idea!
Unfortunately, it didnt work.

Just in case --- anybody else, please don't say "just use ABS"; this is an
inventory management system, and say the difference is 2, there IS a
difference between "2" and "-2" with regards to inventory cycles.

I do remember seeing -0 in exports before, but it's been a long time, and
I can't remember for sure what the cause was. I *think* it was because I
used something like -(A > 4). I remember being highly amused by it (okay,
I'm a math geek AND a computer geek...strange things amuse me).

In your case, though, I would try converting the output of Nz() to a
long/double/whatever...in other words, change your formula to:

CLng(Nz([ColA],0)-Nz([ColB],0)) 'or
CDbl(Nz([ColA],0)-Nz([ColB],0))

Even when data types are implied by the ValueIfNull value, I've seen Nz()
return unexpected string results under some circumstances, or do other
strange things. See if that works.



Rob

Hi all...

I have a db with a view in it. The view has a calculated column in it
which is just the difference of two other columns.
Now, when i view the query in Access... it works fine... e.g.
ColA = 0
ColB = 0
ColC = 0 (computed off the difference of A and B).

However, when i export the query to a spreadsheet, using the following
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"CycleReportSource", "s:\desktop\cyc.xls", True
I get something very weird...
ColA = 0
ColB = 0
ColC = -0 < (NEGATIVE zero)

anyone seen this before, or know why it might be?
columns A and B are both of type Number(Decimal)
column C is defined as follows:
ColC: Nz([ColA],0)-Nz([ColB],0)

I am using Access 2007.
Thanks in advance!

 

[Jeff Boyce]

Quite so! I missed that.

So, if I recall, there are "issues" with the decimal datatype. It might be
something like the issues that crop up for Single and Double datatypes.

Since this is an inventory management system, I'm having trouble imagining a
decimal fraction of an item. I would have thought that an Integer datatype
would be sufficient, unless you are counting fractional parts of whole
items.

And if integer datatypes will do, that may resolve the -0 issue.

(a worst-case scenario, a total kludge, might be to use an IIF() statement
to change -0 to 0)

Regards

Jeff Boyce
Microsoft Office/Access MVP

thats in the post. A & B are Number(Decimal), and C is calculated off A -
B

What are the datatypes of your three columns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all...

I have a db with a view in it. The view has a calculated column in it
which is just the difference of two other columns.
Now, when i view the query in Access... it works fine... e.g.
ColA = 0
ColB = 0
ColC = 0 (computed off the difference of A and B).

However, when i export the query to a spreadsheet, using the following
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"CycleReportSource", "s:\desktop\cyc.xls", True
I get something very weird...
ColA = 0
ColB = 0
ColC = -0 < (NEGATIVE zero)

anyone seen this before, or know why it might be?
columns A and B are both of type Number(Decimal)
column C is defined as follows:
ColC: Nz([ColA],0)-Nz([ColB],0)

I am using Access 2007.
Thanks in advance!