Scan String For Comma (1 Viewer)

GC2010 · Jun 12, 2019

I am working on a function that will scan a string to see if it has a comma. If I use

Code:

Function CommaCheck()
If InStr(“Bob, Frank, James”, “,”) = 0 Then
  Debug.Print “No comma”
Else
  Debug.Print “At least one comes”
End If
End Function

This properly identifies the comma - however I’m needing to pass in a string and when I do this it always shows no comma

Code:

Dim namesList As String
namesList = “Bob, Frank, James”

If InStr(namesList, “,”) = 0 Then
  Debug.Print “Nope”
Else
  Debug.Print “At least one”
End If

jdraw · Jun 12, 2019

Give us an example with your real data.
The quotes you are using “ ” have often been misunderstood by Access in my efforts, but could be acceptable for your regional settings??

To find if a comma exists you need
If (InStr( yourFieldName, ",") >0 Then do something

What it is doing is to show that a comma was found at a position. If it is =0, then no comma was found. Instr()

Update:
This works for me --note the quotes
?chr(34)
"
Your quotes gave me an undefined variable???

Code:

Sub GC2010()
Dim namesList As String
namesList = "Bob, Frank, James"

If InStr(namesList, ",") = 0 Then
  Debug.Print "Nope"
Else
  Debug.Print "At  least  one"
End If
End Sub

Result:
At least one

Gasman · Jun 12, 2019

I cannot see how?

Code:

namesList = "Bob, Frank, James"
? InStr(namesList, ",")
 4

GC2010 said:
I am working on a function that will scan a string to see if it has a comma. If I use

Code:

Function CommaCheck() If InStr(“Bob, Frank, James”, “,”) = 0 Then Debug.Print “No comma” Else Debug.Print “At least one comes” End If End Function

This properly identifies the comma - however I’m needing to pass in a string and when I do this it always shows no comma

Code:

Dim namesList As String namesList = “Bob, Frank, James” If InStr(namesList, “,”) = 0 Then Debug.Print “Nope” Else Debug.Print “At least one” End If

GC2010 · Jun 12, 2019

jdraw said:
Give us an example with your real data.
The quotes you are using “ ” have often been misunderstood by Access in my efforts, but could be acceptable for your regional settings??

To find if a comma exists you need
If (InStr( yourFieldName, ",") >0 Then do something

What it is doing is to show that a comma was found at a position. If it is =0, then no comma was found. Instr()

Those are the real data I’m testing with. I’m posting from mobile so it is messing up the quotes.

GC2010 · Jun 12, 2019

Gasman said:
I cannot see how?

Code:

namesList = "Bob, Frank, James" ? InStr(namesList, ",") 4

What do you mean?

June7 · Jun 12, 2019

I don't see anything wrong with using = 0 - works for me, even with variable.

Gasman · Jun 12, 2019

Yes, I noticed the quotes being altered. I changed them for the code to work.

That is from the immediate window of the VBA debugger. That is where i do quick tests like that.

If you wanted a function, wouldn't you have something like

Code:

Function CommaCheck(strToCheck As Variant)
CommaCheck = False
If InStr(strToCheck, ",") Then
  CommaCheck = True
End If

Result from immediate window

Code:

namesList = "BobFrankJames"
? commacheck(nameslist)
False
namesList = "Bob,Frank,James"
? commacheck(nameslist)
True

theDBguy · Jun 12, 2019

GC2010 said:
What do you mean?

Hi. I think Gasman means it works. It does for me too.

jdraw · Jun 12, 2019

Hi Guys,
I saw the slanted quotes and that reminded me of some old posts and personal experience where those quotes were not recognized and responded to that. The code works for me also. I don't often test the negative if Instr(x..x) = 0 .

The_Doc_Man · Jun 12, 2019

With regards to the "slanted quotes"...

GC2010, you say you have to pass in a string. And I understand that part. But from where does the input ultimately originate? If that is something you input to a form or input box, that is one thing. But if you are reading this from another utility via object automation, it is important to know the source.

In particular, if this input could come through MS Word then quoting becomes an issue. I have seen Word quotes that screw up the feature that used to be called Grammatik even though the quotes look perfectly fine. The point being, not all quotes are alike.

GC2010 · Jun 12, 2019

Okay, I took it back to basics and all of you above are correct the function works as it should. My issue was with variable scope....

Let me illustrate further...
I have a Module where I have

Code:

Public contactName As String

Public Function ValidateInput (contactName As String)
  If InStr(contactName, ",") = 0 Then
    contactName = contactName
    Debug.Print "No Comma"
  Else
    contactName = "Multi"
    Debug.Print "Comma"
  End If
End Function

Now in my button click event of my form I have this code

Code:

Private Sub btnCreateEmail_Click()
Dim emailBody As String
contactName = DLookup("[Main Contact]", "[Table With Spaces]", "[Employee Name] '" & Me.cboEmp.COlumn(0) & "'")

ValidateInput (contactName)

emailBody = "Hi " & contactName

'more code here.

End Sub

Sorry for my mishap here!

Scan String For Comma (1 Viewer)

GC2010

Registered User.

jdraw

Super Moderator

Gasman

Enthusiastic Amateur

GC2010

Registered User.

GC2010

Registered User.

June7

AWF VIP

Gasman

Enthusiastic Amateur

theDBguy

I’m here to help

Attachments

jdraw

Super Moderator

The_Doc_Man

Immoderate Moderator

GC2010

Registered User.

Similar threads

Users who are viewing this thread