Russ-
It's not clear what it is you're trying to do.
Here's what's in your table:
Timecheck s1 s2 s3 s4 s5 w1 w2 w3
w4 w5
Test1 6/2/2013 6/9/2013 6/16/2013 6/23/2013
6/30/2013 5/29/2013 6/5/2013 6/12/2013 6/19/2013
6/26/2013
Here's the SQL for the first query:
UPDATE Table1 SET Table1.s1 =
DateAdd("d",(8+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,0
))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date(
)),Month(Date())+1,1)), Table1.s2 =
DateAdd("d",(15+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1)), Table1.s3 =
DateAdd("d",(22+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1)), Table1.s4 =
DateAdd("d",(29+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1)), Table1.s5 =
DateAdd("d",(36+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1)), Table1.w1 =
DateAdd("d",(4+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,0
))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date(
)),Month(Date())+1,1)), Table1.w2 =
DateAdd("d",(11+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1)), Table1.w3 =
DateAdd("d",(18+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1)), Table1.w4 =
DateAdd("d",(25+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1)), Table1.w5 =
DateAdd("d",(32+IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,7,
0))-Weekday(DateSerial(Year(Date()),Month(Date())+1,1)),DateSerial(Year(Date
()),Month(Date())+1,1));
And here's what it returns:
S1 S2 S3 S4 S5 W1 W2 W3 W4 W5
4/7/2013 4/14/2013 4/21/2013 4/28/2013 5/5/2013
4/3/2013 4/10/2013 4/17/2013 4/24/2013 5/1/2013
Here's the second query:
UPDATE Table1 SET Table1.s1 =
DateSerial(Year(Date()),Month(Date())+1,1)+7-Weekday(DateSerial(Year(Date())
,Month(Date())+1,1),2), Table1.s2 =
DateSerial(Year(Date()),Month(Date())+1,1)+14-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2), Table1.s3 =
DateSerial(Year(Date()),Month(Date())+1,1)+21-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2), Table1.s4 =
DateSerial(Year(Date()),Month(Date())+1,1)+28-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2), Table1.s5 =
DateSerial(Year(Date()),Month(Date())+1,1)+35-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2), Table1.w1 =
DateSerial(Year(Date()),Month(Date())+1,1)+3-Weekday(DateSerial(Year(Date())
,Month(Date())+1,1),2), Table1.w2 =
DateSerial(Year(Date()),Month(Date())+1,1)+10-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2), Table1.w3 =
DateSerial(Year(Date()),Month(Date())+1,1)+17-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2), Table1.w4 =
DateSerial(Year(Date()),Month(Date())+1,1)+24-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2), Table1.w5 =
DateSerial(Year(Date()),Month(Date())+1,1)+31-Weekday(DateSerial(Year(Date()
),Month(Date())+1,1),2);
.. and it returns:
S1 S2 S3 S4 S5 W1 W2 W3 W4 W5
4/7/2013 4/14/2013 4/21/2013 4/28/2013 5/5/2013
4/3/2013 4/10/2013 4/17/2013 4/24/2013 5/1/2013
Note that what I'm showing above is the result of the expressions, not the
original contents of the table.
Please explain what each calculation should yield based on today's date.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ghsclass65
Sent: Thursday, March 28, 2013 4:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] updating date fields
I'm trying to build a formula for some queries that will update a date field
to dates of certain days of the following month. I've uploaded a file called
UpdatingDates.zip that can test out various formulas. There are two update
queries in the db that when run will update the date fields of the test
table. Everything works fine until I change my system date to that of May,
and then try to have the queries return the dates in June of this year. Both
formulas stumble and don't return the right dates. Anyone have any ideas to
improve this?
Russ
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar