Update Query with User-defined function?

[Greg Strong]

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_Se tup.ID,
RtnRndEstRevPY( tblOrderPrYr_Se tup!EstRev,tblO rderPrYr_Setup! ID)
AS RevisedRev,
RtnRndEstRevPY([tblOrderPrYr_Se tup]![EstCGS],[tblOrderPrYr_Se tup]![ID])
AS RevisedCGS
FROM tblOrderPrYr_Se tup;
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_Se tup]![EstCGS],[tblOrderPrYr_Se tup]![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(dblEstRev PY * ((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

[Bruce Rusk]

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

"Greg Strong" <NoJunk@NoJunk4 U².com> wrote in message
news:5p******** *************** *********@4ax.c om...

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_Se tup.ID,
RtnRndEstRevPY( tblOrderPrYr_Se tup!EstRev,tblO rderPrYr_Setup! ID)
AS RevisedRev,
RtnRndEstRevPY([tblOrderPrYr_Se tup]![EstCGS],[tblOrderPrYr_Se tup]![ID])
AS RevisedCGS
FROM tblOrderPrYr_Se tup;
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_Se tup]![EstCGS],[tblOrderPrYr_Se tup]![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(dblEstRev PY * ((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

[Jerry Porter]

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

Jerry

[Greg Strong]

On Fri, 30 Sep 2005 09:55:35 -0700, "Bruce Rusk"
<br************ ***@stanford.ed oo> wrote:

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

No. I tried replacing "!" with "." and also removing the brackets in
Access's GUI query builder. After I leave the field Access puts the
brackets back, and I get the same error message except the exclamation
points are replaced with the periods. Thanks for the suggestion.

--
Regards,

Greg Strong

[Bruce Rusk]

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

"Greg Strong" <NoJunk@NoJunk4 U².com> wrote in message
news:eu******** *************** *********@4ax.c om...

On Fri, 30 Sep 2005 09:55:35 -0700, "Bruce Rusk"
<br************ ***@stanford.ed oo> wrote:

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

No. I tried replacing "!" with "." and also removing the brackets in
Access's GUI query builder. After I leave the field Access puts the
brackets back, and I get the same error message except the exclamation
points are replaced with the periods. Thanks for the suggestion.

--
Regards,

Greg Strong

[SilvrT]

Specifically what field are you trying to Update?

[Greg Strong]

On Fri, 30 Sep 2005 10:48:18 -0700, "Bruce Rusk"
<br************ ***@stanford.ed oo> wrote:

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

This approach help fix the problem. I also changed the function back to
the original using a double and long variables to pass. Solution was:

SQL update query:
UPDATE tblOrderPrYr_Se tup SET tblOrderPrYr_Se tup.EstRev =
RtnRndEstRevPY( tblOrderPrYr_Se tup.EstRev,tblO rderPrYr_Setup. ID),
tblOrderPrYr_Se tup.EstCGS =
RtnRndEstRevPY( tblOrderPrYr_Se tup.EstCGS,tblO rderPrYr_Setup. ID);

User-defined function:
Function RtnRndEstRevPY( dblEstRevPY As Double, lngID As Double)
'Randomize 'initialize random number generator
dblEstRevPY = Round(dblEstRev PY * ((0.94 - 0.9) * Rnd(lngID) + 0.9),
0)
RtnRndEstRevPY = dblEstRevPY
End Function
Thanks for the help!

--
Regards,

Greg Strong