If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Update query with date criteria
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? |
#3
|
|||
|
|||
Update query with date criteria
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? |
#4
|
|||
|
|||
Update query with date criteria
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? |
Thread Tools | |
Display Modes | |
|
|