UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Is There A Function To See If A String Contains A Letter?, Access 2016    
 
   
InfoHound
post Nov 15 2017, 07:40 AM
Post#1



Posts: 1,057
Joined: 1-December 12



I have a string: "111X222_12345678911"

I want to see if there is a letter in the string. I need to do this to determine what events to create based on what I find in a string.

For example say the above string was "111X22A_12345678911". I'd want to flag this as an error because there should be an "A" in the string.

Thanks
Go to the top of the page
 
JonSmith
post Nov 15 2017, 07:43 AM
Post#2



Posts: 3,161
Joined: 19-October 10



So are you trying to make sure it matches a certain pattern(s)? and the second one is wrong because it should be a number at that position and not a letter or vice versa?

If so I would suggest a regular expression, they can be abit of a pain to get your head around at first but they are really useful in situations like that.
Can you expand on what you expect in these codes and why?

JS
Go to the top of the page
 
GroverParkGeorge
post Nov 15 2017, 07:52 AM
Post#3


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


As is often the case, there may be more than one way to do this, and more than one way, in fact, to describe the requirement.

So, as has been suggested, let's start by pinning down what you need.

Are you looking for a pattern, or are you looking for mistakenly included (or excluded) characters? In other words, is it a matter of having the right characters in the right positions--left to right--within the string? Or, are you looking for any occurrence of a letter in a string of digits?

It matters in deciding how to implement your checking function.

Also, does the presence of the "A" in your example constitute an error?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Nov 15 2017, 08:02 AM
Post#4


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


Sorry, I should have included the observation that, if you only want to check for the presence of non-numeric values in a string, you can use IsNumeric(). However, BOTH of your example strings would return false, since they both contain the letter "X", while the second one also contains the letter "A". Again, knowing the exact requirement will help refine the approach to a solution.

--------------------
Go to the top of the page
 
InfoHound
post Nov 15 2017, 08:05 AM
Post#5



Posts: 1,057
Joined: 1-December 12



The first part of the string is used for size.

In this case the size (area) would be found by multiplying the digits to the left of the "X" by the digits to the right of the "X"
"111X222_12345678911"

The result would be 24642"

In the next example
111X22A_12345678911

I'd get 2442" . I have no control over how the number is entered as it is imported from Excel.

What I'm after is a way to show an error message if there are letters in the first 3 positions before the "X" and or next 3 positions after the "X".
Go to the top of the page
 
nuclear_nick
post Nov 15 2017, 08:07 AM
Post#6



Posts: 1,401
Joined: 5-February 06
From: Ohio, USA


Either way... you can use (from another post) the 'InStr'.

First, I'll point out there are 3 parameters to InStr. Most use two, as the third is optional. The third is where you want to start the search in the string. So just in case that comes in handy later.

Second, note that the result returned is the position, so if what you are searching for, whether it be one or more characters together in the string, you're getting a number back, whether it be zero (not found) or the starting position it is found.

Third... and this is one of my favorite tricks... is knowing what is true and what is false. Generally, in VBA, 'False' is zero and 'True' is everything else.
CODE
If CBool(InStr("111X222_12345678911","A")) = True Then
  <code if true>
Else
  <code if false>
End If


Also, once you get used to it...

CODE
If InStr("111X222_12345678911","A") Then
  <code if true>
Else
  <code if false>
End If


Enjoy!

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
InfoHound
post Nov 15 2017, 08:13 AM
Post#7



Posts: 1,057
Joined: 1-December 12



Wow great stuff!
Going to use the IsNumeric function and I'm going to learn more about the CBool function.

Thank you
Go to the top of the page
 
GroverParkGeorge
post Nov 15 2017, 08:16 AM
Post#8


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


Thanks for the clarification.

So you are interested only in the first 7 characters.

There will ALWAYS be an "X" in the fourth position.

So you need to know if the three characters in positions 1-3 are numeric and if the three characters in positions 5-7 are numeric. If either of those two things is NOT true, you need to flag an error.

One way to do it, therefore, would be a combination of Mid() and IsNumeric(), using SQL or VBA, or, as was previously suggested, you could use a simple Regular Expression.

Here's one possibility: IsNumeric(Left([YourSizeFieldStringNameGoesHere],3) to check for non-digits in positions 1, 2, or 3. IsNumeric(Mid([YourSizeFieldStringNameGoesHere],4,3) to check for non-digits in positions 5, 6, or 7.

I believe there is still another way to do the same thing, btw. Hopefully someone will offer that as well.

--------------------
Go to the top of the page
 
nuclear_nick
post Nov 15 2017, 08:16 AM
Post#9



Posts: 1,401
Joined: 5-February 06
From: Ohio, USA


Sorry... saw your response after I typed that last message...

But rather than delete or edit... I'll just respond...

There are ways to retrieve the parts of the "string" you want by using a few functions together.

For example, to get everything from the left of the X...
CODE
NewString=Left("111X222_12345678911",InStr("111X222_12345678911","X")-1)


I believe if you search on 'string manipulation', you'll find lots of material.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
GroverParkGeorge
post Nov 15 2017, 08:17 AM
Post#10


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


Hm. That's fine if "A" is the ONLY possible non-digit you'll ever encounter. If a "B" or an "F" could sneak in, what do you do?

--------------------
Go to the top of the page
 
JonSmith
post Nov 15 2017, 08:20 AM
Post#11



Posts: 3,161
Joined: 19-October 10



As George mentioned, multiple ways of doing it. My preference would be to use a combination of Split and IsNumeric (see below) or a regular express as mentioned before but that might be alot to learn for a relatively simple pattern.

CODE
Public Sub TestValid(strCode As String)

    Dim strFirstChunk As String
    strFirstChunk = Split(strCode, "_")(0)
    If Not IsNumeric(Split(strFirstChunk, "X")(0)) Or Not IsNumeric(Split(strFirstChunk, "X")(1)) Then
        MsgBox "Bad dimensions"
    End If
    
End Sub
Go to the top of the page
 
nuclear_nick
post Nov 15 2017, 08:21 AM
Post#12



Posts: 1,401
Joined: 5-February 06
From: Ohio, USA


GPG...

That was just a beginning sample... (having not finished the book on regular expressions yet)... And InfoHound already mentioned knowing the 'IsNumeric'...

InfoHound...

If you have any more issues... we'll be here all life! (Well, I'm intending to be.)

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
InfoHound
post Nov 15 2017, 08:32 AM
Post#13



Posts: 1,057
Joined: 1-December 12



Fantastic guys, your help is greatly appreciated.

Cheers
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:28 PM