Great Function to Pluralize words

[NauticalGent]

Making my rounds this morning, I came across this handy UDF by @NoLongerSet . Great new tool for my toolbox...

 

[GPGeorge]

Mike offers great stuff. And he posts a new blog every. single. day.

 

[isladogs]

Just testing the Pluralise function:
1 new blog per day, 7 new blogs per week, 0 days with no blogs

As well as great content, I often find interesting material from links he provides. For example, I've just read

Regular Expressions: Now You Have Two Problems

I love regular expressions. No, I’m not sure you understand: I really love regular expressions. You may find it a little odd that a hack who grew up using a language with the ain’t keyword would fall so head over heels in love with something as obtuse and

blog.codinghorror.com

 

[Mike Krailo]

Making my rounds this morning, I came across this handy UDF by @NoLongerSet . Great new tool for my toolbox...

That's a neat function except it didn't really handle the zero case as well.

There are 0 more fingers on his hand after the surgery.

It should say: There are no more fingers on his hand after the surgery. But that's easily handled with just one more extra step. Nice job Mike.

Edit: After playing around with this more, I discovered how to fix the above issue and can appreciate even more how this function works.

for i = -2 to 2
    Print Pluralize ("There [is/are] # {more/fewer} finger[s] on his hand after the surgery." _
    , i, , "#;#;no")
Next i

So instead of 0, the word 'no' can be used in the code and it all works. Very nice.

The new output of the above code:

There are 2 fewer fingers on his hand after the surgery.
There is 1 fewer finger on his hand after the surgery.
There are no more fingers on his hand after the surgery.
There is 1 more finger on his hand after the surgery.
There are 2 more fingers on his hand after the surgery.

 

[sxschech]

This is how I did it back in 2020 to provide a count of files when sending an email message...

intNoOfFiles = CountFilesInFolder("N:\Letters\", "*.pdf")
    stsingplur = "is,are,file,files,has,have"
    If intNoOfFiles = 1 Then
        stsingplur = Split(stsingplur, ",")(0) & "," & Split(stsingplur, ",")(2) & "," & Split(stsingplur, ",")(4)
    Else
        stsingplur = Split(stsingplur, ",")(1) & "," & Split(stsingplur, ",")(3) & "," & Split(stsingplur, ",")(5)
    End If

stMessage = "There " & Split(stsingplur, ",")(0) & " <b>" & intNoOfFiles & _
                "</b> " & Split(stsingplur, ",")(1) & " waiting to be reviewed and signed.<br><br> " & _
                "After signing:<br><br>" & _

 

[NoLongerSet]

@Mike Krailo I actually had a draft of the article that did exactly what you have, with "no" as the "zero" option in the format string. But then I took it back out because I thought it led to ambiguity between the -5 and 0 cases:

There are 5 fewer fingers on his hand after the surgery.
There are no more fingers on his hand after the surgery.

 

[Mike Krailo]

Mike W., thanks for pointing that out, I guess it all depends on the complexity of the actual wording needed for each case but that is an awesome function and went right into my library. I have already updated several of my older projects to include it just to see how it works and this is a keeper. It was very creative to use bracket or curly brace to reverse the logic on wording. And of course the whole regex thing is very helpful to have around as well.

Here is an example that is interesting where there is a filtered continuous form showing the last 20 edited records when no search terms provided:

Last 10 Records edited  'when no search terms entered
49 Records found         'Search term yielded 49 records
1 Record found          'Search term yielded 1 records
No Records found          'Either NO or 0 would work in this case

And the code in the form:

Public Sub RefreshForm()
   Dim NumOfRec As Integer
   Dim FiltString As String
   Dim RecLimit As Integer
  
   ' We want the last {RecLimit} records edited to show by default without search criteria
   RecLimit = 10
   FiltString = vbNullString
  
   If FormActivity.GetSearchCritera <> vbNullString Then
      FiltString = "WHERE SearchText like ('*" & FormActivity.GetSearchCritera & "*');"
      Me.RecordSource = "SELECT * FROM qryCustomerList " & FiltString
      NumOfRec = FormActivity.NumberOfRecord("SELECT count(*) as NumberOfRecord FROM qryCustomerList " & FiltString)
      Me.lblNumberOfRecords.Caption = mStrings.Pluralize("# Record[/s] found.", NumOfRec, , "#;#;No")
      ' Old code for setting the Caption. See new pluralize function above.
'      If NumOfRec > 1 Then Me.lblNumberOfRecords.Caption = NumOfRec & " Records found."
'      If NumOfRec = 1 Then Me.lblNumberOfRecords.Caption = NumOfRec & " Record found."
'      If NumOfRec = 0 Then Me.lblNumberOfRecords.Caption = "No Record found."
   Else
      Me.RecordSource = "SELECT top " & RecLimit & " * FROM qryCustomerList WHERE [dtUpdate] Is Not Null ORDER BY [dtUpdate] DESC;"
      Me.lblNumberOfRecords.Caption = "Last " & RecLimit & " Records edited"
   End If
End Sub

I left the three lines of code commented out

 

[NoLongerSet]

This is how I did it back in 2020 to provide a count of files when sending an email message...

intNoOfFiles = CountFilesInFolder("N:\Letters\", "*.pdf")
    stsingplur = "is,are,file,files,has,have"
    If intNoOfFiles = 1 Then
        stsingplur = Split(stsingplur, ",")(0) & "," & Split(stsingplur, ",")(2) & "," & Split(stsingplur, ",")(4)
    Else
        stsingplur = Split(stsingplur, ",")(1) & "," & Split(stsingplur, ",")(3) & "," & Split(stsingplur, ",")(5)
    End If

stMessage = "There " & Split(stsingplur, ",")(0) & " <b>" & intNoOfFiles & _
                "</b> " & Split(stsingplur, ",")(1) & " waiting to be reviewed and signed.<br><br> " & _
                "After signing:<br><br>" & _

Using the Pluralize function the above code could be rewritten as:

intNoOfFiles = CountFilesInFolder("N:\Letters\", "*.pdf")
stMessage = Pluralize("There [is/are] <b>#</b> file[s] waiting to be reviewed and signed.<br><br> " & _
                      "After signing:<br><br>", intNoOfFiles)