sikan71
asked on
runsql error
hi,
I have written this code that runs at form close event:
Private Sub Form_Close()
Dim db As Dao.Database
Dim rs As Dao.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAMKCC 71", dbOpenDynaset)
If Weekday(Date) = 7 Then
Dim str As String
str = "SELECT tblAMKCC71.SystemMO FROM tblAMKCC71 WHERE weekday(tdate)=4 And format(tdate,'wwww')=form( date()-6,' wwww');"
DoCmd.RunSQL (str)
Dim strup As String
strup = "UPDATE tblAMKCC71 SET tblAMKCC71.SystemMO = str WHERE (weekday(tdate)=4+1 And format(tdate,'mmwwww')=for mat(date() -5,'mmwwww '))Or (weekday(tdate)=4+2 And format(tdate,'mmwwww')=for mat(date() -4,'mmwwww '))Or (weekday(tdate)=4-1 And format(tdate,'mmwwww')=for mat(date() -7,'mmwwww ')) Or (weekday(tdate)=4-2 And format(tdate,'mmwwww')=for mat(date() -8,'mmwwww ')) Or (weekday(tdate)=4-3 And format(tdate,'mmwwww')=for mat(date() -9,'mmwwww '));"
DoCmd.RunSQL (strup)
End If
End Sub
It given me this error message: run-time error 2342
RunSQL action requires an argument consisting of an SQL statement. I can't figure out any.... Please help.
I have written this code that runs at form close event:
Private Sub Form_Close()
Dim db As Dao.Database
Dim rs As Dao.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAMKCC
If Weekday(Date) = 7 Then
Dim str As String
str = "SELECT tblAMKCC71.SystemMO FROM tblAMKCC71 WHERE weekday(tdate)=4 And format(tdate,'wwww')=form(
DoCmd.RunSQL (str)
Dim strup As String
strup = "UPDATE tblAMKCC71 SET tblAMKCC71.SystemMO = str WHERE (weekday(tdate)=4+1 And format(tdate,'mmwwww')=for
DoCmd.RunSQL (strup)
End If
End Sub
It given me this error message: run-time error 2342
RunSQL action requires an argument consisting of an SQL statement. I can't figure out any.... Please help.
ASKER
I want to select a value from table tblAMKCC71 where weekday is 4 and copy that value into rest of the days of that week from sunday-saturday except wednesday since it already has a value
Rocki
Sorry for cross posting but if you get a minute could you look at cyber's last chain question. The logic requires some kind of reiterative subfunction but because it needs a recordset its blowing the stack.
https://www.experts-exchange.com/questions/22036778/VBA-Fixing-Chains-again.html
Mary
Sorry for cross posting but if you get a minute could you look at cyber's last chain question. The logic requires some kind of reiterative subfunction but because it needs a recordset its blowing the stack.
https://www.experts-exchange.com/questions/22036778/VBA-Fixing-Chains-again.html
Mary
ASKER
Some help will be appreciated.....I am beginner programmer in ACCESS 2003.
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat e)=4 And format(tdate,'wwww')=form( date()-6,' wwww'))
WHERE (weekday(tdate)=4+1 And format(tdate,'mmwwww')=for mat(date() -5,'mmwwww '))Or (weekday(tdate)=4+2 And format(tdate,'mmwwww')=for mat(date() -4,'mmwwww '))Or (weekday(tdate)=4-1 And format(tdate,'mmwwww')=for mat(date() -7,'mmwwww ')) Or (weekday(tdate)=4-2 And format(tdate,'mmwwww')=for mat(date() -8,'mmwwww ')) Or (weekday(tdate)=4-3 And format(tdate,'mmwwww')=for mat(date() -9,'mmwwww '));"
Does this give you the result you want
Remove this bit:
------------------
Dim str As String
str = "SELECT tblAMKCC71.SystemMO FROM tblAMKCC71 WHERE weekday(tdate)=4 And format(tdate,'wwww')=form( date()-6,' wwww');"
DoCmd.RunSQL (str)
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat
WHERE (weekday(tdate)=4+1 And format(tdate,'mmwwww')=for
Does this give you the result you want
Remove this bit:
------------------
Dim str As String
str = "SELECT tblAMKCC71.SystemMO FROM tblAMKCC71 WHERE weekday(tdate)=4 And format(tdate,'wwww')=form(
DoCmd.RunSQL (str)
ASKER
Can someone explain what this query exact do? When I run the query it's saying updating 5 rows. But when I check the table no values are being updated why?
ASKER
what does this query give me:
weekday(tdate)=4 (Return what?)
And format(tdate,'wwww')=form( date()-6,' wwww'))(re turn what?)
weekday(tdate)=4 (Return what?)
And format(tdate,'wwww')=form(
OK
What is the value in tdate
is it a date or a year or what?
What is the value in tdate
is it a date or a year or what?
ASKER
For example: My form is opened on daily basis so the date gets entered into the table on daily basis as well as data. But one field value gets entered only on wednesday.
I have data in the table(tblAMKCC71) in field name SystemMO tdate= 10/25/2006 the value is 228. Now i want to update with same value of wednesday in sunday 22nd-saturday 28th except wednesday since it already has value. This is done on weekly basis that is every saturday it gets updated. The above query doesn't seem to give me the result.
I have data in the table(tblAMKCC71) in field name SystemMO tdate= 10/25/2006 the value is 228. Now i want to update with same value of wednesday in sunday 22nd-saturday 28th except wednesday since it already has value. This is done on weekly basis that is every saturday it gets updated. The above query doesn't seem to give me the result.
ASKER
tdate is month day year example: 10/25/2006
weekday(tdate)=4 this returns 4 for dates where the day is Wednesday
to be honest I didn't look at this I just took it form your code where did you get it.
format(tdate,'wwww')=form( date()-6,' wwww')
more to the point what date or date range are you trying to establish outside of it being wednesday.
If the update you describe is done every saturday for the prior week then you can use the following.
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat e)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4 And (tdate>date()-7);
to be honest I didn't look at this I just took it form your code where did you get it.
format(tdate,'wwww')=form(
more to the point what date or date range are you trying to establish outside of it being wednesday.
If the update you describe is done every saturday for the prior week then you can use the following.
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat
WHERE weekday(tdate)<>4 And (tdate>date()-7);
Hi Mary, will have a look
ASKER
hi,
No, I want update done for the current week(not previous week). My week starts from sunday to saturday. For example, this wednesday one of the field value(SystemMO) will be entered. On saturday I want to run the query to update Sunday-Saturday(except wednesday) to have the value of wednesday. In the table I have data of previous year as well as current year. That's why I think it is important to specify the week otherwise it will pick the data of all wednesdays of the year.
This code was given to me by experts exchange.
format(tdate,'wwww')=forma t(date()-6 ,'wwww')
Thanks
No, I want update done for the current week(not previous week). My week starts from sunday to saturday. For example, this wednesday one of the field value(SystemMO) will be entered. On saturday I want to run the query to update Sunday-Saturday(except wednesday) to have the value of wednesday. In the table I have data of previous year as well as current year. That's why I think it is important to specify the week otherwise it will pick the data of all wednesdays of the year.
This code was given to me by experts exchange.
format(tdate,'wwww')=forma
Thanks
ASKER
Hi,
When i run the query:
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat e)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4 And (tdate>date()-7);
It says 6 rows being updated. But when I go to the table the values are not in the field. Why?
When i run the query:
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat
WHERE weekday(tdate)<>4 And (tdate>date()-7);
It says 6 rows being updated. But when I go to the table the values are not in the field. Why?
Run this select query and see what results you get...
SELECT SystemMO
FROM tblAMKCC71
WHERE weekday(tdate)=4
And (tdate>date()-7);
SELECT SystemMO
FROM tblAMKCC71
WHERE weekday(tdate)=4
And (tdate>date()-7);
ASKER
yes i get value 229 that has tdate of 11/1/2006 which is wednesday of last week. My update works but it puts value in the date 11/2/2006-11/7/2006(except wednesday). I want the values in the table from 10/29/2006(sunday) to 11/4/2006(saturday) except wednesday. Is this query based on that i should run it on saturday to get the right result.
ASKER
Is it possible to update more than one field in the table with Dlookup.
Example: i want to update SystemMO as well as SystemGP in table tblAMKCC71
Is this statement possible:
update tbleAMKCC71 set SystemMO, SystemGP = Dlookup("[SystemMO]", "[SystemGP]","tblAMKCC71", "weekday(tdate)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4 And (tdate>date()-7);
Example: i want to update SystemMO as well as SystemGP in table tblAMKCC71
Is this statement possible:
update tbleAMKCC71 set SystemMO, SystemGP = Dlookup("[SystemMO]", "[SystemGP]","tblAMKCC71",
WHERE weekday(tdate)<>4 And (tdate>date()-7);
ASKER
I have increased my points to 250. Any help appreciated.
The above only works if run on the Sunday.
You'll need something like this to decide what day the current date is:
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat e)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4
And (IIf(Weekday(tdate)=7,(tda te)>date() -6),
IIf(Weekday(tdate)=6,(tdat e) BETWEEN date()-12 AND date()-5),
IIf(Weekday(tdate)=5,(tdat e) BETWEEN date()-11 AND date()-4),
IIf(Weekday(tdate)=4,(tdat e) BETWEEN date()-10 AND date()-3),
IIf(Weekday(tdate)=3,(tdat e) BETWEEN date()-9 AND date()-2),
IIf(Weekday(tdate)=2,(tdat e) BETWEEN date()-8 AND date()-1),
IIf(Weekday(tdate)=1,(tdat e) BETWEEN date()-7 AND date()))))))));
I think I have the bracket count right, if you have any problems try adding or deleting a closing bracket at the end.
You'll need something like this to decide what day the current date is:
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdat
WHERE weekday(tdate)<>4
And (IIf(Weekday(tdate)=7,(tda
IIf(Weekday(tdate)=6,(tdat
IIf(Weekday(tdate)=5,(tdat
IIf(Weekday(tdate)=4,(tdat
IIf(Weekday(tdate)=3,(tdat
IIf(Weekday(tdate)=2,(tdat
IIf(Weekday(tdate)=1,(tdat
I think I have the bracket count right, if you have any problems try adding or deleting a closing bracket at the end.
ASKER
Thank you it works. But one more question:
Is it possible to update more than one field in the table with Dlookup.
Example: i want to update SystemMO as well as SystemGP in table tblAMKCC71
Is this statement possible:
update tbleAMKCC71 set SystemMO, SystemGP = Dlookup("[SystemMO]", "[SystemGP]","tblAMKCC71", "weekday(tdate)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4 And (tdate>date()-7);
Is it possible to update more than one field in the table with Dlookup.
Example: i want to update SystemMO as well as SystemGP in table tblAMKCC71
Is this statement possible:
update tbleAMKCC71 set SystemMO, SystemGP = Dlookup("[SystemMO]", "[SystemGP]","tblAMKCC71",
WHERE weekday(tdate)<>4 And (tdate>date()-7);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
U cannot use SELECT statements in DoCmd.RunSQL
they are used for action queries only, add/update/del