Select All Help, One More Time

[channell]

Hello,

I received some help to a question I had a while back. Well, I have one
more question regarding the same topic.

I have a combo box and a list box. The combo box is a filter for the list
box. Both work exactly how I need them to, except when I pass the union
query to SelectAll.

Here is what I have:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT 0 As SelectAll,"-All-" As PlaceHolder FROM
tPRELOADPOSITION ORDER BY BELT;

I have messed around with this and tried a few things, but I haven't quite
figured it out yet.

If I use this, then the people WITHOUT a [BELT ID] show up... I need the
exact opposite, I need those who HAVE a [BELT ID] to show up.

I appreciate it very much! Thanks!

 

[Dorian]

I dont see how [BELT ID] is being filtered at all.
Also, I dont see the point of the second part of your UNION since it selects
only literals and no columns.
The syntax to do what you state is
SELECT .... WHERE [BELT ID] IS NOT NULL
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[channell]

Alright, here is more information-

ComboBoxFilter Row Source :
SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT 0 As SelectAll,"-All-" As PlaceHolder FROM
tPRELOADPOSITION ORDER BY BELT;

ComboBoxFilter VBA Code:
Private Sub Combo55_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [qEMPLOYEES UNION].[Employee ID],[qEMPLOYEES
UNION].[PRELOAD POSITION],[qEMPLOYEES UNION].LastNameFirstName FROM
[qEMPLOYEES UNION] Where [qEMPLOYEES UNION].[PRELOAD POSITION] = " &
Me![Combo55] & " ORDER BY [qEMPLOYEES UNION].LastNameFirstName; "
strSQL = strSQL
Me!ListEmployees1.RowSource = strSQL
End Sub

ListBoxNames Row Source:
SELECT [qEMPLOYEES UNION].[Employee ID], [qEMPLOYEES UNION].[PRELOAD
POSITION], [qEMPLOYEES UNION].LastNameFirstName FROM [qEMPLOYEES UNION] ORDER
BY [qEMPLOYEES UNION].LastNameFirstName;

The ComboBoxFilter needs to Filter the ListBoxNames. It does the job, only
up until I Select "-All-", when it returns nothing in the listbox.

I am not entirely sure where to put the WHERE Clause that you are talking
about. Thanks!

-Scott Channell

I dont see how [BELT ID] is being filtered at all.
Also, I dont see the point of the second part of your UNION since it selects
only literals and no columns.
The syntax to do what you state is
SELECT .... WHERE [BELT ID] IS NOT NULL
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Hello,

I received some help to a question I had a while back. Well, I have one
more question regarding the same topic.

I have a combo box and a list box. The combo box is a filter for the list
box. Both work exactly how I need them to, except when I pass the union
query to SelectAll.

Here is what I have:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT 0 As SelectAll,"-All-" As PlaceHolder FROM
tPRELOADPOSITION ORDER BY BELT;

I have messed around with this and tried a few things, but I haven't quite
figured it out yet.

If I use this, then the people WITHOUT a [BELT ID] show up... I need the
exact opposite, I need those who HAVE a [BELT ID] to show up.

I appreciate it very much! Thanks!

 

[BruceM]

I used an alias (EU for qEmployees Union) to make this more compact in this
forum. It is not required.

Consider this string:

strSQL = "SELECT EU.[Employee ID], _
EU.[PRELOAD POSITION], _
EU.LastNameFirstName _
FROM [qEMPLOYEES UNION] as EU
WHERE EU.[PRELOAD POSITION] = " & 0

How many records have 0 as the value in [PRELOAD POSITION]? If none, then
that is what you will get when you select "All" from the combo box. Adding
"All" as a combo box option leaves you with the necessity of dealing with
that selection. You could do something like this:

strSQL = "SELECT EU.[Employee ID], _
EU.[PRELOAD POSITION], _
EU.LastNameFirstName _
FROM [qEMPLOYEES UNION] as EU"

If Me.Combo55 <> 0 Then
strSQL = strSQL & " WHERE EU.[PRELOAD POSITION] = " & Me.Combo55
End If

strSQL = strSQL & " ORDER BY EU.LastNameFirstName"

The idea is that when "All" is selected there is no WHERE condition.

Alright, here is more information-

ComboBoxFilter Row Source :
SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT 0 As SelectAll,"-All-" As PlaceHolder FROM
tPRELOADPOSITION ORDER BY BELT;

ComboBoxFilter VBA Code:
Private Sub Combo55_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [qEMPLOYEES UNION].[Employee ID],[qEMPLOYEES
UNION].[PRELOAD POSITION],[qEMPLOYEES UNION].LastNameFirstName FROM
[qEMPLOYEES UNION] Where [qEMPLOYEES UNION].[PRELOAD POSITION] = " &
Me![Combo55] & " ORDER BY [qEMPLOYEES UNION].LastNameFirstName; "
strSQL = strSQL
Me!ListEmployees1.RowSource = strSQL
End Sub

ListBoxNames Row Source:
SELECT [qEMPLOYEES UNION].[Employee ID], [qEMPLOYEES UNION].[PRELOAD
POSITION], [qEMPLOYEES UNION].LastNameFirstName FROM [qEMPLOYEES UNION]
ORDER
BY [qEMPLOYEES UNION].LastNameFirstName;

The ComboBoxFilter needs to Filter the ListBoxNames. It does the job,
only
up until I Select "-All-", when it returns nothing in the listbox.

I am not entirely sure where to put the WHERE Clause that you are talking
about. Thanks!

-Scott Channell

I dont see how [BELT ID] is being filtered at all.
Also, I dont see the point of the second part of your UNION since it
selects
only literals and no columns.
The syntax to do what you state is
SELECT .... WHERE [BELT ID] IS NOT NULL
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".

Hello,

I received some help to a question I had a while back. Well, I have
one
more question regarding the same topic.

I have a combo box and a list box. The combo box is a filter for the
list
box. Both work exactly how I need them to, except when I pass the
union
query to SelectAll.

Here is what I have:

SELECT tPRELOADPOSITION.[BELT ID], tPRELOADPOSITION.BELT FROM
tPRELOADPOSITION UNION SELECT 0 As SelectAll,"-All-" As PlaceHolder
FROM
tPRELOADPOSITION ORDER BY BELT;

I have messed around with this and tried a few things, but I haven't
quite
figured it out yet.

If I use this, then the people WITHOUT a [BELT ID] show up... I need
the
exact opposite, I need those who HAVE a [BELT ID] to show up.

I appreciate it very much! Thanks!