Results 1 to 8 of 8
  1. #1
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94

    search Button based on three field (Forms)

    Dear All,

    Below is the code to search based on fromDate and toDate its working fine But i want to include one more field in form named costCode.
    So please help me to modify this code based on more criteria entered as a new field text costCode.
    ex: fromdate 2020-07-01
    toDate 2020-08-01
    costCode HVAC (from left match any four character of entered string table woPR field name CCODE form unbound name costCode )

    Please modify in below code.

    Private Sub Search_Click()
    ' Search

    Dim strCriteria, task As String
    Me.Refresh
    If IsNull(Me.FromDate) Or IsNull(Me.ToDate) Then
    MsgBox " Enter the Date Range", vbInformation, "Date Range required "
    Me.FromDate.SetFocus
    Else
    'i want to click search command button after input from date and to date field value then it return all corresponding records.
    strCriteria = "[dDate]>= #" & Me.FromDate & "# And [dDate] <= #" & Me.ToDate & "#"
    task = " select * from WOpr where " & strCriteria & " order By [dDate]"


    'Debug.Print task
    'DoCmd.ApplyFilter , task
    Me.RecordSource = task
    End If
    End Sub

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Will the costCode be required or optional for the search? Is it always 4 characters? Do you have a list of all valid CCode?

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    aligahk06-davegri-v01.zip

    The attached DB contains the below code:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdShowAll_Click()
        Me.RecordSource = "Select * from woPR;"
    End Sub
    
    
    Private Sub Search_Click()
        'i want to click search command button after input from date and to date field value then it return all corresponding records.
        Dim strCriteria As String, task As String
        Me.Refresh
        If IsNull(Me.FromDate) Or IsNull(Me.ToDate) Then
            MsgBox " Enter the Date Range", vbInformation, "Date Range required "
            Me.FromDate.SetFocus
            Exit Sub
        End If
        'if costCode is required in the search, uncomment the next 5 lines
    ''    If IsNull(Me.costCode) Then
    ''        MsgBox "costCode is required", vbInformation, "costCode required"
    ''        Me.costCode.SetFocus
    ''        Exit Sub
    ''    End If
            If Not IsNull(Me.costCode) Then
                strCriteria = "CCode ='" & Me.costCode & "' AND "
            End If
            strCriteria = strCriteria & "[dDate]>= #" & Me.FromDate & "# And [dDate] <= #" & Me.ToDate & "#"
            task = " select * from woPR where " & strCriteria & " order By [dDate]"
            'Debug.Print task
            
            Me.RecordSource = task
            If Me.RecordsetClone.RecordCount = 0 Then
                MsgBox "no match"
                Me.RecordSource = "select * from woPR"
            End If
    End Sub
    You can make the costCode required or not.
    If you have a list of costCodes, you can change the textbox to a combobox to select a valid costCode.

  4. #4
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    I changed text box to combo box. the name of combox is combo76 .while selecting the list from combo box and toDate and fromDate then clicking on search command it doesn't give the desired result.
    So please modify the code based on combo box. name combo76.
    2. i have a list of all valid ccode, so i tried to select from combo box.
    3. All ccode is not fixed character, some one is 4 character and some one is more than 4 characters.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    So please modify the code based on combo box. name combo76.
    Need more information. How does the ccode list exist - in a table? Something else?
    The combobox needs to have its rowsource set by the list.

    aligahk06-davegri-v02.zip

    For example, this DB has a list that was typed in by me.

  6. #6
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94

    Attached DB for ref Serach command based on combo box

    Dear All,

    Based on ongoing thread . I do attached DB.

    Please look the search command.

    I have two tables one is woPR and other is tblCcode as one to one relationship defined.
    But search command not working properly based on combox.

    Please look at code.
    only for fromDate, toDate and cost centre( i.e combo combo84)
    Please ignore the pr No field query.

    thanks,
    aligah06
    Attached Files Attached Files

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    aligahk06-davegri-v03.zip

    See attached. Changed the combo box row source from value list to your tbluCCodes.

  8. #8
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    Thanks for ur valuable time

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-04-2020, 12:13 AM
  2. Replies: 1
    Last Post: 11-30-2016, 03:41 AM
  3. Replies: 6
    Last Post: 09-02-2016, 02:12 PM
  4. Replies: 3
    Last Post: 03-23-2016, 12:45 PM
  5. Multi field search forms
    By Rogue in forum Forms
    Replies: 10
    Last Post: 05-14-2013, 09:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums