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

    Search based on form unbound field fromDate and toDate and table woPR field Date


    Dear All,
    Could anybody find the error in below highlighte red code as it show compile error.
    Note : Serach command based on two date field in a form unbound - fromDate, toDate
    Table : woPR field Date.

    Private Sub Search_Click()
    ' Search
    Call Search
    End Sub
    Sub 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
    Else
    strCriteria = "([Date]>= *" & me.fromDate & *" And [Date] <= *" & me.toDate & *")" // i want to click search command button after input from date and to date field value then it return all corresponding records.
    task = " select * from woPR where (" & strCriteria & ")orderBy [Date]"
    DoCmd.ApplyFilter 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
    Try using # delimiters instead of * for the dates.
    Made several other syntax changes also. Date is a reserved word in Access. I changed the table field name from Date to dDate.
    Eliminated the call to Search, as I combined the two procedures.
    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
    Last edited by davegri; 08-03-2020 at 02:15 PM. Reason: clarif

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in addition to davegri solution the syntax error is

    strCriteria = "([Date]>= *" & me.fromDate & *" And [Date] <= *" & me.toDate & *")"

    further - if you do not use the US style of date of mm/dd/yyyy you need to use the format function to make it so

    "[dDate]>= #" & format(Me.FromDate, "mm/dd/yyyy") & "#......

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, be aware that variables MUST be explicitly declared:

    Code:
    Dim strCriteria, task As String
    Here, "task" is declared as a string; "strCriteria' defaults to Variant type.
    Better is
    Code:
    Dim strCriteria as String, task As String

    And "Search" is a reserved word (Future SQL Server keyword (TSQL Ref in MSDN))

  5. #5
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    Thanks all for help..

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

Similar Threads

  1. Replies: 3
    Last Post: 10-02-2019, 01:46 PM
  2. Replies: 3
    Last Post: 11-29-2016, 10:03 AM
  3. Replies: 2
    Last Post: 05-22-2014, 06:11 PM
  4. Replies: 3
    Last Post: 12-24-2013, 04:20 PM
  5. Replies: 5
    Last Post: 06-22-2011, 08:47 PM

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