(I guess it can’t be done in LO, at least not yet. … As @Ratslinger has hinted, you can mess with the queries under a report or the like, but this is not really using functions in queries, and also as @peterwt has reminded us you can use SQL functions, but these are not basic functions, and so you can’t edit and debug them like you can with basic.)
Say you have a BASIC macro function called sLastFirst(first,last) that you want to use to format names as “Last, First”.
Function sLastFirst (sFirst As String, sLast As String) As String
sLastFirst = sLast & ", " & sFirst
End Function
Is there any way you can use it in SQL like this:
SELECT sLastFirst(`First`,`Last) As Name From Names;
Is there any way to make this work, or do I instead have to rewrite it into a SQL function?
MORE EXAMPLES
To help others further understand the need for what I am asking for, here are a bunch of functions that I used in Access to help w/ my SQL. This is very hard I think to do with stored procedures, although it might be faster if you can ever get it to work.
'=== NAMES ===============================================================
' --- Just first part of name
Function FirstNameString(First As Variant, NickName As Variant, Middle As Variant)
FirstNameString = Trim(Nz([First])) & _
IIf(Nz([Middle]) <> "", " " & Trim(Nz([Middle])), "") & _
IIf(Nz([NickName]) <> "", " " & Chr(34) & Trim(Nz([NickName])) & Chr(34), "")
End Function
' --- Just first part of name; Return First or Nick; Give priority to First
Function CommonFirstNameString(First As Variant, NickName As Variant, Middle As Variant) As String
If Nz(First) = "" Then
If Nz(NickName) = "" Then
CommonFirstNameString = Trim(Nz(Middle))
Else
CommonFirstNameString = Trim(Nz([NickName]))
End If
Else
CommonFirstNameString = Trim(Nz([First]))
End If
' Broken - returns nick when both exist
' If Nz(NickName) = "" Then
' CommonFirstNameString = Trim(Nz([First]))
' Else
' CommonFirstNameString = Trim(Nz([NickName]))
' End If
End Function
' --- Full name
' Pack together name components with appropriate spaces and projects for nickname
' note: the call for this must make sure that the parameters aren't null.
' the easiest way to do this is to add '& ""' to each parameter to
' force it to be a non-null string. - hwj
Function FormattedName(First As Variant, NickName As Variant, Middle As Variant, Last As Variant)
FormattedName = FirstNameString(Nz([First]), Nz([NickName]), Nz([Middle])) & " " & Trim(Nz([Last]))
End Function
' --- Full name, but with only middle initial
Function ShortFormattedName(First As Variant, NickName As Variant, Middle As Variant, Last As Variant)
ShortFormattedName = FirstNameString(Nz([First]), Nz([NickName]), Left$(Nz([Middle]), 1)) & " " & Trim(Nz([Last]))
End Function
' --- "Last, First" style name
Function LastFirstName(First As Variant, NickName As Variant, Middle As Variant, Last As Variant)
LastFirstName = Trim(Nz([Last])) & ", " & FirstNameString(First, NickName, Middle)
End Function
'=== PHONES ===============================================================
' --- Print phone if not private
Function PublicPhone(Pub As Variant, Phone As Variant)
If (Nz([Pub]) = "") Then
PublicPhone = IIf(Nz([Phone]) = "", "___-____", "(Permission?)")
Else
If ([Pub] = "none") Then
PublicPhone = "(none)"
Else
PublicPhone = IIf([Pub] = "Public" Or [Pub] = "Book", Nz([Phone]), "(Private#)")
End If
End If
End Function
' --- Print phone if not private + extension
Function PublicPhoneX(Pub As Variant, Phone As Variant, Ext As Variant)
If (Nz([Pub]) = "") Then
PublicPhoneX = IIf(Nz([Phone]) = "", "___-____", "(Permission?)")
Else
If ([Pub] = "none") Then
PublicPhoneX = "(none)"
Else
PublicPhoneX = IIf(Nz([Pub]) = "Public" Or Nz([Pub]) = "Book", Nz([Phone]) & IIf(Nz([Ext]) = "", "", " x " & Nz([Ext])), "(Private#)")
End If
End If
End Function
' --- Print phone if not private + extension
Function PPublicPhoneX(Pub As Variant, aCode As Variant, Phone As Variant, Ext As Variant)
If (Nz([Pub]) = "") Then
PPublicPhoneX = IIf(Nz([Phone]) = "", "___-____", "(Permission?)")
Else
If ([Pub] = "none") Then
PPublicPhoneX = "(none)"
Else
PPublicPhoneX = IIf(Nz([Pub]) = "Public" Or Nz([Pub]) = "Book", IIf(Nz([aCode]) = "", " ", Nz([aCode]) & "-") & Nz([Phone]) & IIf(Nz([Ext]) = "", "", " x " & Nz([Ext])), "(Private#)")
End If
End If
End Function
' --- Print phone if not private + extension
Function PPublicBlockedPhoneX(Pub As Variant, Blocked As Variant, aCode As Variant, Phone As Variant, Ext As Variant)
Dim Pfx1 As String
Dim Blkd As String
Dim Pfx2 As String
Dim Extn As String
If (Nz([Pub]) = "") Then
PPublicBlockedPhoneX = IIf(Nz([Phone]) = "", "___-___-____ x ___", "(Permission?)")
Else
If ([Pub] = "none") Then
PPublicBlockedPhoneX = "(none)"
Else
If Nz([Pub]) = "Public" Or Nz([Pub]) = "Book" Then
Pfx1 = IIf(Nz([aCode]) = "", " ", "")
Blkd = IIf(Nz([Blocked]) = "", " ", Nz([Blocked]) & "-")
Pfx2 = IIf(Nz([aCode]) = "", "", Nz([aCode]) & "-")
PPublicBlockedPhoneX = Pfx1 & Blkd & Pfx2 & PhoneExt(Nz([Phone]), Nz([Ext]))
Else
PPublicBlockedPhoneX = "(Private#)"
End If
End If
End If
End Function
' --- Format either "(ACode)" or "", i.e. nothing.
Function OptionalACode(aCode As Variant) As String
If Nz(aCode) = "" Then
OptionalACode = ""
Else
OptionalACode = "(" & [aCode] & ") "
End If
End Function
' --- Build phone# and add "___" if missing
Function Phone___(aCode As Variant, Phone As Variant, Ext As Variant) As String
Phone___ = DefaultaCodePhone("___", aCode, Phone, Ext)
End Function
' --- Build phone# and add "650" if missing
Function Phone650(aCode As Variant, Phone As Variant, Ext As Variant) As String
Phone650 = DefaultaCodePhone("650", aCode, Phone, Ext)
End Function
' --- Build phone# and add "760" if missing
Function Phone760(aCode As Variant, Phone As Variant, Ext As Variant) As String
Phone760 = DefaultaCodePhone("760", aCode, Phone, Ext)
End Function
' --- Build phone# and add default area code if missing (allow for just "911" type areaCode only numbers)
Function DefaultaCodePhone(defaultACode As Variant, aCode As Variant, Phone As Variant, Ext As Variant) As String
DefaultaCodePhone = aCodePhoneExt(IIf(Nz([aCode]) = "", defaultACode, aCode), Phone, Ext)
End Function
' --- Build phone# and add default area code if missing (allow for just "911" type areaCode only numbers)
Function aCodePhoneExt(aCode As Variant, Phone As Variant, Ext As Variant) As String
If Nz(aCode) & Nz([Phone]) = "" Then
aCodePhoneExt = ""
Else
aCodePhoneExt = Nz([aCode]) & IIf(Nz(Phone) = "", "", "-" & PhoneExt(Phone, Ext))
End If
End Function
' === Private sub to build extension
Private Function PhoneExt(Phone As Variant, Ext As Variant) As String
PhoneExt = Nz([Phone]) & IIf(Nz([Ext]) = "", "", " x " & Nz([Ext]))
End Function
'=== ADDRESSES =============================================================
' --- Add Street# to Street name (DEPRECIATED function)
Function StreetAdr(No As Variant, Street As Variant) As String
StreetAdr = Trim([No]) & IIf(Nz([No]) = "", "", " ") & Nz([Street])
End Function
' --- Format street# & Apt#
'Function AdrApt(No As Variant, Apt As Variant) (<-DEPRECIATED name)
Function NoApt(No As Variant, Apt As Variant) As String
NoApt = Trim(Nz([No])) & IIf(Trim(Nz([Apt])) = "", "", " " & IIf(IsNumeric([Apt]), "#", "") & Trim([Apt]))
End Function
' --- Add Street & Apt to Street name
'Function AdrAptStreet(No As Variant, Apt As Variant, Street As Variant) (<-DEPRECIATED name)
Function NoAptStreet(No As Variant, Apt As Variant, Street As Variant) As String
NoAptStreet = Trim(NoApt(No, Apt) & " " & Trim(Nz([Street])))
End Function
' === #/Apt/Street/City (without POB. See also one below)
Function NoAptStreetCity(No As Variant, Apt As Variant, Street As Variant, City As Variant) As String
NoAptStreetCity = NoAptStreet(No, Apt, Street) & ", " & Nz([City])
End Function
' === #/Apt/Street/City (with POB. See also one above)
Function NoAptStreetPOBCity(No As Variant, Apt As Variant, Street As Variant, POB As Variant, City As Variant) As String
NoAptStreetPOBCity = NoAptStreet(No, Apt, Street) & IIf(IsNull(No) And IsNull(Apt) And IsNull(Street), "", ", ") & IIf(IsNull(POB), "", "POB " & POB & ", ") & Nz([City])
End Function
' === City/St
Function CitySt(City As Variant, ST As Variant) As String
CitySt = Nz(City) & ", " & Nz([ST])
End Function
Function CityCountySt(City As Variant, County As Variant, ST As Variant) As Variant
CityCountySt = Nz(City) & IIf(IsNull([City]) Or IsNull([County]), "", ", ") & IIf(IsNull([County]), "", [County]) & IIf(IsNull([ST]), "", ", " & [ST])
End Function
' === #/Apt/Street/City/St (without POB. See also one below)
Function NoAptStreetCitySt(No As Variant, Apt As Variant, Street As Variant, City As Variant, ST As Variant) As String
NoAptStreetCitySt = NoAptStreetCity(No, Apt, Street, City) & ", " & Nz([ST])
End Function
' === #/Apt/Street/POB/City/St (with POB. See also one above)
Function NoAptStreetPOBCitySt(No As Variant, Apt As Variant, Street As Variant, POB As Variant, City As Variant, ST As Variant) As String
NoAptStreetPOBCitySt = NoAptStreetPOBCity(No, Apt, Street, POB, City) & ", " & Nz([ST])
End Function
' === #/Apt/Street/City/St/Zip (without POB. See also one below)
Function NoAptStreetCityStZip(No As Variant, Apt As Variant, Street As Variant, City As Variant, ST As Variant, Zip As Variant) As String
NoAptStreetCityStZip = NoAptStreetCitySt(No, Apt, Street, City, ST) & " " & Nz([Zip])
End Function
' === #/Apt/Street/POB/City/St/Zip (with POB. See also one above)
Function NoAptStreetPOBCityStZip(No As Variant, Apt As Variant, Street As Variant, POB As Variant, City As Variant, ST As Variant, Zip As Variant) As String
NoAptStreetPOBCityStZip = NoAptStreetPOBCitySt(No, Apt, Street, POB, City, ST) & " " & Nz([Zip])
End Function
' === Zip/Plus4
Function ZipPlus4(Zip As Variant, Plus4 As Variant) As String
ZipPlus4 = Zip & IIf(Trim(Nz([Plus4])) = "", "", "-" & Nz([Plus4]))
End Function
' === No/Apt/Street/City/St/Zip/Plus4 (without POB. See also one below)
Function NoAptStreetCityStZipPlus4(No As Variant, Apt As Variant, Street As Variant, City As Variant, ST As Variant, Zip As Variant, Plus4 As Variant) As String
NoAptStreetCityStZipPlus4 = NoAptStreetCityStZip(No, Apt, Street, City, ST, ZipPlus4(Zip, Plus4))
' NoAptStreetCityStZipPlus4 = NoAptStreetCityStZip(No, Apt, Street, City, ST, Zip) & IIf(Trim(Nz([Plus4])) = "", "", "-" & Nz([Plus4]))
End Function
' === No/Apt/Street/POB/City/St/Zip/Plus4 (with POB. See also one above)
Function NoAptStreetPOBCityStZipPlus4(No As Variant, Apt As Variant, Street As Variant, POB As Variant, City As Variant, ST As Variant, Zip As Variant, Plus4 As Variant) As String
NoAptStreetPOBCityStZipPlus4 = NoAptStreetPOBCityStZip(No, Apt, Street, POB, City, ST, ZipPlus4(Zip, Plus4))
End Function
' === City/State/Zip/Plus4 (use Null if Plus4 doesn't exist)
Function CityStateZip(City As Variant, ST As Variant, Zip As Variant, Plus4 As Variant) As String
CityStateZip = Nz([City]) & ", " & Nz([ST]) & " " & Nz([Zip]) & IIf(Nz([Plus4]) = "", "", "-" & Nz([Plus4]))
End Function
' === City/CA/Zip
Function CityCaZip(City As Variant, Zip As Variant, Plus4 As Variant) As String
CityCaZip = Trim(CityStateZip([City], "CA", [Zip], [Plus4]))
End Function
' === StCity (for sorting by location)
Function StCity(ST As Variant, City As Variant) As String
StCity = ST & ", " & City
End Function
' === StCityStreetAdrApt (for sorting by location) As String
Function StCityStreetAdrApt(ST As Variant, City As Variant, Street As Variant, Number As Variant, Apt As Variant)
StCityStreetAdrApt = StCity(ST, City) & ", " & Street & ", " & Number & " " & Apt
End Function