Update Query with User-defined function?

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
.

Relevant Pages

  • RE: Calculation in a subform
    ... Public Function CalcWeeks(D_Start As Variant, ... You could thus use it to create a calculated column in the query that is ... I tried to use my query as my record source for the subform ... individual may spend 52 weeks in the Sales Department, ...
    (microsoft.public.access.forms)
  • Re: Trap Combo-Box error
    ... bound to a field in the query that is the form's recordsource. ... > The combo box is bound to the form's record source and there is not any ... >>> variable that is not a Variant data Type". ... >>> You tried to assign a Null value to a variable that is not a Variant ...
    (microsoft.public.access.formscoding)
  • Re: Passing null values to a Function
    ... then I use a union query to base the report on. ... Public Function UnitStatus(strUnit As String, strLTResult As Variant, ... UnitStatus = "Test Pending" ...
    (microsoft.public.access.modulesdaovba)
  • Re: Calling a Function in a Query and Form
    ... adding on to what Tom wrote... ... You can use the Nz function to return zero, a zero-length string, or another specified value when a Variant is Null. ... Optional (unless used in a query). ... The DateDiff function can be used to specify what time increment you want returned when you subtract dates ...
    (microsoft.public.access.modulesdaovba)
  • RE: Help with carry over a value
    ... Create an Append query, depending on your needs. ... Public Function CarryOverWt(varNewWt as Variant) as Variant ... Static varOldWt as Variant ... Public Function CarryOverDoseas Variant ...
    (microsoft.public.access.formscoding)