Count Number of Specific Character in Text Box

[Josh]

Hello, I have read a little about this topic on the forums, but have been
unable to get it to work. I need to count the number of occurences of "#"
within a text box on a report. I was trying to use the Len function, but am
not able to figure out how to make it work.

Thanks

 

[John Spencer]

The basic expression would be

= Len([YourFieldName] & "") - Len(Replace([YourFieldName] & "","#",""))

Make sure the control is not named the same as a field name as this will cause
an error

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Josh]

Thank you so much for your help. This works great, but I have another
question with this function. I'm wondering if there is a way to write an IIF
(or some other) function that will make this Len function not count the # if
a differen cell within the same entry contains a Null value.

It would look like this:

If the Efficiency cell contains a value, the Len function will count the "#"
within the comments cell.
If the Efficiency cell contains a Null value, then the Len function will not
count the "#" and the value "0" will be returned.

I am not too familiar with the format of the IIF function, but I imagine it
would look something like this (although I could not get this to work):

=IIF(([Efficiency]=Null, 0, =Len([Comments] & "") - Len(Replace([Comments] &
"","#","")))


Thanks again for your help

The basic expression would be

= Len([YourFieldName] & "") - Len(Replace([YourFieldName] & "","#",""))

Make sure the control is not named the same as a field name as this will cause
an error

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello, I have read a little about this topic on the forums, but have been
unable to get it to work. I need to count the number of occurences of "#"
within a text box on a report. I was trying to use the Len function, but am
not able to figure out how to make it work.

Thanks

 

[John Spencer]

VERY Close.

You can't use = Null. You need to use IS Null in a query or
IsNull({Efficiency]) in VBA to determine if the field is noull

Next you don't use the = sign within the IIF to get a value.

=IIF(([Efficiency] IS Null,0,Len([Comments] & "") - Len(Replace([Comments] &
"","#","")))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you so much for your help. This works great, but I have another
question with this function. I'm wondering if there is a way to write an IIF
(or some other) function that will make this Len function not count the # if
a differen cell within the same entry contains a Null value.

It would look like this:

If the Efficiency cell contains a value, the Len function will count the "#"
within the comments cell.
If the Efficiency cell contains a Null value, then the Len function will not
count the "#" and the value "0" will be returned.

I am not too familiar with the format of the IIF function, but I imagine it
would look something like this (although I could not get this to work):

=IIF(([Efficiency]=Null, 0, =Len([Comments] & "") - Len(Replace([Comments] &
"","#","")))


Thanks again for your help

The basic expression would be

= Len([YourFieldName] & "") - Len(Replace([YourFieldName] & "","#",""))

Make sure the control is not named the same as a field name as this will cause
an error

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello, I have read a little about this topic on the forums, but have been
unable to get it to work. I need to count the number of occurences of "#"
within a text box on a report. I was trying to use the Len function, but am
not able to figure out how to make it work.

Thanks

 

[Josh]

Thank you so much.
You are the best.
I couldn't do it without you.

VERY Close.

You can't use = Null. You need to use IS Null in a query or
IsNull({Efficiency]) in VBA to determine if the field is noull

Next you don't use the = sign within the IIF to get a value.

=IIF(([Efficiency] IS Null,0,Len([Comments] & "") - Len(Replace([Comments] &
"","#","")))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you so much for your help. This works great, but I have another
question with this function. I'm wondering if there is a way to write an IIF
(or some other) function that will make this Len function not count the # if
a differen cell within the same entry contains a Null value.

It would look like this:

If the Efficiency cell contains a value, the Len function will count the "#"
within the comments cell.
If the Efficiency cell contains a Null value, then the Len function will not
count the "#" and the value "0" will be returned.

I am not too familiar with the format of the IIF function, but I imagine it
would look something like this (although I could not get this to work):

=IIF(([Efficiency]=Null, 0, =Len([Comments] & "") - Len(Replace([Comments] &
"","#","")))


Thanks again for your help

The basic expression would be

= Len([YourFieldName] & "") - Len(Replace([YourFieldName] & "","#",""))

Make sure the control is not named the same as a field name as this will cause
an error

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Josh wrote:
Hello, I have read a little about this topic on the forums, but have been
unable to get it to work. I need to count the number of occurences of "#"
within a text box on a report. I was trying to use the Len function, but am
not able to figure out how to make it work.

Thanks