Adding a Calculated Date Field to a Query

[MFriend]

Hi Folks: I am trying to create a calculated date field.



In the database is a date called "Shoeing Date". The horses need shoeing every 6 weeks so I need to be able to add 6 weeks (42 days) to that date.

Here is the SQL Code so far:

SELECT DISTINCTROW Historical.[Common Name], Max([Hoof Care].[Shoeing Date]) AS [First Of Shoeing Date]
FROM Historical INNER JOIN [Hoof Care] ON Historical.[Animal] = [Hoof Care].[Animal]
GROUP BY Historical.[Common Name]
HAVING (((First([Hoof Care].Farrier))="COATS"))
ORDER BY Historical.[Common Name];

Here is what I am trying to do:
I'm not sure how to word or write the formula to get it to work properly. If I try to add 42 to the current date I get the error below.


I have attached the access file (zipped) if that helps.

Thanks,
Matthew

[ranman256]

no, its this...date add 42 d (days)

=dateadd("d",42,[Shoeing Date]) as SD

[June7]

Why have DISTINCTROW and GROUP BY in the same query?

Day is default unit when using dates in arithmetic.

Try: Max([Shoeing Date] + 42)

[MFriend]

Hi Ranman256 and June7:

I'm a noob when it comes to Access (it's been a year since I tried developing with it). I get the following errors (not sure what I'm doing wrong):

[June7]

I suggested using Max() on the calculated field.

Or do this simple addition calc in textbox on report.

[MFriend]

Hi June:

Did i put it in the wrong box? I tried it a half dozen different ways.

Sorry I'm dense.

Matthew

[June7]

Max is selected from the Total row, just like you did for First Of Shoeing Date. And instead of the default Expr1, type something more informative.

[MFriend]

Thanks June and Ranman.... its working now.
I appreciate both of yours help.

Matthew

[LaughingBull]

Examples that use the current date in their criteria

To include items that ...	Use this criteria	Query result
Contain today's date	Date()	Returns items with a date of today. If today's date is 2/2/2012, you’ll see items where the date field is set to Feb 2, 2012.
Contain yesterday's date	Date()-1	Returns items with yesterday’s date. If today's date is 2/2/2012, you’ll see items for Feb 1, 2012.
Contain tomorrow's date	Date() + 1	Returns items with tomorrow’s date. If today's date is Feb 2, 2012, you’ll see items for Feb 3, 2012.
Contain dates within the current week	DatePart("ww", [SalesDate]) = DatePart("ww", Date()) and Year( [SalesDate]) = Year(Date())	Returns items with dates during the current week. A week in Access starts on Sunday and ends on Saturday.
Contain dates within the previous week	Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1	Returns items with dates during the last week. A week in Access starts on Sunday and ends on Saturday.
Contain dates within the following week	Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1	Returns items with dates during next week. A week in Access starts on Sunday and ends on Saturday.
Contain a date within the last 7 days	Between Date() and Date()-6	Returns items with dates during the last 7 days. If today's date is 2/2/2012, you’ll see items for the period Jan 24, 2012 through Feb 2, 2012.
Contain a date within the current month	Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now())	Returns items with dates in the current month. If today's date is 2/2/2012, you’ll see items for Feb 2012.
Contain a date within the previous month	Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) - 1	Returns items with dates in the previous month. If today's date is 2/2/2012, you’ll see items for Jan 2012.
Contain a date within the next month	Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) + 1	Returns items with dates in the next month. If today's date is 2/2/2012, you’ll see items for Mar 2012.
Contain a date within the last 30 or 31 days	Between Date( ) And DateAdd("M", -1, Date( ))	Returns a month's worth of items. If today's date is 2/2/2012, you’ll see items for the period Jan 2, 2012 to Feb 2, 2012.
Contain a date within the current quarter	Year([SalesDate]) = Year(Now()) And DatePart("q", [SalesDate]) = DatePart("q", Now())	Returns items for the current quarter. If today's date is 2/2/2012, you’ll see items for the first quarter of 2012.
Contain a date within the previous quarter	Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())- 1	Returns items for the previous quarter. If today's date is 2/2/2012, you’ll see items for the last quarter of 2011.
Contain a date within the next quarter	Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())+1	Returns items for the next quarter. If today's date is 2/2/2012, you’ll see items for the second quarter of 2012.
Contain a date within the current year	Year([SalesDate]) = Year(Date())	Returns items for the current year. If today's date is 2/2/2012, you’ll see items for the year 2012.
Contain a date within the previous year	Year([SalesDate]) = Year(Date()) - 1	Returns items for the previous year. If today's date is 2/2/2012, you’ll see items for the year 2011.
Contain a date within the next year	Year([SalesDate]) = Year(Date()) + 1	Returns items with next year's date. If today's date is 2/2/2012, you’ll see items for the year 2013.
Contain a date between Jan 1 and today (year-to-date items)	Year([SalesDate]) = Year(Date()) and Month([SalesDate]) <= Month(Date()) and Day([SalesDate]) <= Day (Date())	Returns items with dates between Jan 1 of the current year and today. If today's date is 2/2/2012, you’ll see items for the period Jan 1, 2012 to 2/2/2012.
Contain a date that occurred in the past	< Date()	Returns items with dates before today.
Contain a date that occurs in the future	> Date()	Returns items with dates after today.