Full Version: Select help for Row Source
UtterAccess Forums > Microsoft® Access > Access Forms
austenr
I'm having trouble with this select statement:
CODE
SELECT DISTINCT Employee.EmployeeLast FROM Employee UNION SELECT '' from Employee ORDER BY Employee.EmployeeLast;

This code needs to go in the row source of a combo box I have that I am using to filter a report. The report has a concantanated name on it where I combine the EmployeeLast and EmployeeFirst names by doing the following in the report:
Name: [Employee].[EmployeeLast] & "," & [Employee].[EmployeeFirst]
in design view on the report and that is the format I need to check the name against in the filter.
Cant seem to get the row source in the form to take the select statement. Where am I going wrong? Thanks
austenr
Here is my Filter Code as well:
CODE
Option Compare Database
Option Explicit
Private Sub cmdApplyFilter_Click()
    Dim strFilter As String
    Dim blnFilterOn As Boolean
    Dim strCriteria As String
    
    strCriteria = "Criteria: "
    
    
    'Set Name Filter
    If Nz(Me.cboEmployeeLast, vbNullString) <> vbNullString Then
        strFilter = " AND [EmployeeLast] = '" & Me.cboEmployeeLast & "'"
        strCriteria = strCriteria & "Name = " & cboEmployeeLast
    End If
    
    
    'Set Date Range Filter
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString And Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        If Me.txtStartDate > Me.txtEndDate Then
            MsgBox "Start Date Cannot Be Greater Than End Date", vbInformation
            Exit Sub
        End If
    End If
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [MyDate] >= #" & Me.txtStartDate & "#"
        strCriteria = strCriteria & vbCrLf & "Begin Date: " & Me.txtStartDate
    End If
    
    If Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [MyDate] <= #" & Me.txtEndDate & "#"
        strCriteria = strCriteria & vbCrLf & "End Date: " & Me.txtEndDate
    End If
    
    If strFilter <> vbNullString Then
        strFilter = Mid(strFilter, 6)
        Me.txtCriteria = strCriteria
        Reports("SummaryReport").Filter = strFilter
        Reports("SummaryReport").FilterOn = True
    Else
        MsgBox "Enter some criteria", vbInformation
    End If
    
    
End Sub
Private Sub cmdClose_Click()
    DoCmd.Close acReport, "SummaryReport"
    DoCmd.Close acForm, "frmReportCriteria"
    
End Sub
Private Sub cmdRemoveFilter_Click()
        Me.txtCriteria = "Criteria: None"
        Reports("SummaryReport").Filter = vbNullString
        Reports("SummaryReport").FilterOn = False
End Sub
Private Sub Form_Open(Cancel As Integer)
    DoCmd.OpenReport "SummaryReport", acPreview
End Sub
fkegley
You need to Alias the field like this:
SELECT DISTINCT Employee.EmployeeLast FROM Employee UNION SELECT '' AS EmployeeLast from Employee ORDER BY Employee.EmployeeLast;
austenr
Thanks got it to work. How would I go about selecting multiple employees to filter on?
Nyteshade
This post was a mistake. Please disregard.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.