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'

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...

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

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);

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