Access Query to find H99999

In an Access 2010 Query,
I have a field with a string of 75 characters and I need to find the the record ID numbers Named [LOG_ID] where the field named [SYMP_TEXT1]  has an "H" followed by 5 numbers.
I tried like "*h#####" but it doesn't work.
LVL 1
Mitch SwetskyBusiness AnalystAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Look a sample DB
DBH-.accdb
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
use
like  "*h#####*"
0
 
Dale FyeCommented:
If the H must be the first character in the field, then use:

Like "H#####*"
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Mitch SwetskyBusiness AnalystAuthor Commented:
These suggestions
do not catch the number when it was in the following

DH,Info. Employee# H83784
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
The H could be upper or lower case
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Once I get the record I need to pull only the string  "*h#####*" into another field
0
 
Rey Obrero (Capricorn1)Commented:
what do you mean?

post your query

this should catch it

like  "*h#####*"
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Can I retrieve "*h#####*" into another column with instr ??
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Return the record
And then I need to add the h????? to another field
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
SELECT [6040].LOG_ID, [6040].SYMP_TEXT1, InStr([SYMP_TEXT1],"H") AS HPosit, "H" & (Mid([SYMP_TEXT1],([HPosit]+1),6)) AS Expr1
FROM 6040
WHERE ((([6040].SYMP_TEXT1) Like "*H#####"));
0
 
Rey Obrero (Capricorn1)Commented:
you may need vba codes to do that, try this


Expr1: Mid([SYMP_TEXT1],InStrRev([SYMP_TEXT1],"h"),6)
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
In this example I get the incorrect result

DH,Info. Employee# H83784
0
 
Rey Obrero (Capricorn1)Commented:
did you use my last post ?

Expr1: Mid([SYMP_TEXT1],InStrRev([SYMP_TEXT1],"h"),6)
0
 
Dale FyeConnect With a Mentor Commented:
No, you are not going to be able to do it with a simple use of instr or instrrev.

Your best bet is to create a function, something like:
Public Function GetH(FindIn as Variant) as Variant

    Dim myString as string

    'If the value passed was NULL, then exit the function, returning NULL
    if FindIn & "" = "" Then Exit Function

    myString = FindIn
    While instr(myString, "H") > 0
        myString = Mid(MyString, instr(myString, "H"))
        If Left(myString, 6) Like "H#####" Then
            GetH = Left(myString, 6)
            Exit Function
        End If
    Wend

    GetH = NULL

End Function

Open in new window


Then you can call this in your SQL:

SELECT GetH([SYMP_TEXT1]) FROM yourTable WHERE GetH([SYMP_TEXT1]) IS NOT NULL
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Capricorn Yes I did
0
 
Rey Obrero (Capricorn1)Commented:
what is wrong with the result form this record

In this example I get the incorrect result

DH,Info. Employee# H83784


it should return H83784

take note of the function i used  INSTRREV
0
 
als315Commented:
Try this function:
Function getid(A As Variant) As Variant
Dim i As Integer, C As String, D As String
getid = Null
If IsNull(A) Then Exit Function
For i = 1 To Len(A) - 5
    C = Mid(A, i, 1)
    If C = "H" Or C = "h" Then
        D = Trim(Mid(A, i + 1, 5))
        
        If Len(D) = 5 And IsNumeric(D) Then
            getid = C & D
            Exit For
        End If
    End If
Next i
End Function

Open in new window

Sample strings:
DH,Info. Employee# H83784
DH,Info. Employee# H8378 DH,Info.  H83784Employee#
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I am sorry for my incomplete original question.
When I return the correct records I need to extract that string h#####
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
by: fyed
I get a message GetH undefined when I try the query
0
 
Dale FyeCommented:
mswetsky,

You will have to put that code in a standard code module, not one that belongs to a specific form or report.  

In the VBA code window, right click in the Project window(normally in the upper left), then select Insert Module.  Then paste the code into that new module, and try again.
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I really appreciate all of  the suggestions provided I accepted als315 because it included a sample that worked with a mock table etc.

I always learn from your suggestions THANK YOU ALL
0
 
als315Commented:
"I accepted als315 " ????
0
 
Dale FyeCommented:
Oops, you actually accepted my solution, not als's.

Click on the "Request Assistance" link in the bottom right of your original question and ask the moderators to re-open the question so you can redistribute the points.
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Thank you all for your suggestions
I appreciate the replies and chance to learn.
I selected als because there was a sample database with an easy to use solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.