Update query with date criteria

Discussion in 'Microsoft Access Queries' started by gillah11, Dec 9, 2009.

  1. gillah11

    gillah11 Guest

    Hi all. I have an update query that should only update a record if today
    falls between the 1st of it's month and the 7th of the following month. For
    instance, if the record date is 11/15/2009, it should only update if today is
    between 11/1/2009 and 12/7/2009, or if the record date is 10/6/2009 is should
    only update if today is between 10/1/2009 and 11/7/2009. Here's the criteria
    I was trying to use, and I was using this criteria in the field that I want
    to update if the criteria is true:

    now()<dateserial(year[ticketcreateddate],month[ticketcreateddate]+1,7)

    Using this, I'm only getting updates on the records created on 12/7/2009.

    Any other ideas?
     
    gillah11, Dec 9, 2009
    #1
    1. Advertisements

  2. gillah11

    John Spencer Guest

    It would help if you posted the SQL of the query you are trying to use.
    (Hint - Menu: View : SQL)

    PERHAPS the following will give you the idea. I'm cannot tell from your post
    what field you are attempting to update and what field you are using to
    determine criteria and whether you are attempting to update more than one field.

    UPDATE [YourTable]
    SET [Some Field] = ??????
    WHERE [Record Date Field] Between DateSerial(Year(Date()),Month(Date()),1)
    and DateSerial(Year(Date()),Month(Date())+1,7)

    The Where condition above identifies which records to update.



    John Spencer
    Access MVP 2002-2005, 2007-2009
    The Hilltop Institute
    University of Maryland Baltimore County

    gillah11 wrote:
    > Hi all. I have an update query that should only update a record if today
    > falls between the 1st of it's month and the 7th of the following month. For
    > instance, if the record date is 11/15/2009, it should only update if today is
    > between 11/1/2009 and 12/7/2009, or if the record date is 10/6/2009 is should
    > only update if today is between 10/1/2009 and 11/7/2009. Here's the criteria
    > I was trying to use, and I was using this criteria in the field that I want
    > to update if the criteria is true:
    >
    > now()<dateserial(year[ticketcreateddate],month[ticketcreateddate]+1,7)
    >
    > Using this, I'm only getting updates on the records created on 12/7/2009.
    >
    > Any other ideas?
     
    John Spencer, Dec 9, 2009
    #2
    1. Advertisements

  3. gillah11

    KARL DEWEY Guest

    Try this --
    WHERE Date() Between DateSerial(Year([ticketcreateddate]),
    Month([ticketcreateddate]), 1) AND DateAdd("m", 1,
    DateSerial(Year([ticketcreateddate]), Month([ticketcreateddate]), 7))

    Or in design view add calculated field like this --
    MyDate: Date()
    Criteria --
    Between DateSerial(Year([ticketcreateddate]), Month([ticketcreateddate]),
    1) AND DateAdd("m", 1, DateSerial(Year([ticketcreateddate]),
    Month([ticketcreateddate]), 7))

    --
    Build a little, test a little.


    "gillah11" wrote:

    > Hi all. I have an update query that should only update a record if today
    > falls between the 1st of it's month and the 7th of the following month. For
    > instance, if the record date is 11/15/2009, it should only update if today is
    > between 11/1/2009 and 12/7/2009, or if the record date is 10/6/2009 is should
    > only update if today is between 10/1/2009 and 11/7/2009. Here's the criteria
    > I was trying to use, and I was using this criteria in the field that I want
    > to update if the criteria is true:
    >
    > now()<dateserial(year[ticketcreateddate],month[ticketcreateddate]+1,7)
    >
    > Using this, I'm only getting updates on the records created on 12/7/2009.
    >
    > Any other ideas?
     
    KARL DEWEY, Dec 9, 2009
    #3
  4. I think that you need a Between statement. The first part sets the date to
    the first of the month. The part after AND add a month and just under 8 days
    to that value. That way if records have a time component, It will still
    select records up to a second before the 8th.

    Between Date() - Day(Date()-1) AND DateAdd("m",1,Date() - Day(Date())+7.99999)

    PS. I bet that there is a nicer way of doing it that all my Date()s.
    --
    Jerry Whittle, Microsoft Access MVP
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "gillah11" wrote:

    > Hi all. I have an update query that should only update a record if today
    > falls between the 1st of it's month and the 7th of the following month. For
    > instance, if the record date is 11/15/2009, it should only update if today is
    > between 11/1/2009 and 12/7/2009, or if the record date is 10/6/2009 is should
    > only update if today is between 10/1/2009 and 11/7/2009. Here's the criteria
    > I was trying to use, and I was using this criteria in the field that I want
    > to update if the criteria is true:
    >
    > now()<dateserial(year[ticketcreateddate],month[ticketcreateddate]+1,7)
    >
    > Using this, I'm only getting updates on the records created on 12/7/2009.
    >
    > Any other ideas?
     
    Jerry Whittle, Dec 9, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dustin Swartz
    Replies:
    1
    Views:
    644
    PC Datasheet
    Jan 25, 2005
  2. Karl Burrows

    Query Date Range Criteria Doesn't Include Last Date in Range

    Karl Burrows, Jun 9, 2005, in forum: Microsoft Access Queries
    Replies:
    6
    Views:
    1,046
    Allen Browne
    Jun 10, 2005
  3. JOJ via AccessMonster.com

    ====> Debug query criteria date field all dates or selected date..URGENT ..

    JOJ via AccessMonster.com, Oct 15, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    264
    JOJ via AccessMonster.com
    Oct 16, 2005
  4. Bob
    Replies:
    13
    Views:
    431
    Dale Fye
    Jun 27, 2007
  5. Albert D. Kallal

    Re: Using Date Criteria with Other Criteria

    Albert D. Kallal, May 4, 2008, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    225
    Albert D. Kallal
    May 4, 2008
Loading...

Share This Page