Date Diff [DATE] & Now

[fenhow]

Is there a way in a query I can add a Date Diff option to calculate the time between a date that is in the query and now?

I was trying something like this but no luck

=DateDiff("d", [DateToPerson], [now])

Thanks

 

[pbaldy]

When you enclose Now in brackets, it's assumed to be a field name. Try:

=DateDiff("d", [DateToPerson], Now())

and I'd probably use Date() instead of Now().

 

[fenhow]

Ok, that works on the form if I add it to a new txtbox as the control however can I add this to a query? I tried to add it but the query comes back with no records, when i remove it it runs?

Thanks

 

[boblarson]

Does your date field have time in it? If it does you will need a slight modification to your IIF or create a new column formatted with just the date and the use the previous posting for the criteria.

 

[pbaldy]

It should work as a calculated field.

NewField: DateDiff("d", [DateToPerson], Now())

 

[fenhow]

Do I add this NewField into the query? Do I need a control in my table for the result?

 

[pbaldy]

If that's what you want, sure. I wouldn't store it in a table however.

 

[fenhow]

Ok, I dont actually want to keep track of it, i just want to see it on a report or on the form. For some reason when i add a new field into my query and cal it say exp1 and enter the code into the criteria it asks me for a param value for exp1? Also will it handle null values?

Thanks alot!

 

[pbaldy]

You can change Expr1 to whatever you want. In my example I used NewField. Expr1 is a default alias that the QBE adds.

I just tested and it worked fine with a criteria, but check in SQL view instead of regular design view. The WHERE clause should be

WHERE DateDiff...

not

WHERE Expr1...

 

[Sham]

hi - i've got a date question
Is there a formula that can work out the number of years and months between 2 dates. For example 17-Jan-06 - 30/04/2007 = 1yr 4 months.
is this possible. Once i've done this I can put them in year bandings i.e. <1yr, 2-3yr. Currently i'm doing this in Excel.
Welcome your responses