Help

[Smudge0411]

Hi,

I have a col called Work Shift Name in a table which contains days of the week ie Mo, Tu, We, Th, Fr, Sa, Su. Im trying to count how many days a week an agent works, below is an example of the data & what I would like it to return
Thanks
K

Work Shift Name	No of days worked
Mo, Tu, We, Th, Fr	5​
Mo, Tu, We, Th, Fr	5​
Mo, Tu, We, Th, Fr	5​
Mo, Tu, We, Th, Fr	5​
Mo, Tu, We	3​
Mo, Tu, We, Th, Fr	5​
Mo, Tu, We, Th, Fr	5​
Mo, Tu, We, Th, Fr	5​
Tu, We, Th, Fr	4​

 

[Eugene-LS]

Public Function GetDaysFromText(vValue) As Integer
Const sDelimiter$ = ","
Dim sArr$()
    sArr = Split(vValue & "", sDelimiter)
    GetDaysFromText = UBound(sArr) + 1
End Function

Test:

?GetDaysFromText("Mo, Tu, We, Th, Fr")
 5

 

[Gasman]

If your DB was normalsed, that would be a piece of cake.
No user function required, use a built in function DCount()

 

[Smudge0411]

I've attached a set of example data I'm using, ultimately I am trying to count the number of days an employee works in a week form the Work Shift Name column & the YH Days Worked column being the result I'm after

 

[Gasman]

I've attached a set of example data I'm using, ultimately I am trying to count the number of days an employee works in a week form the Work Shift Name column & the YH Days Worked column being the result I'm after

Nothing attached?
Anyway, what is wrong with Eugene's solution?