MajP
You've got your good things, and you've got mine.
- Local time
- Today, 12:39
- Joined
- May 21, 2018
- Messages
- 8,582
You are likely going to want to do this more than one place in your DB. So for easy usability I like to make individual functions. When I call a function from a query I always make sure I can handle bad data especially nulls and then return either Null or an empty string if the data is bad.
now my sql string is real easy
It checks to make sure the input is not null, has one /, and has info on both sides of the /. Also the year is numeric (2020, 2021), make a little easier to read.
Public Function GetPatientNumber(varInput As Variant) As String
Dim aInput() As String
If Not IsNull(varInput) Then
aInput = Split(varInput, "/")
If UBound(aInput) = 1 Then
GetPatientNumber = aInput(0)
End If
End If
End Function
Public Function GetInputYear(varInput As Variant) As Variant
Dim aInput() As String
If Not IsNull(varInput) Then
aInput = Split(varInput, "/")
If UBound(aInput) = 1 Then
GetInputYear = CInt("20" & aInput(1))
End If
End If
End Function
now my sql string is real easy
Code:
Order by getPatientNumber([someField]), getInputYear([someField])
It checks to make sure the input is not null, has one /, and has info on both sides of the /. Also the year is numeric (2020, 2021), make a little easier to read.