Solved

Search code help

Posted on 2013-02-04
14
288 Views
Last Modified: 2013-02-06
Hi Experts,
I have a question about the Search coding.  I ahve a table that contained a field "CHDOCKET", this field is a numeric and letter, for example, it stores "00D012345", "00D023456", "00D45678" ect, sometimes the CHDOCKET is length is 9 digits (included letter and number), sometimes the length is 8 digits (if it's 8 then it missing a 0 after the D).  what I want to do is:
I created a form for user to search if the CHDOCKET is in the table or not.  When user entered either 8 digits of the CHDOCKET on the Search form of the text box, if the CHDOCKET is there, then show it otherwise give them the message that there is not such CHDOCKET in the table.   I problem now is if user enter "00D12345" then the message came out, but it should be show "00D012345" for user because the 00D12345 is the same as "00D12345" or if the user entered "00D045678" then it should put the "00D45678".  below is the code that I'm having now, please help.
thanks,

Private Sub cmdSearch_Click()
Dim DocketRef As String
Dim strSearch As String
   
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
    End If
'---------------------------------------------------------------
 
'Performs the search using value entered into txtSearch
'and evaluates this against values in DocketNo
       
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("CHDOCKET")
    DoCmd.FindRecord Me!txtSearch
       
    CHDOCKET.SetFocus
    DocketRef = CHDOCKET.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text

    If DocketRef = strSearch Then
        'MsgBox "Match Found For: " & " Docket# " & "" & strSearchForDocket, , "Congratulations!"
        If Mid(CHDOCKET, 4, 1) = 0 Then
           CHDOCKET.SetFocus
           txtSearch = ""
        'If value not found sets focus back to txtSearch and shows msgbox
        Else
              txtSearch = "like '" & Left(CHDOCKET, 3) & Right(CHDOCKET, 5) '""
              CHDOCKET.SetFocus
              txtSearch = ""
           'End If
        End If
    Else
        MsgBox "Match Not Found For: " & " Docket# " & "" & strSearchForReqFile1 & " - Please Try Again.", _
        , "Invalid Search Criterion!"
        txtSearch.SetFocus
       'End If
    End If
End Sub

I know the problem is in:      
txtSearch = "like '" & Left(CHDOCKET, 3) & Right(CHDOCKET, 5) '""
but I don't know what else I can put.
0
Comment
Question by:jodstrr2
  • 7
  • 4
  • 3
14 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38852516
If "00D012345" is equivalent to "00D12345", then why don't you fix the database by requiring that this field contain 9 digits (or 8)?

It would be easy to identify all those records where LEN([CHDOCKET]) = 8 and then modify them using an update query.
0
 

Author Comment

by:jodstrr2
ID: 38852537
The table contained all CHDOCKET with either 8 or 9 digits is a history table and I don't want to update anything in the history table, user only allow to search if the CHDOCKET is in the history table not update them.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38852603
OK,

Given that, if the field contains 9 digits, will the number after the "D" always be a zero?

In a query, the criteria might look like:

WHERE (Len(forms!yourFormName.txtSearch & "") = 9
               AND [CHDOCKET] = forms!yourFormName.txtSearch)
OR  (Len(forms!yourFormName.txtSearch & "") = 9
        AND Mid(Forms!yourFormName.txtSearch, 4, 1) = "0"
        AND [CHDOCKET] = Left(forms!yourFormName.txtSearch, 3)  & Mid(forms!yourFormName.txtSearch, 5))
OR (Len(forms!yourFormName.txtSearch & "") = 8
              AND [CHDOCKET] = forms!yourFormName.txtSearch)
OR  (Len(forms!yourFormName.txtSearch & "") = 8
        AND [CHDOCKET] = Left(forms!yourFormName.txtSearch, 3)  & "0" & Mid(forms!yourFormName.txtSearch, 5))

Or, you could also create a function which you pass the value of [CHDOCKET] and your search field and do similar assessment.
0
 

Author Comment

by:jodstrr2
ID: 38852675
yes, if the field contained 9 digits, the number after the "D" is always be a Zero.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38853241
I think my approach might be to change your code.

Your code is currently only checking if the 4th character is a zero.  I think it should first check to see if the user typed 8 or 9 characters.  Only if the user typed 8 characters would you need to accommodate alternative docket values.

If the user typed 9 characters, just search for that string.
0
 

Author Comment

by:jodstrr2
ID: 38855213
That's what I stuck on the part of to check for the if the user type 8 or 9 characters.
0
 

Author Comment

by:jodstrr2
ID: 38859972
I want clarify that what I want to do in the search code.
1). if user enter 00d012345 (which is 9 digits) in the txtSearch box, but in the table this number was entered as 00d12345, I would like to pull this number from the table because they are the same.
2). if user enter 00D34567 (which is 8 digits) in the txtSearch box, but in the table this number was entered as 00D034567, I would like to pull this number from the table.

below is the code I'm trying to do if user enter 9 digits of the CHDOCKET wihch is for #1, but it seems not working, can you please help take a look what I did wrong.
Thanks

If DocketRef = strSearch Then
       If Len(txtSearch) = 9 Then
          If Mid(txtSearch, 4, 1) = 0 Then
             txtSearch = Left(txtSearch, 3) & "0" & Right(txtSearch, 5)
         Else
             txtSearch = Left(txtSearch, 3) & Right(txtSearch, 5)
         End If
     Else
         CHDOCKET.SetFocus
         txtSearch = ""          
     End If
End if
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

Expert Comment

by:aikimark
ID: 38860008
Do you need to determine whether the docket value exists?
Do you need to determine whether the docket value does not exist?
or just position the table on the row with that docket value?
0
 

Author Comment

by:jodstrr2
ID: 38860033
I try to do is if either 00D012345 or 00D12345 are not in the table then there will be a message "this Docket is not found".
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38860106
Try a function:
Public Function fnCHDocket(TableValue as Variant, SearchFor as Variant) as Boolean

    if (TableValue & "" = "") OR (SearchFor & "" = "") Then
       fnCHDocket = false
    elseif LEN(TableValue) <7 OR Len(TableValue) > 9 then
       fnCHDocket = False
    elseif LEN(SearchFor) < 7 or Len(SearchFor) > 9 then
       fnCHDocket = False
    Else

        If Len(TableValue) = 8 then
            TableValue = Left(TableValue, 3) & "0" & Mid(TableValue, 4)
        endif

        if Len(SearchFor) = 8 then
            SearchFor = Left(SearchFor, 3) & "0" & Mid(SearchFor, 4)
        endif

        fnCHDocket = (TableValue = SearchFor)
   
    End IF

End Function

Open in new window

You could use this function in the Filter property of a form, or in a DLOOKUP or whatever.  In a Form Filter, it might look like:
Private Sub txtSearch_AfterUpdate

    me.Filter = "fnCHDocket([CHDOCKET], '" & forms!yourFormName.txtSearch & "') = -1"
    me.filterOn = true

End Sub

Open in new window

if you just need to determine whether a match exists, you could use something like:
Private Sub txtSearch_AfterUpdate

    dim strCriteria as string
    strCriteria = "fnCHDocket([CHDOCKET], '" & forms!yourFormName.txtSearch & "') = -1"
    if dCount("ID", "yourTable", strCriteria) > 0 then
       msgbox "Value already exists!"
    endif

End Sub

Open in new window

0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 38860142
Since you didn't post the name of the table, I used a generic DocketTable name in the DFirst() function.  You will need to change this.

Private Sub cmdSearch_Click()
    Dim DocketRef As String
    Dim strSearch As String
    Dim strDockets(8 To 9) As String
    
    Select Case True
        Case IsNull(Me![txtSearch]), Len(Me![txtSearch]) < 8, Len(Me![txtSearch]) > 9
            MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
            Me![txtSearch].SetFocus
            Exit Sub
    End Select
'---------------------------------------------------------------
  
'Performs the search using value entered into txtSearch
'and evaluates this against values in DocketNo
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("CHDOCKET")
    'DoCmd.FindRecord Me!txtSearch
    strSearch = Me!txtSearch
    Select Case Len(strSearch)
        Case 8
            strDockets(8) = strSearch
            strDockets(9) = Left$(strSearch, 3) & "0" & Mid$(strSearch, 4)
        Case 9
            strDockets(8) = Left$(strSearch, 3) & Mid$(strSearch, 5)
            strDockets(9) = strSearch
        Case Else
    End Select
    strSearch = DFirst("CHDOCKET", "DocketTable", "CHDOCKET In('" & Join(strDockets, "','") & "')")
    If Len(strSearch) = 0 Then
        MsgBox "Match Not Found For: " & " Docket# " & "" & Me!txtSearch & " - Please Try Again.", _
        , "Invalid Search Criterion!"
        txtSearch.SetFocus
    Else
        CHDOCKET.SetFocus
        DoCmd.FindRecord strSearch
    End If
End Sub

Open in new window

0
 

Author Comment

by:jodstrr2
ID: 38860433
Hi aikimark, I tried your code, that's exactly what I was looking for but just a slight problem when I entered a docket# that is not existing in the FP_Child table, I did not get the message instead I got it "Run -time error '94':  "invalid use of null" error message, the problem is in line 30 on your code.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38860475
Change that line to:
    If IsNull(strSearch) Then

Open in new window


and change the definition of the variable to:
    Dim strSearch As Variant

Open in new window

0
 

Author Closing Comment

by:jodstrr2
ID: 38860497
Thank you so very much aikimark!!!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
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 …

746 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

9 Experts available now in Live!

Get 1:1 Help Now