Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 28th, 2008, 04:12 PM
Friend of Wrox
 
Join Date: Mar 2004
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'

 
Old February 28th, 2008, 04:19 PM
Friend of Wrox
 
Join Date: Mar 2004
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...

 
Old February 29th, 2008, 08:55 AM
Friend of Wrox
 
Join Date: Mar 2004
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
 
Old February 29th, 2008, 12:13 PM
Friend of Wrox
 
Join Date: Mar 2004
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);



 
Old February 29th, 2008, 12:50 PM
Friend of Wrox
 
Join Date: Mar 2004
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?






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





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