Count instaces of charcter in field

[Barkster]

I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks

 

[Duane Hookom]

Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

 

[Barkster]

Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP

I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks

 

[Duane Hookom]

I missed any mention of visual studio in your original post ;-)
I'm not aware of a solution outside of the Access environment.

--
Duane Hookom
MS Access MVP

Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP

I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks

 

[Brendan Reynolds]

The Len() function works in queries executed from external apps, including
..NET (I've tested it using C# and ADO.NET). But the Replace() function
doesn't. Unfortunately, I can't think of a way of doing it without using
Replace.

--
Brendan Reynolds
Access MVP

Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP

I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks

 

[Barkster]

Ok thanks, I'll see if I can do it another way. Appreciate the
comments.

The Len() function works in queries executed from external apps, including
.NET (I've tested it using C# and ADO.NET). But the Replace() function
doesn't. Unfortunately, I can't think of a way of doing it without using
Replace.

--
Brendan Reynolds
Access MVP

Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP


I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks