Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Search code help

Posted on 2013-02-04
14
Medium Priority
?
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 48

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 48

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 48

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

618 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