date difference expression in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to calculate a couple of date-related values, one is a date 6 months
from the start date, the other is the age today.

I can get this information by putting the expressions into a form, but then
cannot use this info to produce reports.

When I use the expressions in a query, I can generate the dates, but cannot
filter out the dates using Between' 'start date' And 'end date'.

In addition, I cannot get these query-based calculations to show in the
relevant fields in the form.

I would be very grateful for some help, as I have looked everywhere to find
a solution!

Many thanks
 
I need to calculate a couple of date-related values, one is a date 6 months
from the start date, the other is the age today.

I can get this information by putting the expressions into a form, but then
cannot use this info to produce reports.
Click to expand...

You could put the same expressions into the Contro Sources of
textboxes on the Report as well as on the Form.
When I use the expressions in a query, I can generate the dates, but cannot
filter out the dates using Between' 'start date' And 'end date'.
Click to expand...

Sure you can. What makes you think you can't apply criteria to a
calculated field? If it's a date/time field (say from DateAdd) then
you can use a criterion of BETWEEN [Start date:] AND [End date:].

Admittedly, this may be somewhat inefficient, since the calculated
field will not be indexed - but it will in fact work.
In addition, I cannot get these query-based calculations to show in the
relevant fields in the form.
Click to expand...

Well... how are you doing the query-based calculation? If you have a
field cell containing

DueDate: DateAdd("m", 6, [StartDate])

then you can set the Control Source of a Form or Report textbox to
DueDate, and you'll see that calculated expression. If you think
you're doing this and it's not working, please open your query in SQL
view and post the SQL code here (along with a description of what
you're actually seeing).

John W. Vinson[MVP]
 
Thanks so much, John. Have been battling away for the last few hours and
have used =Age([DOB]) to calculate in both the form and the query, so I've
used the query result to generate the value for the report.

However, what I want to be able to do is filter out the result in the =Age
field to find all employees over 65 adn I get 'DAta type mismatch in criteria
expression' whe i look for >65

Same prob with the + 6 mo query: once I have found the review date (date 6
months on from the start date), I need to be able to find all the due dates
in a particular month.

thank you so very much for your suggestions, will continue working through
them now. Am rather basic user of Access so words of one syllable
appreciated.

John Vinson said:
I need to calculate a couple of date-related values, one is a date 6 months
from the start date, the other is the age today.

I can get this information by putting the expressions into a form, but then
cannot use this info to produce reports.
Click to expand...

You could put the same expressions into the Contro Sources of
textboxes on the Report as well as on the Form.
When I use the expressions in a query, I can generate the dates, but cannot
filter out the dates using Between' 'start date' And 'end date'.
Click to expand...

Sure you can. What makes you think you can't apply criteria to a
calculated field? If it's a date/time field (say from DateAdd) then
you can use a criterion of BETWEEN [Start date:] AND [End date:].

Admittedly, this may be somewhat inefficient, since the calculated
field will not be indexed - but it will in fact work.
In addition, I cannot get these query-based calculations to show in the
relevant fields in the form.
Click to expand...

Well... how are you doing the query-based calculation? If you have a
field cell containing

DueDate: DateAdd("m", 6, [StartDate])

then you can set the Control Source of a Form or Report textbox to
DueDate, and you'll see that calculated expression. If you think
you're doing this and it's not working, please open your query in SQL
view and post the SQL code here (along with a description of what
you're actually seeing).

John W. Vinson[MVP]
Click to expand...
 
Hi, John

Have tried to use the Between...And in the DateAdd field, and it does
nothing.

Am off to bed as nearly midnight, will try your reply in the morning.

Many thanks for starting me on the right track - I am full of admiration for
your knowledge!
 
Thanks so much, John. Have been battling away for the last few hours and
have used =Age([DOB]) to calculate in both the form and the query, so I've
used the query result to generate the value for the report.

However, what I want to be able to do is filter out the result in the =Age
field to find all employees over 65 adn I get 'DAta type mismatch in criteria
expression' whe i look for >65

Same prob with the + 6 mo query: once I have found the review date (date 6
months on from the start date), I need to be able to find all the due dates
in a particular month.

thank you so very much for your suggestions, will continue working through
them now. Am rather basic user of Access so words of one syllable
appreciated.
Click to expand...

Please post the VBA of your Age function, and the SQL text of your
query. Also indicate just WHAT is not working. It sounds like you're
putting something in the wrong place.

Just a suggestion: a query like

SELECT [DOB], DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB],
"mmdd") < Format(Date(), "mmdd"), 1, 0) AS Age, [StartDate],
DateAdd("m", 6, [StartDate]) AS DueDate
FROM yourtable
WHERE [Age] > 60
OR DueDate BETWEEN #6/1/05# AND #6/30/05#

should work just fine.

John W. Vinson[MVP]
 
Back
Top