Count instances of character in a query 1

[gtenney]

I've been pondering and trying various things, then searching but I still haven't been able to find an answer. My question is this - I have a user who wants to store multiple dates in one field, then count the number of dates in that field. The dates would be separated by the same character always, probably semicolon. Can I count the number of semicolons in one field (and add 1 - easy part) and return that number in another field in a query?

CountInstance([Field1],";")+1 would be nice here

Thanks!

 

[PHV]

Depending of the version of access you may consider this:
Len([Field1])-Len(Replace([Field1],';',''))+1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

storing multiple pieces of information in a single field breaks normalization rules. You should set up another table to handle the many side of the relationship. It's much easier to count records than fields.

check out Fundamentals of Relational Database Design

leslie


Leslie

 

[gtenney]

PHV: Thanks alot, that seemed to work! I had played with replace and len, but I was I didn't think of that.

Les: I agree, and if I was designing the db in a rational manner I would to another table to store the dates, but the user said no to that and the user is also my boss so dates in one field it is!

 

[sfreeman]

Public Function CountOfInstances(strDate, strSearchChar) As Integer

Dim intLen As Integer
Dim intCount As Integer
Dim i As Integer
Dim strCurrChar As String

intCount = 0
intLen = Len(strDate)

For i = 1 To intLen
strCurrChar = Mid(strDate, i, 1)
If strCurrChar = strSearchChar Then intCount = intCount + 1
Next i

CountOfInstances = intCount

End Function

Sam_F
"90% of the problem is asking the right question.

 

[PHV]

The same function as Sam if ac2k or above:
Public Function CountOfInstances(strDate, strSearchChar) As Integer
CountOfInstances = UBound(Split(strDate, strSearchChar))
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sfreeman]

;-B

Nicely done...

Sam_F
"90% of the problem is asking the right question.