Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 28th, 2008, 04:12 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default Operation must use an updateable query

Hi,

I'm running the following query, or rather trying... and I keep getting the error 'Operation must use an updateable query', and I can't for the life of me figure out why...

UPDATE TrackingTable SET January = (SELECT COUNT(*) AS Total FROM MigTable WHERE CostSplit = 'Churn' AND Format(ActionDt,'mmmm') = 'January' AND Format(ActionDt,'yyyy') = '2008') WHERE Initiative = 'MigInit'

Reply With Quote
  #2 (permalink)  
Old February 28th, 2008, 04:19 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also as a note, I am trying to use this from within Access itself... I'm not using ASP or anything like that... I've read a few articles that refer to to security settings with the IUSER login... but that shouldn't be my case...

Reply With Quote
  #3 (permalink)  
Old February 29th, 2008, 08:55 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Currently:

UPDATE TrackingTable
   SET January =
     (SELECT COUNT(*) AS Total
      FROM MigTable
      WHERE CostSplit = 'Churn'
      AND Format(ActionDt,'mmmm') = 'January'
      AND Format(ActionDt,'yyyy') = '2008')
   WHERE Initiative = 'MigInit'

Can you do this instead:

SELECT COUNT(*) AS Total
 FROM MigTable
 WHERE CostSplit = 'Churn'
   AND Format(ActionDt,'mmmm') = 'January'
   AND Format(ActionDt,'yyyy') = '2008'

Then refer to the results of this query for the second one:

UPDATE TrackingTable
 SET January = (Total value here)
 WHERE Initiative = 'MigInit'

You have several methods to do this, including creating the first query with a reference to controls on the form so they can take parameters directly from the form, and then creating an update query that references the first query. You can also do all of this with code.

Two things: Total is a reserved word, so not a good name for a field alias. Also, is '2008' a string or integer?

Another issue is that it is problematic doing value transactions in the middle of an executing SQL string in Access. Is there another way to get this data before you build the string:

Format(ActionDt,'mmmm')
Format(ActionDt,'yyyy')

Did any of that help?





mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #4 (permalink)  
Old February 29th, 2008, 12:13 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Its just odd that Microsoft access does not allow me to run this query in 1 shot... I've tried this in other mediums without any issue...

As to the Format(ActionDt,'yyyy')... what is so wrong with using that?

In the end this is how it will be queried...
AND Format(SubmitDt,"mmmm") = (SELECT Month FROM DtRange) AND Format(SubmitDt,"yyyy") = (SELECT Year FROM DtRange);



Reply With Quote
  #5 (permalink)  
Old February 29th, 2008, 12:50 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, how am I suppose to reference the Total value without doing a select of the value from the first table?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Operation must use an updateable query sandhya12_rcnair ASP.NET 2.0 Professional 3 November 11th, 2008 11:56 AM
Operation must use an updateable query hchlebowski ASP.NET 1.0 and 1.1 Basics 1 January 24th, 2006 07:11 AM
Operation must use an updateable query. Laindir Sarnon Classic ASP Databases 6 September 2nd, 2004 04:27 PM
Operation must use an updateable query chaos2003 Classic ASP Databases 4 August 22nd, 2003 03:04 PM



All times are GMT -4. The time now is 07:14 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.