PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 1.00 average.

Update query with date criteria

 
 
gillah11
Guest
Posts: n/a
 
      9th Dec 2009
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?
 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      9th Dec 2009
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?

 
Reply With Quote
 
 
 
 
KARL DEWEY
Guest
Posts: n/a
 
      9th Dec 2009
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?

 
Reply With Quote
 
Jerry Whittle
Guest
Posts: n/a
 
      9th Dec 2009
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?

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
date criteria - data mismatch (aka: When is a date really a date????) Bob Microsoft Access Queries 13 27th Jun 2007 12:23 AM
i have two date fileds Opend date Due date, can i set default on due date so, its always = to open date on my data entry form1 Urgent Mike Saifie Microsoft Access Form Coding 1 9th Mar 2006 02:08 AM
====> Debug query criteria date field all dates or selected date..URGENT .. JOJ via AccessMonster.com Microsoft Access Queries 2 16th Oct 2005 05:15 PM
Query Date Range Criteria Doesn't Include Last Date in Range Karl Burrows Microsoft Access Queries 6 10th Jun 2005 07:24 AM
Run Query from date to date, Print the from date to date in the header of the report? Dustin Swartz Microsoft Access Queries 1 25th Jan 2005 08:06 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:10 AM.