Results 1 to 5 of 5
  1. #1
    Dyee4613 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4

    Search by Date

    Hi Guys. I'm trying to do a search by date form. Unfortunately, I keep running into an error with my code. Here is the video I'm using for reference: https://www.youtube.com/watch?v=EFQ6ClG3KUs

    The error I'm getting is "The expression on click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control"




    Code:
    Option Compare Database
    
    Private Sub Command344_Click()
    'Search Button'
    Call Search
    End Sub
    
    
    Sub Search()
    Dim strCriteria, task As String
    
    
    Me.Refresh
    If IsNull(Me.txtboxbegin) Or IsNull(Me.txtboxend) Then
        MsgBox "Please Enter the Date Range", vbInformation, "Date Range Required"
        Me.txtboxbegin.SetFocus
    
    
    Else
        strCriteria = "([Time] >= #" & Me.txtboxbegin & "# And [Time] <= #" & Me.txtboxend & "#)"
        task = "select * from Assets where (" & strCriteria & ") order by [Time]"
        DoCmd.ApplyFilter task
        
    End If
    
    
    
    
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
     
    strCriteria = "([Time] >= #" & Me.txtboxbegin & "# And [Time] <= #" & Me.txtboxend & "#)"
        
     me.filter =  strCriteria
     me.filterOn = true

  3. #3
    Dyee4613 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4
    Thank you! It's working now. I have two more questions for the forums:

    1) How would I modify the code to filter to hasdefects2 = 2? I assume it's in the where section but I can't seem to figure out the syntax
    2) I want to create a search function that would look like this...

    Select Asset.ID, Asset.Time, Asset.Truck, Asset.Company, Asset.VehicleCondition
    From Assets
    Where Asset.ID like "txtbox2" OR Asset.Time like "txtbox2" OR asset.company like "txtbox2" Or asset.vehiclecondition like "txtbox2";

    I figured it would look something like this

    "SELECT * from Assets where Asset.ID like "*me.txtbox2*" OR Asset.Trucks like "*me.txtbox2*" OR Assets.odometer Like "*me.txtbox2*";

    But I'm horribly off somewhere. Will rep all helpful answers, thank you!
    Last edited by Dyee4613; 03-26-2018 at 02:59 PM.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    youre confusing filter from criteria.

    you only have 1 query, all records...
    select * from table

    the 'filter' i gave keeps the query unchanged, and only filters the existing records.
    then youd do the same for another filter....

    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here
    Code:
    Dim strCriteria, task As String
    Task is declared as a string and strCriteria defaults to a variant type.
    You MUST explicitly declare variables
    Code:
    Dim strCriteria As String, task As String
    "TIME" is a reserved word and a built in function in Access and shouldn't be used as an object name.


    You MUST concatenate variables to create the string
    Code:
    SELECT * from Assets where Asset.ID like *" & me.txtbox2 & "* OR Asset.Trucks  like *" & me.txtbox2 & "* OR Assets.odometer Like *" & me.txtbox2 & "*;"
    I'm going to guess that "ID" is a number and the other two fields are text. With text, you MUST to use delimiters; numbers do not need delimiters.
    The key word "LIKE" is not useful for numbers: (this is untested)
    Code:
    SELECT * FROM Assets WHERE Asset.ID = " & me.txtbox2 & " OR Asset.Trucks LIKE '*" & me.txtbox2 & "*' OR Assets.odometer LIKE '*" & me.txtbox2 & "*' ;"

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

Similar Threads

  1. Replies: 6
    Last Post: 04-19-2016, 03:58 PM
  2. Replies: 4
    Last Post: 04-21-2015, 07:12 PM
  3. Search for date
    By azhar2006 in forum Forms
    Replies: 12
    Last Post: 07-10-2014, 10:02 AM
  4. Replies: 9
    Last Post: 11-25-2013, 04:33 PM
  5. Replies: 3
    Last Post: 09-11-2013, 09:49 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