If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
date difference expression in query
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 |
#2
|
|||
|
|||
On Wed, 22 Jun 2005 13:17:03 -0700, "D"
wrote: 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. 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'. 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. 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] |
#3
|
|||
|
|||
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" wrote: On Wed, 22 Jun 2005 13:17:03 -0700, "D" wrote: 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. 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'. 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. 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] |
#4
|
|||
|
|||
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! "D" wrote: 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 |
#5
|
|||
|
|||
On Wed, 22 Jun 2005 15:17:02 -0700, "D"
wrote: 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. 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unable to open query with Complex Expression | Lex | Running & Setting Up Queries | 1 | April 2nd, 2005 04:44 PM |
How to pass date parameter to query via VBA ? | Kalle via AccessMonster.com | Running & Setting Up Queries | 2 | January 26th, 2005 06:55 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |