Very Computer

I asked this before but unfortunately I didn't get back to the newsgroup
soon enough.  I am using VB5 and storing data in a DAO 3.5 database (Access
97).

I would like to do the following query in code:

  UPDATE PlotRecords SET PlotRecords.Accounting =
FirstNumberSeries(PlotRecords.PlotName);

The problem I have is that FirstNumberSeries is a public function defined in
the VB5 code and I get the message:
"Undefined function 'FirstNumberSeries' in expression."

I have tried to put the FirstNumberSeries in a code module in the mdb file
with no luck either.  I would prefer to avoid creating a recordset and
walking through each record (or would that be faster?).  Could someone
please give some me guidance.  Thanks.

Cameron A. Eckman

Sorry, you cant user user defined functions embeded in SQL Statment in VB
.. (only in Access ...)

--
Alberto Borbolla
Tecnologia en Sistemas
MEXICO

Replace "X" to send mail

Cameron Eckman wrote ...

You can't have a function embedded in your sql statement.
It has to do with the fact that the Jet Engine evaluates
SQL statements and your user-defined function is not
an SQL statement.

However, since all Jet is asking for is an SQL statement
(a string) why not do the following:

    ...
    dim db as database
    dim strSQL as string

    set db = DBEngine.Workspaces(0).OpenDatabase("Your database", etc...)

    ' Build the SQL statement using concatenation ...

    strSQL = "UPDATE PlotRecords SET PlotRecords.Accounting = '" & _
             FirstNumberSeries(PlotRecords.PlotName) & "';"

    db.Execute strSQL

    ...

This is assuming that your function FirstNumberSeries() returns a string
value.

The public function will ve evaluated by VB and you will be sending
an SQL statement to JET.

Now, let's assume your function returns the following:  "whatever"
your SQL statement (strSQL) would then be:

    UPDATE PlotRecords SET PlotRecords.Accounting = 'whatever';

Hope this helps !

Robert Ayotte

Montreal

Thanks, I know I could do what you said.  However, the function needs to be
applied to each record as it finds the first series of numbers in a string;
mostly useful for searching for project numbers in varying directory paths;
better than Val because it goes until it does find a number.  For
"P:\Projects\012345\myplot.plt", returns "012345".

Code in case anyone would like it:

Public Function FirstNumberSeries(ByVal InString As String) As String
Dim blnFoundNumber As Boolean
Dim lngStringLength As Long, lChar As Long
Dim strTempValue As String, strChar As String

   strTempValue = ""
   lngStringLength = Len(InString)

   For lChar = 1 To lngStringLength
      strChar = Mid(InString, lChar, 1)

      If (Asc(strChar) >= 48) And (Asc(strChar) <= 57) Then
         blnFoundNumber = True
         strTempValue = strTempValue & strChar
      ElseIf (blnFoundNumber) Then
         If (strChar = " ") Or (strChar = "_") Or (strChar = "-") Then
            strTempValue = strTempValue & strChar
         Else
            Exit For
         End If
      End If
   Next lChar

   FirstNumberSeries = strTempValue
End Function

I havent tryed this but I think you can include the FirstNumberSeries
function in the MDB database and your query should work fine. Try it out.

Hope it works.
Jaime

Quote:>I havent tryed this but I think you can include the FirstNumberSeries
>function in the MDB database and your query should work fine.

Not from within VB. Only from within Access.

-- Jim Ferguson, FMS
   http://www.fmsinc.com