[AccessD] Query will not run function

[John Bodin]

Not sure.  I did similar to you: SELECT EMPLOYEE.ADDRESS, extractnumber([Address]) AS Expr1 FROM EMPLOYEE

and got

Name                Expr1
39 Maple St         39
Maintenance
30-50 Oak St     3050











John Bodin

sBOR Office Systems

jbodin at sbor.com<mailto:jbodin at sbor.com>



________________________________
From: AccessD <accessd-bounces at databaseadvisors.com> on behalf of Kaup, Chester <Chester_Kaup at kindermorgan.com>
Sent: Tuesday, April 2, 2019 9:35 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] FW: Query will not run function

Got the function to run but it returns a value only for the first record in the table.

From: Kaup, Chester
Sent: Tuesday, April 02, 2019 8:25 AM
To: Access Developers discussion and problem solving
Subject: Query will not run function

I am probably missing something simple but when I try to run this query I get no results for field Old_Property_ID.

Here is the SQL
SELECT [tbl Property Codes].New_Property_ID, ExtractNumber([New_Property_ID]) AS Old_Property_ID,
[tbl Property Codes].Description,
[tbl Property Codes].Class,
[tbl Property Codes].Process_Id,
[tbl Property Codes].Asset_Id,
[tbl Property Codes].[Accounting Unit]
FROM [tbl Property Codes];

And here is the function

Option Compare Database
Option Explicit

Public Function ExtractNumber(strInput) As String
    ' Returns the numeric characters within a string in sequence in which they are found within the string
    Dim strResult As String, strCh As String, fExtractNumeric As String
    Dim intI As Integer
     If Not IsNull(strInput) Then
        For intI = 1 To Len(strInput)
            strCh = Mid(strInput, intI, 1)
            Select Case strCh
                 Case "0" To "9"
                    strResult = strResult & strCh
                 Case Else
             End Select
        Next intI
     End If
     fExtractNumeric = strResult
End Function

Thanks for your help.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com