stop rounding please!!

K

KC_Cheer_Coach

I am creating a comparison again, only this time I am not working with
currency. I am using a plain number with two decimal places. The first
database I did works fine in returning currency at 2 decimal places without
rounding. I cannot for the life of me figure out why I can't get this one to
do the same. Can someone help?

CURRENT Table and PREVIOUS Table are exactly the same:
H1 (text)
H4 (text)
BR (text)
DESC (text)
AMT/CNT (number: field size single or double, dec places is 2, format is
standard)

qryCOMPARE : Union Query
SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC, PREVIOUS.[AMT/CNT],
CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) - Nz(PREVIOUS.[AMT/CNT],0) as
[AMT/CNT DIFF]
FROM [CURRENT]
LEFT JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC))
UNION
SELECT PREVIOUS.H1, PREVIOUS.H4, PREVIOUS.BR, PREVIOUS.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM PREVIOUS
LEFT JOIN [CURRENT] ON ((PREVIOUS.H4 = CURRENT.H4) AND (PREVIOUS.H1 =
CURRENT.H1) AND (PREVIOUS.DESC = CURRENT.DESC))
UNION SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM [CURRENT]
INNER JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC));

Can you see what I am doing wrong from this??

Thanks!

Carrie
 
K

KC_Cheer_Coach

Even if I change it to Decimal it does not stop rounding. I either get
rounding with no decimal places or rounding with decimal places. I would like
NO rounding with 2 decimal places.
 
C

Clifford Bass

Hi Carrie,

The Nz(...) might be the culprit. I don't know this for sure. Try
wrapping all of your Nz(..)'s with CSng() or CDbl() depending on the data
type. So Nz(CURRENT.[AMT/CNT],0) - Nz(PREVIOUS.[AMT/CNT],0) would become
CSng(Nz(CURRENT.[AMT/CNT],0)) - CSng(Nz(PREVIOUS.[AMT/CNT],0)) and so on. If
that does not work, post a few data rows for each table, the results you
expect and the results you actually get.

Clifford Bass

KC_Cheer_Coach said:
Even if I change it to Decimal it does not stop rounding. I either get
rounding with no decimal places or rounding with decimal places. I would like
NO rounding with 2 decimal places.

KC_Cheer_Coach said:
I am creating a comparison again, only this time I am not working with
currency. I am using a plain number with two decimal places. The first
database I did works fine in returning currency at 2 decimal places without
rounding. I cannot for the life of me figure out why I can't get this one to
do the same. Can someone help?

CURRENT Table and PREVIOUS Table are exactly the same:
H1 (text)
H4 (text)
BR (text)
DESC (text)
AMT/CNT (number: field size single or double, dec places is 2, format is
standard)

qryCOMPARE : Union Query
SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC, PREVIOUS.[AMT/CNT],
CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) - Nz(PREVIOUS.[AMT/CNT],0) as
[AMT/CNT DIFF]
FROM [CURRENT]
LEFT JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC))
UNION
SELECT PREVIOUS.H1, PREVIOUS.H4, PREVIOUS.BR, PREVIOUS.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM PREVIOUS
LEFT JOIN [CURRENT] ON ((PREVIOUS.H4 = CURRENT.H4) AND (PREVIOUS.H1 =
CURRENT.H1) AND (PREVIOUS.DESC = CURRENT.DESC))
UNION SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM [CURRENT]
INNER JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC));

Can you see what I am doing wrong from this??

Thanks!

Carrie
Click to expand...
Click to expand...
 
K

KC_Cheer_Coach

GENIUS! I changed the format type from decimal to double and used the CDbl()
and it is working exactly how it is intended! Thank you so much!!

Carrie

Clifford Bass said:
Hi Carrie,

The Nz(...) might be the culprit. I don't know this for sure. Try
wrapping all of your Nz(..)'s with CSng() or CDbl() depending on the data
type. So Nz(CURRENT.[AMT/CNT],0) - Nz(PREVIOUS.[AMT/CNT],0) would become
CSng(Nz(CURRENT.[AMT/CNT],0)) - CSng(Nz(PREVIOUS.[AMT/CNT],0)) and so on. If
that does not work, post a few data rows for each table, the results you
expect and the results you actually get.

Clifford Bass

KC_Cheer_Coach said:
Even if I change it to Decimal it does not stop rounding. I either get
rounding with no decimal places or rounding with decimal places. I would like
NO rounding with 2 decimal places.

KC_Cheer_Coach said:
I am creating a comparison again, only this time I am not working with
currency. I am using a plain number with two decimal places. The first
database I did works fine in returning currency at 2 decimal places without
rounding. I cannot for the life of me figure out why I can't get this one to
do the same. Can someone help?

CURRENT Table and PREVIOUS Table are exactly the same:
H1 (text)
H4 (text)
BR (text)
DESC (text)
AMT/CNT (number: field size single or double, dec places is 2, format is
standard)

qryCOMPARE : Union Query
SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC, PREVIOUS.[AMT/CNT],
CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) - Nz(PREVIOUS.[AMT/CNT],0) as
[AMT/CNT DIFF]
FROM [CURRENT]
LEFT JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC))
UNION
SELECT PREVIOUS.H1, PREVIOUS.H4, PREVIOUS.BR, PREVIOUS.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM PREVIOUS
LEFT JOIN [CURRENT] ON ((PREVIOUS.H4 = CURRENT.H4) AND (PREVIOUS.H1 =
CURRENT.H1) AND (PREVIOUS.DESC = CURRENT.DESC))
UNION SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM [CURRENT]
INNER JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC));

Can you see what I am doing wrong from this??

Thanks!

Carrie
Click to expand...
Click to expand...
Click to expand...
 
C

Clifford Bass

Hurrah! You are welcome.

Clifford Bass

KC_Cheer_Coach said:
GENIUS! I changed the format type from decimal to double and used the CDbl()
and it is working exactly how it is intended! Thank you so much!!

Carrie

Clifford Bass said:
Hi Carrie,

The Nz(...) might be the culprit. I don't know this for sure. Try
wrapping all of your Nz(..)'s with CSng() or CDbl() depending on the data
type. So Nz(CURRENT.[AMT/CNT],0) - Nz(PREVIOUS.[AMT/CNT],0) would become
CSng(Nz(CURRENT.[AMT/CNT],0)) - CSng(Nz(PREVIOUS.[AMT/CNT],0)) and so on. If
that does not work, post a few data rows for each table, the results you
expect and the results you actually get.

Clifford Bass

KC_Cheer_Coach said:
Even if I change it to Decimal it does not stop rounding. I either get
rounding with no decimal places or rounding with decimal places. I would like
NO rounding with 2 decimal places.

:

I am creating a comparison again, only this time I am not working with
currency. I am using a plain number with two decimal places. The first
database I did works fine in returning currency at 2 decimal places without
rounding. I cannot for the life of me figure out why I can't get this one to
do the same. Can someone help?

CURRENT Table and PREVIOUS Table are exactly the same:
H1 (text)
H4 (text)
BR (text)
DESC (text)
AMT/CNT (number: field size single or double, dec places is 2, format is
standard)

qryCOMPARE : Union Query
SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC, PREVIOUS.[AMT/CNT],
CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) - Nz(PREVIOUS.[AMT/CNT],0) as
[AMT/CNT DIFF]
FROM [CURRENT]
LEFT JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC))
UNION
SELECT PREVIOUS.H1, PREVIOUS.H4, PREVIOUS.BR, PREVIOUS.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM PREVIOUS
LEFT JOIN [CURRENT] ON ((PREVIOUS.H4 = CURRENT.H4) AND (PREVIOUS.H1 =
CURRENT.H1) AND (PREVIOUS.DESC = CURRENT.DESC))
UNION SELECT CURRENT.H1, CURRENT.H4, CURRENT.BR, CURRENT.DESC,
PREVIOUS.[AMT/CNT], CURRENT.[AMT/CNT], Nz(CURRENT.[AMT/CNT],0) -
Nz(PREVIOUS.[AMT/CNT],0) as [AMT/CNT DIFF]
FROM [CURRENT]
INNER JOIN PREVIOUS ON ((CURRENT.H4 = PREVIOUS.H4) AND (CURRENT.H1 =
PREVIOUS.H1) AND (CURRENT.DESC = PREVIOUS.DESC));

Can you see what I am doing wrong from this??

Thanks!

Carrie
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Excel 2007 won't stop rounding down my formula results! 5
Loop through a record set to change a currency field based on average difference of two fields 0
Rounding problem 9
Report Limitation 2
Defined (field) size = -1 5
Rounding Macro 1
Fixed format not working correctly 2
combining queries into 1 8

Top