Date calculation in query with form variable

[Matti]

Hello
I urgently need to modify an old Access application, with no real knowledge
of Access. So, I would greatly
appreciate help. I believe it was originally written in Access-97, but is
now -2003

The following expression in Query calculates the age of an invoice in
months, from invoide date ( Inv Date1 ) to current date.
....,12*Year(Now())+Month(Now())+1-12*Year([Inv Date1])-Month([Inv Date1] AS
Age, ...
( I guess there was/is no 'datediff' function like in MS-SQL )

I now need to substitute current date by a date from a form variable. Form
name = BsQuery , field = Text8
What would be the syntax of the expression using the form field?
TIA
Matti

 

[Marshall Barton]

I urgently need to modify an old Access application, with no real knowledge
of Access. So, I would greatly
appreciate help. I believe it was originally written in Access-97, but is
now -2003

The following expression in Query calculates the age of an invoice in
months, from invoide date ( Inv Date1 ) to current date.
...,12*Year(Now())+Month(Now())+1-12*Year([Inv Date1])-Month([Inv Date1] AS
Age, ...
( I guess there was/is no 'datediff' function like in MS-SQL )

I now need to substitute current date by a date from a form variable. Form
name = BsQuery , field = Text8
What would be the syntax of the expression using the form field?

DateDiff("m", [Inv Date1[, Forms!BsQuery.Text8)

Yes, Matti, there is a DateDiff function in Access ;-)

 

[raskew via AccessMonster.com]

Hello,

In calculating age (of anything) there’s a problem using the DateDiff()
function. If computing months, the function merely subtracts one month from
another without regard to days. Example from debug (immediate) window:

InvDate1 = #1/15/07#
‘today’s date = #2/5/07# (5-Feb-07 – US Short Date)

? DateDiff("m", InvDate1, date())
1

‘In fact, only 21 days have expired – far short of a month.

To accurately return the number of full months, you need to add a Boolean
statement which will return –1 if True, 0 if False, e.g..

? (Day(Date()) < Day(InvDate1))
True

So, try this:

? DateDiff("m", InvDate1, Date()) + (Day(Date()) < Day(InvDate1))
0

HTH - Bob

I urgently need to modify an old Access application, with no real knowledge
of Access. So, I would greatly

[quoted text clipped - 10 lines]

name = BsQuery , field = Text8
What would be the syntax of the expression using the form field?

DateDiff("m", [Inv Date1[, Forms!BsQuery.Text8)

Yes, Matti, there is a DateDiff function in Access ;-)