Solved

Access Query to find H99999

Posted on 2013-01-28
25
333 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
ID: 38827439
use
like  "*h#####*"
0
 
LVL 47

Expert Comment

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

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

Author Comment

by:Mswetsky
ID: 38827482
These suggestions
do not catch the number when it was in the following

DH,Info. Employee# H83784
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 38827488
The H could be upper or lower case
0
 
LVL 1

Author Comment

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

Expert Comment

by:Rey Obrero
ID: 38827504
what do you mean?

post your query

this should catch it

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

Author Comment

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

Author Comment

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

Author Comment

by:Mswetsky
ID: 38827524
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
ID: 38827533
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
ID: 38827548
In this example I get the incorrect result

DH,Info. Employee# H83784
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38827593
did you use my last post ?

Expr1: Mid([SYMP_TEXT1],InStrRev([SYMP_TEXT1],"h"),6)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 38827600
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
ID: 38827617
Capricorn Yes I did
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38827631
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
ID: 38827652
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
ID: 38827662
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
ID: 38827669
by: fyed
I get a message GetH undefined when I try the query
0
 
LVL 39

Accepted Solution

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

Expert Comment

by:Dale Fye (Access MVP)
ID: 38827695
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
ID: 38827738
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
ID: 38827759
"I accepted als315 " ????
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38827767
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
ID: 38830770
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Open a Specific Record With a Specific Tab Page 5 31
Running sum query 6 33
Query design issue 2 24
Format a Field AFTER UPDATE 5 29
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

18 Experts available now in Live!

Get 1:1 Help Now