Update query with date criteria

[gillah11]

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]

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

 

[KARL DEWEY]

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))

 

[Jerry Whittle]

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.