Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    14

    Unanswered: Adding a Calculated Date Field to a Query

    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:

    Code:
    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.
    Click image for larger version. 

Name:	access_calculated_field.JPG 
Views:	6 
Size:	62.7 KB 
ID:	16465

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

    OTHER SPECIES.zip

    Thanks,
    Matthew

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You're trying to use the Field [Shoeing Date] in your expression

    SD:[Shoeing Date] + 42

    but that Field is not included in the Query! And I'm not absolutely sure, but I believe that, in reading the Fields in the Query, from left-to-right, [Shoeing Date] has to be to the left of the Calculated Field.

    You'd probably also do well to use the DateAdd() function:

    SD: DateAdd("d", 42, [Shoeing Date])

    rather than

    SD:[Shoeing Date] + 42

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2010
    Posts
    14
    Hi Linq:

    Thank you very much, your code worked perfectly!

    I appreciate it...

    Matthew

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •