Removing a Date String

[OU18]

Hi all,

Was wondering if someone could help me with this little issue i am having with creating a query to pull out a date string from a field.

the fields data looks like this.

FieldName = SV_DESC
CONF:7/26 #(4) MIN(99) 800PC

I need to pull only the information 7/26 from the field.

Note: It could be 07/26 or in there as 7/26. Also there are instances where it appears without the )colon after the CONF

I tried using the following in my query,

Date: Mid([SV_DESC],InStr([SV_DESC],":")+1,InStr(InStr([SV_DESC]," ")-2,[SV_DESC]," "))

but it gives me data after the space in the date. 7/26 #(4)

Thanks for the help and advice

 

[Golom]

Try
[blue][tt]
IIF(Instr([SV_DESC],":") = 0,

Mid([SV_DESC],5,instr([SV_DESC]," ")-6),

Mid([SV_DESC],InStr([SV_DESC],":") + 1,
InStr([SV_DESC]," ") - InStr([SV_DESC],":") - 1)
)
[/tt][/blue]

 

[SgtJarrow]

the below function will get your result as well....

Public Function ReturnDate(strString As String) As String

    Dim strTest As String
    
    strTest = Mid(strString, InStr(1, strString, "/") - 2, 2)
    
    If IsNumeric(strTest) Then
        ReturnDate = Mid(strString, InStr(1, strString, "/") - 2, 5)
    Else
        ReturnDate = Mid(strString, InStr(1, strString, "/") - 1, 4)
    End If

End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[OU18]

Thanks for the input everyone. I made a modification to it, but it was the core information I needed. Thanks again