count certain character

[Cynthia]

I need to be able to count the number of commas in a text string. I found
that itemcount does not work in access query. I am running a query where I
need to separate out a field that has multiple dwgnames. I am getting this
information from somewhere else and have no control over that. I have a
linenumber and isonumber field as shown below

linenumber isonumbers
2"-234-sdf cw123,cs234,dd123
3"-233-asd dd333,cc233,aa233,dd222

I need a query that will show each isonumber separately with the linenumber
What is the best way to go about this?

 

[Ken Snell \(MVP\)]

I use this function for this counting operation:



' ***************************************
' ** Function CountCharacterOccurences **
' ***************************************

Public Function CountCharacterOccurences(ByVal strStringToSearch As Variant,
_
ByVal strCharacterToFind As String) As Long
' *** THIS FUNCTION COUNTS THE NUMBER OF TIMES A SINGLE CHARACTER IS FOUND
' *** IN A TEXT STRING. THE SEARCH IS CASE-INSENSITIVE.
' *** THE FUNCTION'S VALUE IS THE NUMBER OF TIMES THE SINGLE CHARACTER IS
FOUND.

Dim lngCountChars As Long, lngPosition As Long

On Error Resume Next

lngCountChars = 0
'truncate sChar in case it is longer than one character
strCharacterToFind = Left(strCharacterToFind, 1)

For lngPosition = 1 To Len(strStringToSearch)
'if character is found, increment the counter
If Mid(strStringToSearch, lngPosition, 1) = strCharacterToFind Then _
lngCountChars = lngCountChars + 1
Next lngPosition

CountCharacterOccurences = lngCountChars
Exit Function
End Function

 

[RoyVidar]

I need to be able to count the number of commas in a text string. I
found that itemcount does not work in access query. I am running a
query where I need to separate out a field that has multiple
dwgnames. I am getting this information from somewhere else and have
no control over that. I have a linenumber and isonumber field as
shown below

linenumber isonumbers
2"-234-sdf cw123,cs234,dd123
3"-233-asd dd333,cc233,aa233,dd222

I need a query that will show each isonumber separately with the
linenumber What is the best way to go about this?

Here's an attempt at a function that should give something like that
(air code)

public function MyCount(ByVal TheString as variant, _
optional byval TheDelimiter as String = ",") as long

dim tmp as string

tmp = TheString & vbnullstring

if len(tmp) then
MyCount = Ubound(Split(tmp, TheDelimiter))
end if

end function

 

[RoyVidar]

Looks like I totally overlooked this part of the question, sorry.

I don't know how to do that in a query. I think it would involve a
bit of coding and either build up a temp table, or an in memory
structure adding each of the isonumbers to a separate row with the
linenumber.

 

[Ken Snell \(MVP\)]

One approach to "unconcatenating" your data is to write a UNION query that
would have one subquery to extract the first value, a second for the second
value, etc. You could use the function I provided to determine if a subquery
should try to return a value or not. This approach may lead to some
interesting errors that might need to be "trapped", and would involve some
programming to go with the query.

Or one could generate a temporary table and fill it with the appropriate
records via VBA programming, and then use a query to show the records from
that table.

Or there might be some additional programming approaches that would be
useful.

What is the maximum number of values that might be contained within the
isonumbers field?

 

[Cynthia]

My largest number would be six, I appreciate your quick responce, I believe
your suggestions will get me where I need to go. Although I do like your
idea of a temporary table, it may get me the data quicker.

 

[Ken Snell \(MVP\)]

OK, post back if you have additional questions.

--

Ken Snell
<MS ACCESS MVP>

My largest number would be six, I appreciate your quick responce, I
believe
your suggestions will get me where I need to go. Although I do like your
idea of a temporary table, it may get me the data quicker.

< snipped >

 

[missinglinq via AccessMonster.com]

Are we talking about counting the number of times a comma appears in a string?


CommaCount = Len([YourString]) - Len(Replace([YourString], ",", ""))