[AccessD] Query will not run function

[John Bodin]

Change fExtractNumeric = strResult to your  function name, ExtractNumber = strResult






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:24 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] 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