Exporting data changes absolute values...

  • Thread starter Thread starter Arthur Dent
  • Start date Start date
A

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!
 
What are the datatypes of your three columns?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
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
 
thats in the post. A & B are Number(Decimal), and C is calculated off A - B

Jeff Boyce said:
What are the datatypes of your three columns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Arthur Dent said:
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!
Click to expand...
Click to expand...
 
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.

Robert Morley said:
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 said:
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!
Click to expand...
Click to expand...
 
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

Arthur Dent said:
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.

Robert Morley said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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

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

Jeff Boyce said:
What are the datatypes of your three columns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Arthur Dent said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top