Query not running right in subroutine

[Historypaul]

I think this is a weird one. I've got a query doing this same thing working fine in another situation, but I get the attached error every time my subroutine tries to call this query. I tried it directly in the subroutine and now I've tried having the subroutine call the query. I get this error every time.

This is the subroutine:

Private Sub Check696_Click()
If Check696 = True Then
Form_Employees.FilterOn = True
Employee_Select.RowSource = [Employees_Current]
Else
Form_Employees.FilterOn = False
Employee_Select.RowSource = [Employees_All]
End If
Employee_Select.value = Null
End Sub

Here is the query:

SELECT Employees.ID, [Employees].[First_Name] & " " & [Employees].[Last_Name] AS Name, Employees.Last_Date
FROM Employees
WHERE (((Employees.Last_Date) Is Null))
ORDER BY [Employees].[First_Name] & " " & [Employees].[Last_Name];

I suspect my problem/solution is with quotations, but I'm stuck.

[alcapps]

have you tried breaking down the query. Like remove the order by then name field and see what works and what doesn't?

[alcapps]

is your query code in a string or a normal query object?

if it is in a string then you need to change the " double quotes to singles '.

I would also remove tHe ( ) in the where clause too.. WHERE [LAST_DATE] IS NULL

LAST_DATE needs [] around it that might be your issue as well..

it is tough when you add special chars to field names because they have to wrapped everywhere.. with []'s

[Historypaul]

Another thought - what I'm doing here is using a checkbox control to turn on and off the forms filter that filters out former employees or shows all. I'm using a combo box control for a navigation. Can I just tie the rowsource of the combo control to the forms current recordset so one updates the other?

[alcapps]

Sure you can that's easy to do

[Historypaul]

It's definitely in the first line SELECT statement. When I hit debug, it opens to that line. When I enter that line by itself, I get a "expected end of statement" error usually.

[Historypaul]

Sure you can that's easy to do

Well thanks ya big tease...

So if I set the on click or on focus event for my combobox something like, "Set Me.Recordset = Forms!Form1.Recordset" then what would I use in my Row Source property? "SELECT Employee_ID from Me.Recordset"?

[Historypaul]

For what it's worth, I created the Employees_Current and Employees_All queries, then used a query in my rowsource property plucking the employee ID's and names from those queries and it works just fine. It's a little convoluted and not very eloquent and I think there's got to be a better way to do this in VBA, but this one fixed the problem. But if you could help me out with that recordset method instead, it would be more universally useful on my other forms and wouldn't have to build queries to query queries to get this done...

[alcapps]

can you put a database together that has the data and form in it so that we can see the issue and maybe correct it and post it back?