Update query with date criteria

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?

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?

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?

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?