Nz Query?

P

Paul Johnson

I've got the following query, but it does not seem to work
properly as the U_Days_Susp field in a number field where
as the others are all date/time field.

DateDiff("m",Nz([U_DNA],[U_DDA]),[U_Census_Date]-
[U_Days_Susp])

For example...for whatever reason I get the following
result

Census_Date 31/08/2003
U_DDA 10/04/2003
U_DNA 21/06/2003
U_Susp_Days 61

With "d" I get the answer 10 days
Using "m" I get the answer 1 month (should be 0)

DateDiff("m",Nz([U_DNA],[U_DDA]), DateAdd("d",-
[U_Days_Susp],[U_Census_Date])) And it returns the same
answer. Any suggestions?
 
M

Michel Walsh

Hi,



? #31/08/2003# - 61
01/07/2003



Since U_DNA is not null, Nz returns it.


? DateDiff( "m", #21/06/2003#, #07/01/2003#)
1


since, indeed, you compare something in June and something in July, they are
not on the same month, 0 is not the right answer... and indeed there is one,
1, month "change" between the two dates.


? DateDiff( "d", #21/06/2003#, #07/01/2003#)
10

or 10 days changes. Since we do not have the hour-minute-second part, it can
be closer to 9 or to 11 in reality, just like


? DateDIff( "d", #20/06/2003 23:59:59#, #21/06/2003 00:00:01# )
1

for 1 day ( change ) even if it is just a matter of exactly 2 seconds!



Hoping it may help,
Vanderghast, Access MVP
 
P

Paul Johnson

You have lost me, are you saying that the calulation is
correct? And nothing is wrong with it?

-----Original Message-----
Hi,



? #31/08/2003# - 61
01/07/2003



Since U_DNA is not null, Nz returns it.


? DateDiff( "m", #21/06/2003#, #07/01/2003#)
1


since, indeed, you compare something in June and something in July, they are
not on the same month, 0 is not the right answer... and indeed there is one,
1, month "change" between the two dates.


? DateDiff( "d", #21/06/2003#, #07/01/2003#)
10

or 10 days changes. Since we do not have the hour-minute- second part, it can
be closer to 9 or to 11 in reality, just like


? DateDIff( "d", #20/06/2003 23:59:59#, #21/06/2003 00:00:01# )
1

for 1 day ( change ) even if it is just a matter of exactly 2 seconds!



Hoping it may help,
Vanderghast, Access MVP



I've got the following query, but it does not seem to work
properly as the U_Days_Susp field in a number field where
as the others are all date/time field.

DateDiff("m",Nz([U_DNA],[U_DDA]),[U_Census_Date]-
[U_Days_Susp])

For example...for whatever reason I get the following
result

Census_Date 31/08/2003
U_DDA 10/04/2003
U_DNA 21/06/2003
U_Susp_Days 61

With "d" I get the answer 10 days
Using "m" I get the answer 1 month (should be 0)

DateDiff("m",Nz([U_DNA],[U_DDA]), DateAdd("d",-
[U_Days_Susp],[U_Census_Date])) And it returns the same
answer. Any suggestions?
Click to expand...


.
Click to expand...
 
P

Paul Johnson

I've re-read your reply and understand what you are syaing
now. What calculation do I need in place to convert the
answer 10 using the "d" calculation into months then?

-----Original Message-----
You have lost me, are you saying that the calulation is
correct? And nothing is wrong with it?

-----Original Message-----
Hi,



? #31/08/2003# - 61
01/07/2003



Since U_DNA is not null, Nz returns it.


? DateDiff( "m", #21/06/2003#, #07/01/2003#)
1


since, indeed, you compare something in June and something in July, they are
not on the same month, 0 is not the right answer... and indeed there is one,
1, month "change" between the two dates.


? DateDiff( "d", #21/06/2003#, #07/01/2003#)
10

or 10 days changes. Since we do not have the hour-minute- second part, it can
be closer to 9 or to 11 in reality, just like


? DateDIff( "d", #20/06/2003 23:59:59#, #21/06/2003 00:00:01# )
1

for 1 day ( change ) even if it is just a matter of exactly 2 seconds!



Hoping it may help,
Vanderghast, Access MVP



I've got the following query, but it does not seem to work
properly as the U_Days_Susp field in a number field where
as the others are all date/time field.

DateDiff("m",Nz([U_DNA],[U_DDA]),[U_Census_Date]-
[U_Days_Susp])

For example...for whatever reason I get the following
result

Census_Date 31/08/2003
U_DDA 10/04/2003
U_DNA 21/06/2003
U_Susp_Days 61

With "d" I get the answer 10 days
Using "m" I get the answer 1 month (should be 0)

DateDiff("m",Nz([U_DNA],[U_DDA]), DateAdd("d",-
[U_Days_Susp],[U_Census_Date])) And it returns the same
answer. Any suggestions?
Click to expand...


.
Click to expand...
.
Click to expand...
 
P

Paul Johnson

I've figured it out...

DateDiff("m",Nz([U_DNA],[U_DDA]),[U_Census_Date]-
[U_Days_Susp])

I've changed this to...

DateDiff("m",Nz([U_DNA],[U_DDA])+[U_Days_Susp],
[U_Census_Date])

It now gives the results I expect.



-----Original Message-----
I've re-read your reply and understand what you are syaing
now. What calculation do I need in place to convert the
answer 10 using the "d" calculation into months then?

-----Original Message-----
You have lost me, are you saying that the calulation is
correct? And nothing is wrong with it?

-----Original Message-----
Hi,



? #31/08/2003# - 61
01/07/2003



Since U_DNA is not null, Nz returns it.


? DateDiff( "m", #21/06/2003#, #07/01/2003#)
1


since, indeed, you compare something in June and something in July, they are
not on the same month, 0 is not the right answer... and indeed there is one,
1, month "change" between the two dates.


? DateDiff( "d", #21/06/2003#, #07/01/2003#)
10

or 10 days changes. Since we do not have the hour-
Click to expand...
minute-
second part, it can
be closer to 9 or to 11 in reality, just like


? DateDIff( "d", #20/06/2003 23:59:59#, #21/06/2003 00:00:01# )
1

for 1 day ( change ) even if it is just a matter of exactly 2 seconds!



Hoping it may help,
Vanderghast, Access MVP



I've got the following query, but it does not seem to work
properly as the U_Days_Susp field in a number field where
as the others are all date/time field.

DateDiff("m",Nz([U_DNA],[U_DDA]),[U_Census_Date]-
[U_Days_Susp])

For example...for whatever reason I get the following
result

Census_Date 31/08/2003
U_DDA 10/04/2003
U_DNA 21/06/2003
U_Susp_Days 61

With "d" I get the answer 10 days
Using "m" I get the answer 1 month (should be 0)

DateDiff("m",Nz([U_DNA],[U_DDA]), DateAdd("d",-
[U_Days_Susp],[U_Census_Date])) And it returns the same
answer. Any suggestions?




.
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

query 6
date difference calculation 1
Help with small result type 2
DSum update query with multiple fields 0
outer join not working 9
Calculate Field In Query 5
Nz Function 1
PIVOT Question 6

Top