Listbox Rowsource

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

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
Click to expand...
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.
 
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
Click to expand...


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.
 
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
 
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
Click to expand...
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;
 
fredg said:
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
Click to expand...



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.
Click to expand...
Works great!
Thanks
DS
 
Bob said:
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.
Click to expand...
Works Great Thanks
PS I did have to add another field so your advice came in handy!
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
 
WHERE tblItemTypes.ItemTypeID Not Like 5
Click to expand...

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]
 
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
Click to expand...

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.
 
John said:
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]
Click to expand...
Thanks John, I also needed to move the where clause to an earlier
position. It works now.
Thanks
DS
 
fredg said:
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.
Click to expand...
Thanks Fred,
All of that was very informative. I appreciate it.
Thanks
DS
 
Back
Top