Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Unanswered: Drop Down Filter

    Right, i am trying to filter records based on what selection the person has chosen in the drop down box, when i run the filter it returns no results and when i try to run the filter again from the drop down i get an error, you entered an expression that has no value

    Here is the code
    Code:
    Dim Yesterday As Variant
        Dim UseDate As Date
        Dim today As Variant
    
        Yesterday = Date - 1
        
        If Format(Yesterday, "ddd") = "SUN" Then
        UseDate = Date - 2
        Else
        UseDate = Date - 1
        
        End If
        
        Dim SQL1 As String
        Dim SQL2 As String
        Dim SQL3 As String
        
        
        SQL1 = "SELECT Salesorderheader.orderno, Salesorderheader.SOHID, Salesorderheader.Date, Salesorderheader.NettValue, Salesorderheader.VAT, Salesorderheader.Total"
        SQL1 = SQL1 & " From Salesorderheader"
        SQL1 = SQL1 & " WHERE Salesorderheader.Date = " & Date
        
        SQL2 = "SELECT Salesorderheader.orderno, Salesorderheader.SOHID, Salesorderheader.Date, Salesorderheader.NettValue, Salesorderheader.VAT, Salesorderheader.Total"
        SQL2 = SQL2 & " From Salesorderheader"
        SQL2 = SQL2 & " WHERE Salesorderheader.Date = " & UseDate
        
        SQL3 = "SELECT Salesorderheader.orderno, Salesorderheader.SOHID, Salesorderheader.Date, Salesorderheader.NettValue, Salesorderheader.VAT, Salesorderheader.Total"
        SQL3 = SQL3 & " From Salesorderheader"
    
        
        If FilterDrop = "Todays Orders" Then
        RecordSource = SQL1
        Requery
        MsgBox RecordSource
        Else
        If FilterDrop = "Yesterdays Orders" Then
        RecordSource = SQL2
        Requery
        MsgBox RecordSource
        Else
        If FilterDrop = "All Orders" Then
        RecordSource = SQL3
        Requery
        MsgBox RecordSource
        End If
            End If
                End If
    Can anyone see a problem with this code?

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I have a similar for my database.

    In mine I have three drop downs and it shows values depending on what is in the other two. Also.... Shows 2nd depending on 1st & 3rd.... and Shows 3rd depending on 1st and 2nd.

    I can't really tell you what to do, but I can show you what I've done and hopefully it'll help you.

    Best wishes.. Christy

    - - - - -

    My tables are Tbl_Detail_CourseTitle and Tbl_Courses.
    Fields on the form are Val_Category, Val_Trainer and Val_TrainingCourse


    Behind the first drop down (Val_TrainingCourse) which selects the course title, is this SQL statement:

    SELECT DISTINCT Tbl_Detail_CourseTitle.Category
    FROM Tbl_Detail_CourseTitle RIGHT JOIN Tbl_Courses ON Tbl_Detail_CourseTitle.[Training Course] = Tbl_Courses.[Training Course]
    GROUP BY Tbl_Detail_CourseTitle.Category, Tbl_Detail_CourseTitle.[Training Course], Tbl_Courses.Trainer
    HAVING (((Tbl_Detail_CourseTitle.[Training Course]) Like [Forms]![Frm_Nav_Report]![Val_TrainingCourse]) AND ((Tbl_Courses.Trainer) Like [Forms]![Frm_Nav_Report]![Val_Trainer]))
    ORDER BY Tbl_Detail_CourseTitle.Category;

    behind the drop down under afterupdate() is the following VB code:

    Private Sub Val_Category_AfterUpdate()

    On Error GoTo Err_Val_Category

    Me.Val_TrainingCourse.Requery
    Me.Val_Trainer.Requery

    Exit_Val_Category:
    Exit Sub

    Err_Val_Category:
    MsgBox Err.Description
    Resume Exit_Val_Category

    End Sub

Posting Permissions

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