Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Working out Date from Month/Year (DATEADD)

    I am having problems with the DATADD command.
    I have a WillDate Field and an expression in SQL/Query named MONTH

    I run the following:
    Month(DateAdd("yyyy",-3,Now()))

    and it returns all August (as we are in August currently) for ALL years.

    How can I limit this list to only current month for the past 3 years only (eg: only 2007)

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You are deducting three years from today. August three years ago was (drum role) August.

    What do you want you expression to return?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    it is returning all the years to august, up to 1973? I need to only include the last 3 years based on the month selected, in this case August (now)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - now this makes no sense.

    Can you include the entire SQL statement please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To speed things up:
    You are using this in a WHERE clause filtering against WillDate - correct?
    And you want to filter WillDate so that it returns the last three years only, starting on the beginning of the current month (so if run for today, you want all rows where WillDate is greater than or equal to 01 August 2007) - correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    yes that is correct.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Right - so we can build this up:
    First, how many months between now and an arbitrary date (important thing it is the first day of the month):
    Code:
    ?DATEDIFF("m", #1970/01/01#, NOW())
    Once we know that we can add that many months to our arbitrary date and vwala we have the first day of this month:
    Code:
    ?DATEADD("m", DATEDIFF("m", #1970/01/01#, NOW()), #1970/01/01#)
    Now all we need do is knock three years off and we get (for today) 2007-08-01.
    Code:
    ?DATEADD("yyyy", -3, DATEADD("m", DATEDIFF("m", #1970/01/01#, NOW()), #1970/01/01#))
    Just filter on >= and you are done.
    Last edited by pootle flump; 08-04-10 at 12:59. Reason: typo
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, slightly shorter (though not necessarily simpler):
    Code:
    ?DATEADD("m", DATEDIFF("m", #1970/01/01#, NOW()) - (3*12), #1970/01/01#)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    thanks, that helped.

    Now I just have the problem that the dates are filtering as they should but when I add a new tick box selection (say for only dead wills) then it doesn't work

  10. #10
    Join Date
    Mar 2004
    Posts
    287
    Right, it isn't working again

    I have the WillData and CodicilDate working fine so it selects from a list that sets the year and month (found this an easier methodmin the end).

    But when I add say another tick box criteria that is selectable by the user, for example 'Dead Files' then it doesn't show the appropriate WillDate AND only those with DEAD Files (if ticked by user).

    See query code (VBA/SQL) below. Any suggestions/help would be very much appreciated at this time

    Code:
    SELECT TBL_Wills.ID, TBL_Wills.[Will date], Month([Will Date]) AS Expr1, Year([Will Date]) AS Expr2, TBL_Wills.[Codicil Date], Month([Codicil Date]) AS Expr3, Year([Codicil Date]) AS Expr4, TBL_Wills.Dead, TBL_Wills.MailshotInclude, TBL_Wills.StaffRelative, TBL_Wills.Certainty, TBL_Wills.NRB, TBL_Wills.OG, TBL_Wills.ExecutorOther, TBL_Wills.ExecutorPalmers, TBL_Wills.ExecutorBoth, TBL_Wills.Surname, TBL_Wills.Forename, TBL_Wills.Initials, TBL_Wills.Title, TBL_Wills.Address1, TBL_Wills.Address2, TBL_Wills.Address3, TBL_Wills.Address4, TBL_Wills.Postcode, TBL_Wills.[Will date 2], TBL_Wills.[Will date notes], TBL_Wills.[Codicil Date 2], TBL_Wills.[Codicil Notes], TBL_Wills.[Executor name], TBL_Wills.[Executor Details], TBL_Wills.[Old Will Revoked], TBL_Wills.[Old Will Revoked 2], TBL_Wills.Notes, TBL_Wills.Benfleet, TBL_Wills.MichaelThomas, TBL_Wills.LargeWill, TBL_Wills.[Will taken by], TBL_Wills.[Date Will taken], TBL_Wills.[Reason Will taken], TBL_Wills.Comments, TBL_Wills.EvolutionCase, TBL_Wills.[Client No in evolution], TBL_Wills.Mailshot, TBL_Wills.Response, TBL_Wills.Location, TBL_Wills.DeadComments, TBL_Wills.Inheritance, TBL_Wills.Email
    FROM TBL_Wills
    WHERE (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected] And (Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will1] And (Year([Will Date]))=[Forms]![LetterInputMailshot]![Will3])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will4])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will5])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will10])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will15])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will20])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected] And (Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will25] And (Year([Will Date]))=[Forms]![LetterInputMailshot]![Will30])) OR (((Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil1])) OR (((Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil3])) OR (((Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil5])) OR (((Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected] And (Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil10] And (Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil15])) OR (((Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil20])) OR (((Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil25])) OR (((Month([Codicil Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Codicil Date]))=[Forms]![LetterInputMailshot]![Codicil30])) AND (((TBL_Wills.MailshotInclude)=[Forms]![LetterInputMailshot]![TickMailshot]));

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    UPDATE:

    Let me just add that the WillDate selections all work when they are by their self.
    The other tick boxes all work OK when they are all by themselves) using AND between

    But when I put the WillDate selection with the tick boxes that when I have problems.

    See code below for Working WillDate:
    Code:
    WHERE (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will1])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will3])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will4])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will5])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will10])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will15])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will20])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will25])) OR (((Month([Will Date]))=[Forms]![LetterInputMailshot]![MonthSelected]) AND ((Year([Will Date]))=[Forms]![LetterInputMailshot]![Will30]));
    And the code for the Tick Boxes ALL WORKING (without WillDate Selection):
    Code:
    WHERE (((TBL_Wills.Dead)=[Forms]![LetterInputMailshot]![TickDead]) AND ((TBL_Wills.MailshotInclude)=[Forms]![LetterInputMailshot]![TickMailshot]) AND ((TBL_Wills.StaffRelative)=[Forms]![LetterInputMailshot]![TickStaff]) AND ((TBL_Wills.Certainty)=[Forms]![LetterInputMailshot]![TickCertainty]) AND ((TBL_Wills.ExecutorOther)=[Forms]![LetterInputMailshot]![TickExecOther]) AND ((TBL_Wills.ExecutorPalmers)=[Forms]![LetterInputMailshot]![TickExecPalmers]) AND ((TBL_Wills.ExecutorBoth)=[Forms]![LetterInputMailshot]![TickExecBoth]));

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This isn't your fault, but I'm afraid I can't help with the dog's dinner that Access has spit out. To really see what is going on I would need to remove all the egregious and extraneous brackets (parentheses) that Access insists on stuffing in there and then formatting the SQL to make it readable.

    I'm afraid I don't have time.

    For me to help you would need to do one of two things:
    1) Remove all the brackets and format the SQL nicely
    2) Cut the example down to a much more manageable WHERE clause
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2004
    Posts
    287
    i understand. thanks anyway.
    i actually started doing that very thing and typing it as SQL code first then adding it to the query rather than Access create it. So far I have it 80% working. I know its just an OR or AND label and a bracket somewhere.

    Thanks anyway.

Posting Permissions

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