Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Unanswered: need help changing a Query

    Hiya

    I have my database which searches my teaching documents. so far, when i enter an article, i can chose to add Keyword, Media Type (webpage, text dox, image), Rating (1-5) and Topic. When i search for an article back, those fields HAVE to be entered otherwise nothing is returned. I would like it so that i can search via JUST rating, or Media Type and Topic. Can anyone help?

    Thanks

    Mark

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I created a form with three fields on it. The names are txtRating, txtMediaType, and txtTopic. Then added a button named cmdSearch, and put the following code into the Click event for that button.
    Code:
    Private Sub cmdSearch_Click()
    'Build the Where clause
    Dim SQLWhere As String
      
      If Len(Nz(Me.txtRating)) > 0 Then   'Rating criteria given
        SQLWhere = "Rating Like '" & txtRating & "*'"
      End If
      If Len(Nz(Me.txtMediaType)) > 0 Then  'Media Type criteria given
        If Len(SQLWhere) > 0 Then
          SQLWhere = SQLWhere & " AND "
        End If
        SQLWhere = SQLWhere & "MediaType Like '" & Me.txtMediaType & "*'"
      End If
      If Len(Nz(Me.txtTopic)) > 0 Then  'Topic criteria given
        If Len(SQLWhere) > 0 Then
          SQLWhere = SQLWhere & " AND "
        End If
        SQLWhere = SQLWhere & "Topic Like '" & Me.txtTopic & "*'"
      End If
      DoCmd.OpenForm "frmFoundTeachingDocuments", acFormDS, , SQLWhere
      
    End Sub
    The form named "frmFoundTeachingDoucments uses the table "tblTeachingDocuments" as it's Record Source. Table "tblTeachingDocuments" is my version of your table that describes your teaching documents.
    Hope this helps,

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    or from a query

    Select *
    From table
    Where (media = [your value] or media is null) AND (topic = [your value] or topic is null) AND etc.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •