count number of commas in a field

[lala]

how do i count number of commas in a field?

thank you

 

[ajetrumpet]

for a control:

dim i as integer, str as string, counter as integer

str = me.control
counter = 0

  for i = 1 to len(str)
    if mid(str, i, 1) = "," then
      counter = counter + 1
    end if
  next i

msgbox "The number of commas in this field is " & counter & "."

 

[lala]

thank you, and i know i should start another subject for this, but i'm in a rush to get this done

how do i write a replace function to replace spaces AT THE START of field?
i got it to work replacing spaces everywhere, but how do i specify that it's a start of field

here's what i have

replace([finalname]," ","")


thank you again

 

[Rabbie]

Try looking up the TRIM function in Access help. LTRIM is probably the one you want.

 

[boblarson]

Just use Trim (for spaces at both ends) or LTrim (for spaces at the beginning)

 

[boblarson]

Try looking up the TRIM function in Access help. LTRIM is probably the one you want.

Ah, beat me to it

 

[Rabbie]

Ah, beat me to it

At least we agreed on the answer

 

[lala]

why am i not thinking of this????????????
i feel like an idiot, i swear

thank you!!!!

 

[dbonin1599]

I don't understand what the me.control does...I am working with MS Access and would like to use this code snippet, but I don't quite understand it. I get most of it, but I don't get the me.control and I don't understand how it knows what field to look at?

Also, if I put this in as a SUB or a FUNCTION, how do I call it. I have built a couple of nice Sub routines, but I only know how to run them by opening up VB and running it from there. I may be just tired after a long day, but right now I feel like I am wondering in the desert surrounded by smart people and not knowing what to ask.

for a control:

dim i as integer, str as string, counter as integer

[COLOR=blue]str = me.control[/COLOR]
[COLOR=blue]counter = 0[/COLOR]
 
[COLOR=blue] for i = 1 to len(str)[/COLOR]
[COLOR=blue]   if mid(str, i, 1) = "," then[/COLOR]
[COLOR=blue]     counter = counter + 1[/COLOR]
[COLOR=blue]   end if[/COLOR]
[COLOR=blue] next i[/COLOR]
 
[COLOR=blue]msgbox "The number of commas in this field is " & counter & "."[/COLOR]

 

[John Big Booty]

The Me. of Me.Control indicates that we are dealing with a control on the current form. Whilst the Control of Me.Control should be replaced with the control name that you wish to interrogate.

 

[ajetrumpet]

I don't understand what the me.control does...I am working with MS Access and would like to use this code snippet, but I don't quite understand it. I get most of it, but I don't get the me.control and I don't understand how it knows what field to look at?

Also, if I put this in as a SUB or a FUNCTION, how do I call it. I have built a couple of nice Sub routines, but I only know how to run them by opening up VB and running it from there. I may be just tired after a long day, but right now I feel like I am wondering in the desert surrounded by smart people and not knowing what to ask.

"me" references the current object that you are on. calling this from another place, like query or an event will probably require you to input a variable in the arguments section. for example, function("control name"). then the actual code would look like this:

function(mycontrol as string)

mycontrol = forms("formname").controls(mycontrol).value

then the loop here to find the commas...

 

[missinglinq]

Actually, Replace() provides an easier way of counting commas:

Private Sub YourFieldName_AfterUpdate()
  Me.CommaCount = Len([YourFieldName]) - Len(Replace([YourFieldName], ",", ""))
End Sub

You could use the same count in other events, depending on your needs.

 

[georgedwilkinson]

Yeah, I thought that too until I noticed it was from over a year ago.