Re: Update Query with User-defined function?

Normally you'd use periods, not exclamation points, in SQL. Try switching
and see if that helps.

"Greg Strong" <NoJunk@NoJunk4U².com> wrote in message
news:5pnqj1llo750vi5p2dd2rjaacrosnfcv7l@xxxxxxxxxx
> 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,tblOrderPrYr_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. 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.
>
> Thanks!
>
> --
> Regards,
>
> Greg Strong


.