Hello All, In an 'Update' query can you use user-defined functions in the 'Update To' for the query? Either no or I'm missing something. See the additional info below. If I run a query as a select query it runs fine. The select query is as follows: SELECT tblOrderPrYr_Setup.ID, RtnRndEstRevPY(tblOrderPrYr_Setup!EstRev,tblOrderP rYr_Setup!ID) AS RevisedRev, RtnRndEstRevPY([tblOrderPrYr_Setup]![EstCGS],[tblOrderPrYr_Setup]![ID]) AS RevisedCGS FROM tblOrderPrYr_Setup; If I change it to an update query on and either try to run it, save it, or open into SQL I get an error message which reads: 'RtnRndEstRevPY([tblOrderPrYr_Setup]![EstCGS],[tblOrderPrYr_Setup]![ID])' is not a valid name. Find invalid objects in your databases DBA has a number of duties that are primarily targeted at supporting of database performance capabilities and data consistency. The administrator can use the CHECKDB command to easily verify the data consistency; however, in case they need to find an invalid object in a database schema, some difficulties. Make sure that it does not include any invalid characters or punctuation and that it is not too long. The custom function is as follows: Function RtnRndEstRevPY(dblEstRevPY As Variant, lngID As Variant) 'Randomize 'initialize random number generator dblEstRevPY = Round(dblEstRevPY * ((0.94 - 0.9) * Rnd(lngID) + 0.9), 0) RtnRndEstRevPY = dblEstRevPY End Function I originally had dblEstRevPY as a double and lngID as a long integer, but I have changed them to variant. Still same error. It appears Access does not see that I'm calling the 2 expressions RevisedRev and RevisedCGS, respectively, when I change it to an update query. -- Regards, Greg Strong. Normally you'd use periods, not exclamation points, in SQL. Try switching and see if that helps. 'Greg Strong' wrote in message news:5p********************************@4ax.com. ![]() Make sure that it does not include any invalid characters or punctuation and that it is not too long. The custom function is as follows: Function RtnRndEstRevPY(dblEstRevPY As Variant, lngID As Variant) 'Randomize 'initialize random number generator dblEstRevPY = Round(dblEstRevPY * ((0.94 - 0.9) * Rnd(lngID) + 0.9), 0) RtnRndEstRevPY = dblEstRevPY End Function I originally had dblEstRevPY as a double and lngID as a long integer, but I have changed them to variant. Still same error. Gta vice city free download torrent. It appears Access does not see that I'm calling the 2 expressions RevisedRev and RevisedCGS, respectively, when I change it to an update query. -- Regards, Greg Strong. Hi Greg, You certainly can use functions in the Update To of a query. The standard syntax for referring to a field in a tables is using a period instead of!: tblOrderPrYr_Setup.EstRev I don't know if this is the source of your problem. Actually, in this case, since you only have one table in your From clause, you don't need to refer to the table: RtnRndEstRevPY(EstRev,ID). If these changes don't help, I suggest you pare your query down to the simplest components that demonstrate your problem, and then post the SQL of the update query. You could try: (a) to write the update query from scratch in SQL or (B) - Write a simpler select query, with the same fields but without the function (e.g., instead of the function just add or concatenate the two fields) - Change that simpler query to an update - Add the function to the resulting Update query Also, it's a good idea to specify that your function returns a long (or other data type).
0 Comments
Leave a Reply. |