Select query that won't run as Update query?

[Greg Strong]

Hello All,

Any reason why a select query that runs will not run as an update
query?

What I've done is created a select query that runs successfully. The
query has several custom functions to create data. When I attempt to
run the query as an update query I receive an error message which
reads:

'RetnCreateInvPd([tblInvCurYr]![ID],[tblInvCurYr]![PdNewOrd])' is not
a valid name. Make sure that it does not include invalid characters or
punctuation and that it is not too long.

The select query is below. Any ideals? TIA!

Select Query:

SELECT tblInvCurYr.ID,
RetnCreateInvPd([tblInvCurYr]![ID],[tblInvCurYr]![PdNewOrd]) AS
CalcInvPd, RetnRndInvNo([tblInvCurYr]![ID]) AS RdmInvNo,
RetnInvAmt([tblInvCurYr]![ID],[tblInvCurYr]![EstRev]) AS CalcInvAmt,
Round([CalcInvAmt]*ReturnRndCGSPerc([tblInvCurYr]![ID]),0) AS
CalcInvCGS FROM tblInvCurYr;

--
Regards,

Greg Strong

[Greg Strong]

On Fri, 23 Sep 2005 19:19:52 GMT, Greg Strong <NoJunk@NoJunk4U².com>
wrote:

Any ideals?

Yes run the whole thing in code calling the functions that I created.
It takes less than 25 seconds to update over 181k records.

Function SetUpInvData()

Randomize 'Initialize random number generator

Dim strInvPd As String
Dim lngInvNo As Long, lngID As Long, lngIDno As Long, lngInvAmt As
Long, _
lngInvCGS As Long

Dim dteStartTime As Date

Dim db As Database
Dim rstInvData As Recordset

Set db = CurrentDb()
Set rstInvData = db.OpenRecordset("tblInvCurYr", DB_OPEN_DYNASET)

'record start time
dteStartTime = Timer

rstInvData.MoveFirst
Do While Not rstInvData.EOF
'edit data
rstInvData.Edit

'set up invoice pd
lngID = rstInvData!ID
lngIDno = rstInvData!ID
strInvPd = rstInvData!PdNewOrd
strInvPd = RetnCreateInvPd(lngID, strInvPd)
rstInvData!InvPd = strInvPd

'set up invoice number
lngInvNo = RetnRndInvNo(lngID)
rstInvData!InvNo = lngInvNo

'set up invoice amount
lngInvAmt = rstInvData!EstRev
lngInvAmt = RetnInvAmt(lngIDno, lngInvAmt)
rstInvData!InvAmt = lngInvAmt

'set up invoice CGS
lngInvCGS = lngInvAmt * ReturnRndCGSPerc(CDbl(lngID))
rstInvData!InvCGS = lngInvCGS

'update data
rstInvData.Update

rstInvData.MoveNext
Loop

rstInvData.Close

MsgBox Format(Timer - dteStartTime, "00.00") & " seconds"

End Function
Sorry about any wrap!

--
Regards,

Greg Strong