Can I use DateDiff twice in same formula in query? 3

[buecker]

I'm currently using the datediff command to find the # of days between two dates...however I want to be able to subtract that from the datediff of two other numbers. Ofcourse, to no avail, I can't seem to get datediff to work with embedded parenthesis?

Expr2: ((DateDiff('y',[tocredit],SubmitDate])-(DateDiff('y',[Fileonhold],[FileReactivate]))))

 

[PWise]

try

Expr2: DateDiff('y',[tocredit],[SubmitDate])-DateDiff('y',[Fileonhold],[FileReactivate])

 

[SgtJarrow]

You shouldn't need all the parans anyway....and you are missing a open bracket.

Expr2:  DateDiff('y',[tocredit],[SubmitDate])-DateDiff('y',[Fileonhold],[FileReactivate])

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[Golom]

I think you need double quotes around the first argument. DateDiff is a function and it (probably) doesn't recognize 'y' as a valid argument specification.

Expr2: DateDiff("y",[tocredit],[SubmitDate]) - DateDiff("y",[Fileonhold],[FileReactivate])

You were also missing a square bracket before SubmitDate]

 

[SgtJarrow]

Yep...I missed the double quotes.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[buecker]

Thanks for the quick responses....I have another field that was the datediff for tocredit, submitdate. It stopped working for some reason.

I need to figure that what happened with that to test these choices.

Ben

 

[buecker]

Golom's code worked. Unfortunately, this brings up a different issue that I wasn't prepared for.

If this part: DateDiff("y",[Fileonhold],[FileReactivate]) is null (which should be most of the time), Access won't assume its a zero and therefore won't do the calculation will it?

 

[SgtJarrow]

Wrap that with an ISNULL

ISNULL(DateDiff("y",[Fileonhold],[FileReactivate]), 0)

That will will return a 0 and the subtracting will work correctly.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[buecker]

The expression you entered has a function containing the wrong number of arguments."

Without the zero at the end it just doesn't do the math. I'm trying to figure it out right now.

 

[PWise]

mstrmage1768

Isnull is for SqlServer

For jet Sql use nz(Value,value if null)

 

[SgtJarrow]

Sorry....use this instead:

Nz(DateDiff("y",[Fileonhold],[FileReactivate]), 0)

I mixed my syntax.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[SgtJarrow]

Yep...thanks pwise for the reminder.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[buecker]

Thanks everyone! NZ worked great.

Ben

 

[PWise]

if FileReactivate is the one that is null

use datediff("y",Fileonhold,nz(FileReactivate,xxx))

I dont think that is should be (0) Zero

try in the debug window
? datediff("y",Date(),0)

perhaps
datediff("y",Fileonhold,nz(FileReactivate,Fileonhold))