Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

runsql error

Posted on 2006-10-28
21
Medium Priority
?
412 Views
Last Modified: 2008-02-01
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("tblAMKCC71", 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')=format(date()-5,'mmwwww'))Or (weekday(tdate)=4+2 And format(tdate,'mmwwww')=format(date()-4,'mmwwww'))Or (weekday(tdate)=4-1 And format(tdate,'mmwwww')=format(date()-7,'mmwwww')) Or (weekday(tdate)=4-2 And format(tdate,'mmwwww')=format(date()-8,'mmwwww')) Or (weekday(tdate)=4-3 And format(tdate,'mmwwww')=format(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.
0
Comment
Question by:sikan71
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
  • 2
21 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17825871
What do u want to do with the SELECT statement?

U cannot use SELECT statements in DoCmd.RunSQL

they are used for action queries only, add/update/del
0
 

Author Comment

by:sikan71
ID: 17825889
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
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17826012
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.

http://www.experts-exchange.com/Databases/MS_Access/Q_22036778.html

Mary
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:sikan71
ID: 17826141
Some help will be appreciated.....I am beginner programmer in ACCESS 2003.
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17826281
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdate)=4 And format(tdate,'wwww')=form(date()-6,'wwww'))
WHERE (weekday(tdate)=4+1 And format(tdate,'mmwwww')=format(date()-5,'mmwwww'))Or (weekday(tdate)=4+2 And format(tdate,'mmwwww')=format(date()-4,'mmwwww'))Or (weekday(tdate)=4-1 And format(tdate,'mmwwww')=format(date()-7,'mmwwww')) Or (weekday(tdate)=4-2 And format(tdate,'mmwwww')=format(date()-8,'mmwwww')) Or (weekday(tdate)=4-3 And format(tdate,'mmwwww')=format(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)
0
 

Author Comment

by:sikan71
ID: 17831769
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?
0
 

Author Comment

by:sikan71
ID: 17831773
what does this query give me:
weekday(tdate)=4 (Return what?)
And format(tdate,'wwww')=form(date()-6,'wwww'))(return what?)
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17831779
OK

What is the value in tdate

is it a date or a year or what?
0
 

Author Comment

by:sikan71
ID: 17831793
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.  
0
 

Author Comment

by:sikan71
ID: 17831795
tdate is month day year example: 10/25/2006
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17831849
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(tdate)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4 And (tdate>date()-7);




0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17832586
Hi Mary, will have a look
0
 

Author Comment

by:sikan71
ID: 17889957
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')=format(date()-6,'wwww')
Thanks
0
 

Author Comment

by:sikan71
ID: 17890083
Hi,

When i run the query:
UPDATE tblAMKCC71
SET tblAMKCC71.SystemMO = Dlookup("[SystemMO]", "tblAMKCC71","weekday(tdate)=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?
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17894375
Run this select query and see what results you get...

SELECT SystemMO
FROM tblAMKCC71
WHERE weekday(tdate)=4
And (tdate>date()-7);
0
 

Author Comment

by:sikan71
ID: 17894691
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.  
0
 

Author Comment

by:sikan71
ID: 17894758
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);



0
 

Author Comment

by:sikan71
ID: 17894772
I have increased my points to 250.  Any help appreciated.
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17894953
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(tdate)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4
And (IIf(Weekday(tdate)=7,(tdate)>date()-6),
IIf(Weekday(tdate)=6,(tdate) BETWEEN date()-12 AND date()-5),
IIf(Weekday(tdate)=5,(tdate) BETWEEN date()-11 AND date()-4),
IIf(Weekday(tdate)=4,(tdate) BETWEEN date()-10 AND date()-3),
IIf(Weekday(tdate)=3,(tdate) BETWEEN date()-9 AND date()-2),
IIf(Weekday(tdate)=2,(tdate) BETWEEN date()-8 AND date()-1),
IIf(Weekday(tdate)=1,(tdate) 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.

0
 

Author Comment

by:sikan71
ID: 17934403
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);
0
 
LVL 9

Accepted Solution

by:
mpmccarthy earned 1000 total points
ID: 17935049
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);

That won't work ... but this might.

update tbleAMKCC71
set SystemMO = Dlookup("[SystemMO]", "tblAMKCC71", "weekday(tdate)=4 And (tdate>date()-7"))
AND SystemGP = Dlookup("[SystemGP]","tblAMKCC71", "weekday(tdate)=4 And (tdate>date()-7"))
WHERE weekday(tdate)<>4 And (tdate>date()-7);
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question