Solved

Search code help

Posted on 2013-02-04
14
315 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

808 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