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

    Unanswered: Using Date in Query (to same month)

    I have a query that selects records within 5 years as below;

    Code:
    >=Now()-1825
    My question is how do I select 5 years to the same month as today?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can you explain again - i don't understand
    select 5 years to the same month as today
    now is 27 April'07
    you want 1 April'02 to 30 April'07 or ?????????

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    duh!
    1 April'03 to 30 april'07
    currently using SS 2008R2

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you should be able to build a new date using the date functions...
    something like

    <mynewdatevar> = cdate(format(now(),"dd/mmm/") & year(now())-5)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    yes thats correct (1 April'03 to 30 april'07)

    it has to be within a query, so would the <mynewdatevar> work ?

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    FYI this is my query code;

    Code:
    SELECT TBL_Wills.ID, TBL_Wills.Active, TBL_Wills.[Will date], TBL_Wills.[Codicil Date], 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.StaffRelative, TBL_Wills.ExecutorOther, TBL_Wills.ExecutorPalmers, TBL_Wills.ExecutorBoth, 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
    FROM TBL_Wills
    WHERE (((TBL_Wills.Active)=True) AND ((TBL_Wills.[Will date])>=Now()-1825) AND ((TBL_Wills.[Codicil Date])>=Now()-1825));

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by NeilMansell
    yes thats correct (1 April'03 to 30 april'07)

    it has to be within a query, so would the <mynewdatevar> work ?
    ..... I dunno.. why dont you try it and see


    if you want to use the expression in a query then you may have to tweak it...


    where <mydatecolumn> >= cdate("01-" & month(now()) & "-" year(now()-5) and
    and <mydatecolumn>) <= now()
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Perhaps this will do it

    WHERE (((TBL_Wills.Active)=True) AND ((TBL_Wills.[Will date])>=DateAdd("yyyy", -5, Date)) AND ((TBL_Wills.[Codicil Date])>=DateAdd("yyyy", -5, Date));

    ??

    If you are using Query designer then the date fuction should be Date() !!??



    MTB

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    that works but can you explain the date() part of your reply.

    Asd I can't change it to Date() using Query Designer

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Wel, if I use Date or Date() in VBA it returns to-days date, BUT if I use Date in the query designer criteria field, it gets changes to a literal "Date" (with the quotes), however if I use Date() in the query desinger criteria it returns to-days date!!??

    So how you mange to use just Date in the qury desinger I do not know, perhaps some out there does know ?


    MTB

Posting Permissions

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