Solved

Access Query to find H99999

Posted on 2013-01-28
25
323 Views
Last Modified: 2013-01-29
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.
0
Comment
Question by:Mswetsky
  • 12
  • 5
  • 4
  • +1
25 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 50 total points
Comment Utility
use
like  "*h#####*"
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
If the H must be the first character in the field, then use:

Like "H#####*"
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
These suggestions
do not catch the number when it was in the following

DH,Info. Employee# H83784
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
The H could be upper or lower case
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
Once I get the record I need to pull only the string  "*h#####*" into another field
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
what do you mean?

post your query

this should catch it

like  "*h#####*"
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
Can I retrieve "*h#####*" into another column with instr ??
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
Return the record
And then I need to add the h????? to another field
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you may need vba codes to do that, try this


Expr1: Mid([SYMP_TEXT1],InStrRev([SYMP_TEXT1],"h"),6)
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
In this example I get the incorrect result

DH,Info. Employee# H83784
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
did you use my last post ?

Expr1: Mid([SYMP_TEXT1],InStrRev([SYMP_TEXT1],"h"),6)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
Comment Utility
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
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
Capricorn Yes I did
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
I am sorry for my incomplete original question.
When I return the correct records I need to extract that string h#####
0
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
by: fyed
I get a message GetH undefined when I try the query
0
 
LVL 39

Accepted Solution

by:
als315 earned 350 total points
Comment Utility
Look a sample DB
DBH-.accdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 1

Author Comment

by:Mswetsky
Comment Utility
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
 
LVL 39

Expert Comment

by:als315
Comment Utility
"I accepted als315 " ????
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 1

Author Closing Comment

by:Mswetsky
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now