query

[John Reynolds]

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])

Any suggestions on how I can get round this. I can not
change the format of the table in any way though.

 

[Lucky]

Try following:
DateDiff("m",Nz([U_DNA],[U_DDA]), DateAdd("d"),-
[U_Days_Susp],[U_Census_Date]))

Let me know how it worked.

Lucky

 

[Duane Hookom]

What do you mean by "does not seem to work"? Other than U_DNA, are any
fields that might be Null?

 

[Guest]

I don't know what I am doing wrong but it won't accept the
second half of the query. COmes up with an error.

-----Original Message-----
Try following:
DateDiff("m",Nz([U_DNA],[U_DDA]), DateAdd("d"),-
[U_Days_Susp],[U_Census_Date]))

Let me know how it worked.

Lucky

-----Original Message-----
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])

Any suggestions on how I can get round this. I can not
change the format of the table in any way though.
.

.

 

[Guest]

It doesn't work in that it some fields it returns an
incorrect month. THis seems to happen where there is a a
number present in the U_Susp_Days field.

-----Original Message-----
What do you mean by "does not seem to work"? Other than U_DNA, are any
fields that might be Null?

--
Duane Hookom
MS 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])

Any suggestions on how I can get round this. I can not
change the format of the table in any way though.

.

 

[Guest]

I've got around the error and the calculation returns
values but again they are incorrect.

For example..whatever reason I get the following

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

answer using "d" is 10 days
answer using "m" is 1 month (should be 0)

-----Original Message-----
I don't know what I am doing wrong but it won't accept the
second half of the query. COmes up with an error.

-----Original Message-----
Try following:
DateDiff("m",Nz([U_DNA],[U_DDA]), DateAdd("d"),-
[U_Days_Susp],[U_Census_Date]))

Let me know how it worked.

Lucky

-----Original Message-----
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])

Any suggestions on how I can get round this. I can not
change the format of the table in any way though.
.

.

.

 

[Duane Hookom]

Lucky's response had an extra ")" so it will always error without removing
the ) after "d". Keep in mind that DateDiff() counts the number of month
boundaries between two dates.
DateDiff("m",#10/31/2003#,#11/1/2003#) = 1
DateDiff("m",#10/1/2003#,#11/30/2003#) = 1
DateDiff("m",#10/1/2003#,#1/31/2003#) = 0

--
Duane Hookom
MS Access MVP

It doesn't work in that it some fields it returns an
incorrect month. THis seems to happen where there is a a
number present in the U_Susp_Days field.

-----Original Message-----
What do you mean by "does not seem to work"? Other than U_DNA, are any
fields that might be Null?

--
Duane Hookom
MS 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])

Any suggestions on how I can get round this. I can not
change the format of the table in any way though.

.