Listbox Rowsource

[Bob Quintal]

I want to use a table as a listbox rowsource.
But here is the catch. I want to add the record "ALL" to the
listbox without adding "ALL" as a record in the table.

Thanks
DS

Create a union query.
SELECT "<< ALL >>" AS field1 from mytable
UNION SELECT fieldname from mytable.

Use that as the recordsource for the listbox.
If you need multiple fields, use "" as field2, etc.
The number of columns must agree in both select statements.
The "from mytable" in the first select will return 1 row even if
the table has many, and is required.

 

[fredg]

I want to use a table as a listbox rowsource.
But here is the catch. I want to add the record "ALL" to the listbox
without adding "ALL" as a record in the table.

Thanks
DS

Without knowing your actual situation, or the field names you wish to
include, you can adapt the following.
Change the table and field names to your actual table and field names.

Create a Union query.

Select YourTable.SomeField from YourTable
Union select "<All>" from YourTable
Order by [SomeField];

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

Make this query the Rowsource of the List box.

 

[DS]

I want to use a table as a listbox rowsource.
But here is the catch. I want to add the record "ALL" to the listbox
without adding "ALL" as a record in the table.

Thanks
DS

 

[Bob Quintal]

OK all of the other listboxes work except this one which has a
criteria.

SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
FROM tblItemTypes UNION SELECT 0,"All Types" FROM tblItemTypes
WHERE tblItemTypes.ItemTypeID Not Like 5
ORDER BY ItemTypeName;

it's not excluding the one record.

Thanks
DS

Try
SELECT
tblItemTypes.ItemTypeID,
tblItemTypes.ItemTypeName
FROM tblItemTypes
WHERE tblItemTypes.ItemTypeID Not Like 5
UNION SELECT
0 as ItemTypeID,
"All Types" as ItemTypeName
FROM tblItemTypes
ORDER BY ItemTypeName;

 

[Bob Quintal]

Works Great Thanks
PS I did have to add another field so your advice came in
handy! DS

It's a lesson I learned the hard way, a long long time ago.

 

[DS]

I want to use a table as a listbox rowsource.
But here is the catch. I want to add the record "ALL" to the listbox
without adding "ALL" as a record in the table.

Thanks
DS

Without knowing your actual situation, or the field names you wish to
include, you can adapt the following.
Change the table and field names to your actual table and field names.

Create a Union query.

Select YourTable.SomeField from YourTable
Union select "<All>" from YourTable
Order by [SomeField];

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

Make this query the Rowsource of the List box.

Works great!
Thanks
DS

 

[DS]

Create a union query.
SELECT "<< ALL >>" AS field1 from mytable
UNION SELECT fieldname from mytable.

Use that as the recordsource for the listbox.
If you need multiple fields, use "" as field2, etc.
The number of columns must agree in both select statements.
The "from mytable" in the first select will return 1 row even if
the table has many, and is required.

Works Great Thanks
PS I did have to add another field so your advice came in handy!
DS

 

[DS]

OK all of the other listboxes work except this one which has a criteria.

SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
FROM tblItemTypes UNION SELECT 0,"All Types" FROM tblItemTypes
WHERE tblItemTypes.ItemTypeID Not Like 5
ORDER BY ItemTypeName;

it's not excluding the one record.

Thanks
DS

 

[John W. Vinson]

WHERE tblItemTypes.ItemTypeID Not Like 5

You don't need to use the LIKE operator here - it should be used only when
you're using wildcards on string fields. If you want to exclude typeID 5 just
use

WHERE tblItemTypes.ItemTypeID <> 5

John W. Vinson [MVP]

 

[fredg]

OK all of the other listboxes work except this one which has a criteria.

SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
FROM tblItemTypes UNION SELECT 0,"All Types" FROM tblItemTypes
WHERE tblItemTypes.ItemTypeID Not Like 5
ORDER BY ItemTypeName;

it's not excluding the one record.

Thanks
DS

What's the datatype of the ItemTypeID field? Is it a Number or Text?

If it is a Text datatype, you can use the Like keyword with a wildcard
to exclude any value in the field that starts with, contains anywhere
in the field, or ends with, that 5 value. Note, because it is Text
datatype, the value must be encloses within quotes "5".

WHERE tblItemTypes.ItemTypeID Not Like "5*"

will exclude records that begin with the 5, i.e. 5, 53, 569, etc.

If the value to exclude is just "5", then there is no need for a wild
card nor the Like keyword.

WHERE tblItemTypes.ItemTypeID Not "5"

If the datatype is a Number datatype, then do not use the Like keyword
nor the quotes..

WHERE tblItemTypes.ItemTypeID Not 5

Also the Where clause goes after the Select, not after the Union
Select...

I'll guess that the 5 is a number, not text.

SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
FROM tblItemTypes
WHERE tblItemTypes.ItemTypeID Not 5
UNION SELECT 0,"All Types" FROM tblItemTypes
ORDER BY ItemTypeName;

Note: if one of the values sorts before "All Types", for example,
"alabaster", your "All Types" will NOT be first in the list.
To sort first, you could use "<All Types" or " All Types", etc.

 

[DS]

You don't need to use the LIKE operator here - it should be used only when
you're using wildcards on string fields. If you want to exclude typeID 5 just
use

WHERE tblItemTypes.ItemTypeID <> 5

John W. Vinson [MVP]

Thanks John, I also needed to move the where clause to an earlier
position. It works now.
Thanks
DS

 

[DS]

What's the datatype of the ItemTypeID field? Is it a Number or Text?

If it is a Text datatype, you can use the Like keyword with a wildcard
to exclude any value in the field that starts with, contains anywhere
in the field, or ends with, that 5 value. Note, because it is Text
datatype, the value must be encloses within quotes "5".

WHERE tblItemTypes.ItemTypeID Not Like "5*"

will exclude records that begin with the 5, i.e. 5, 53, 569, etc.

If the value to exclude is just "5", then there is no need for a wild
card nor the Like keyword.

WHERE tblItemTypes.ItemTypeID Not "5"

If the datatype is a Number datatype, then do not use the Like keyword
nor the quotes..

WHERE tblItemTypes.ItemTypeID Not 5

Also the Where clause goes after the Select, not after the Union
Select...

I'll guess that the 5 is a number, not text.

SELECT tblItemTypes.ItemTypeID,tblItemTypes.ItemTypeName
FROM tblItemTypes
WHERE tblItemTypes.ItemTypeID Not 5
UNION SELECT 0,"All Types" FROM tblItemTypes
ORDER BY ItemTypeName;

Note: if one of the values sorts before "All Types", for example,
"alabaster", your "All Types" will NOT be first in the list.
To sort first, you could use "<All Types" or " All Types", etc.

Thanks Fred,
All of that was very informative. I appreciate it.
Thanks
DS