Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39
    Yes I ended up doing it like that but it is going to be one hell of a code as I have so many fields...I have a total of 15 searchable fields...is this the only way to do this?

  2. #17
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It is the accepted way, I am not sure if it is the only way. You can make a loop which will loop thru all the fields. Will have to google it.

  3. #18
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #19
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    xxxxxxxxxx

  5. #20
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If I read that correctly, Orange, it uses OR not AND. Is that right?

    Here is a piece of code that builds the SQL based on AND.
    (1) all fields must have its format set - some type of "Date", some type of "Number"
    (2) there has to be a way to identify the fields - I have "txt" in front of the fields I was using to search
    (3) each field's control source must have the correct name - use this query as the record source for the form (without the criteria)

    Code:
    Private Sub Cmd_Search_Click()
    
        Dim strSQL As String, ctl As Control
    
        strSQL = "SELECT * From Table1 WHERE (1=1)"
    
        For Each ctl In Me.Controls
            If Left(ctl.Name, 3) = "txt" Then
                If ctl.Format Like "*Date*" Then
                    If Not IsNull(Me(ctl.Name)) Then strSQL = strSQL & " And ([" & ctl.ControlSource & "] =#" & Me(ctl.Name) & "#)"
                ElseIf ctl.Format Like "*Number*" Then
                    If Not IsNull(Me(ctl.Name)) Then strSQL = strSQL & " And ([" & ctl.ControlSource & "] =" & Me(ctl.Name) & ")"
                Else
                    If Not IsNull(Me(ctl.Name)) Then strSQL = strSQL & " And (([" & ctl.ControlSource & "] Is Null OR [" & ctl.ControlSource & "] Like '*" & Me(ctl.Name) & "*'))"
                End If
            End If
        Next
        
        Debug.Print strSQL
        
        Set ctl = Nothing
    
    End Sub

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple Text criteria working oddyly
    By bruegel in forum Queries
    Replies: 3
    Last Post: 04-05-2016, 08:24 AM
  2. Simple datasheet view not working
    By dcol in forum Forms
    Replies: 2
    Last Post: 12-23-2015, 04:34 AM
  3. simple delete SQL statement not working
    By markjkubicki in forum Programming
    Replies: 9
    Last Post: 05-22-2013, 05:49 PM
  4. Replies: 1
    Last Post: 07-30-2011, 07:58 AM
  5. Simple Nav Form Code Not Working
    By alsoto in forum Forms
    Replies: 10
    Last Post: 04-10-2009, 09:30 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