?
Solved

Search code help

Posted on 2013-02-04
14
Medium Priority
?
334 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 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 46

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
 
LVL 46

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 49

Expert Comment

by:Dale Fye
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 46

Accepted Solution

by:
aikimark earned 2000 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 46

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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