How do I test the value of a specific character within a string?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to write some VBA code for an Access 2000 database app that checks the
value of the 5th character in a 6 character string. I used to know how to do
this, but my brain has turned to mush and I've failed to find anything useful
in the online help.

No doubt the answer is extremely simple, but how do I do this?

David
 
If Left("MyString",5) = "ValueTestingFor" Then
MsgBox "the 5th character in " & "MyString" & " is what I am looking
for"
Else
MsgBox "OOps, wrong value!"
End If

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Thanks for the answers, guys. I was pretty sure that 'Left' or 'Right' were
the operators I was looking for (Mid$ was a less familiar solution), so why
couldn't I find anything about them in the Access online help?

David
 
Thanks for the answers, guys. I was pretty sure that 'Left' or 'Right' were
the operators I was looking for (Mid$ was a less familiar solution), so why
couldn't I find anything about them in the Access online help?

David
Click to expand...

It's not intuitive!
Open any code window (Ctrl + G) and click on help. Now search for
"function".
You'll find right, left, mid, etc. in the list.
 
Dale's code is correct, but Steve's code

If Left("MyString",5) = "ValueTestingFor" Then
MsgBox "the 5th character in " & "MyString" & " is what I am looking
for"
Else
MsgBox "OOps, wrong value!"
End If

as is often the case, makes no sense at all!

"Providing Customers A Resource For Help With Access, Excel And Word
Applications" has got to be the biggest lie since "Iraq has weapons of mass
destruction!"
 
Steve said:
If Left("MyString",5) = "ValueTestingFor" Then
MsgBox "the 5th character in " & "MyString" & " is what I am looking
for"
Else
MsgBox "OOps, wrong value!"
End If

PC Datasheet
Providing Customers BAD ANSWERS For Help With Access, Excel And Word
Applications
(e-mail address removed)
Click to expand...

--
You are *not* a resource at all !!
Stop advertising here, or get lost for another year or so...
http://home.tiscali.nl/arracom/whoissteve.html

ArnoR
 
If Left("MyString",5) = "ValueTestingFor" Then
MsgBox "the 5th character in " & "MyString" & " is what I
am looking
for"
Else
MsgBox "OOps, wrong value!"
End If

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word Applications
(e-mail address removed)
Click to expand...
Lovely code, but it fails to do what the Original Poster asked.

This is what he wanted.
If mid("MyString",5) = Whatever then

is the correct test.
"David Anderson" <[email protected]>
wrote in message
Click to expand...
 
Fred,
Thanks for the suggestion, but i've just tried it (with Access 2000) and
can't see any trace of Right, Left, etc, in the search results for
'Function'. Please clarify.

David
 
Bob,
Remember that I was wanting to do my check on just the 5th character. Dale's
solution works correctly and so does yours - but only if (like Dale) you add
the final parameter of 1. Because of my inability to find anything in the
online help, I'm not sure of the difference between Mid and Mid$, but both
seem to do the same job in this particular case.

David
 
Actually, you are both wrong.

The OP wanted the fifth character of a six character string.

As usual, stevie's answer is way off base and will get the first five
characters of the string.

Bob, you are closer, but you left out the third parameter, the string count.
With the third paramter missing MID will return the characters from the
fifth position to the end. In this case, the fifth and sixth character. So
was it a typo?

So the answer should be:
If mid("MyString",5,1) = Whatever then

John... Visio MVP
 
The functions are similar and the difference is in what is returned. MID,
LEFT, RIGHT return variants while MID$, LEFT$ and RIGHT$ return strings.

Some of the other $ functions are CHR$, DATE$, TRIM$, UCASE$ STR$, DIR$ and
about twenty other functions.

John... Visio MVP
 
For the VBA stuff, I use the help with VBA, but I must admit it is not as
easy in Office 2007 as in older versions.

John... Visio MVP

David Anderson said:
John,
Thanks for the info. Where exactly is all this covered in the online help?

David
Click to expand...
 
John,
I haven't tried looking in the Access 2007 online help (I've only just got
Office 2007 Pro and haven't started to use A2007 yet). I'm still using A2000
and have failed to find any of this stuff in the help, whether called from
Access itself or from the VBA coding window.

David
 
David said:
I haven't tried looking in the Access 2007 online help (I've only just got
Office 2007 Pro and haven't started to use A2007 yet). I'm still using A2000
and have failed to find any of this stuff in the help, whether called from
Access itself or from the VBA coding window.
Click to expand...


I never used A2K, but I suspect that, like every other
version of Access, you can go to the table of contents, scan
down to the VB Language Reference and find the Functions
chapter.
 
Hello John,


I remembered that I should have added the optional parameter for
the number of characters to return from the mid function as
soon as I clicked send. :-(


Actually, you are both wrong.

The OP wanted the fifth character of a six character string.

As usual, stevie's answer is way off base and will get the
first five characters of the string.
Click to expand...
Bob, you are closer, but you left out the third parameter, the
string count. With the third paramter missing MID will return
the characters from the fifth position to the end. In this
case, the fifth and sixth character. So was it a typo?

So the answer should be:
If mid("MyString",5,1) = Whatever then

John... Visio MVP

Bob Quintal said:
Lovely code, but it fails to do what the Original Poster
asked.

This is what he wanted.
If mid("MyString",5) = Whatever then

is the correct test.
Click to expand...
Click to expand...
 
Marshall,
You are right. The functions are indeed all listed in the table of contents.
I never tried that, because I assumed that an index search would always
include such basic items as the main chapter headings. Silly me, to expect
the online help to act in a logical manner! On top of such built-in quirks I
also found that my help system was corrupted in some way. Access itself
recognised there was a problem and offered to fix it, with the aid of the
original installation CD. Before this repair, no search would find either the
Right, Left or Mid Functions. Since this repair, I can now find both Right
and Mid via an Index search - but not Left. However, an Answer Wizard search
does now find all these Functions, including Left.

All these irritating complications make me nostalgic for the days of a
simple printed manual....

David
 
Back
Top