469,632 Members | 1,750 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

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,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. 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
Nov 13 '05 #1
6 7483
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:5p********************************@4ax.com...
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. 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

Nov 13 '05 #2
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.

Jerry

Nov 13 '05 #3
On Fri, 30 Sep 2005 09:55:35 -0700, "Bruce Rusk"
<br***************@stanford.edoo> 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
Nov 13 '05 #4
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@NoJunk4UČ.com> wrote in message
news:eu********************************@4ax.com...
On Fri, 30 Sep 2005 09:55:35 -0700, "Bruce Rusk"
<br***************@stanford.edoo> 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

Nov 13 '05 #5
Specifically what field are you trying to Update?

Nov 13 '05 #6
On Fri, 30 Sep 2005 10:48:18 -0700, "Bruce Rusk"
<br***************@stanford.edoo> 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_Setup SET tblOrderPrYr_Setup.EstRev =
RtnRndEstRevPY(tblOrderPrYr_Setup.EstRev,tblOrderP rYr_Setup.ID),
tblOrderPrYr_Setup.EstCGS =
RtnRndEstRevPY(tblOrderPrYr_Setup.EstCGS,tblOrderP rYr_Setup.ID);

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

--
Regards,

Greg Strong
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Kunal | last post: by
15 posts views Thread by Darren | last post: by
reply views Thread by DP | last post: by
4 posts views Thread by jofo | last post: by
1 post views Thread by hmlarson | last post: by
2 posts views Thread by eggie5 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.