andy
Posts: 183
|
| Posted: 07/25/2007, 7:41 AM |
|
If you would like to display 2 fields with a separator or space between them in a listbox
such as:
Field1 :: Field2
Then this is how you do it:
In the Data properties for the listbox, select SQL under Data Source Type.
In Data Source, click on the ... to enter the query builder.
You will want to select 2 fields. One for the bound column (data stored at database level) and one for the Text column (data displayed on the form).
The Text column is a concatanation of 2 fields with a separator in the middle.
Here is an example:
SELECT Name, concat(AccountType," :: ",Name) 2cols
FROM account
WHERE AccountType="Cost of Goods Sold"
ORDER BY ID
Name and AccountType are fields in the table "account"
Name is selected and the alias (made up name) "2cols" is selected.
2 cols displays as AccountType :: Name
You can include a Where statement if you want to limit the records displayed.
You can include an ORDER BY field to change the sort order
Then - to continue this example - in the Data properties for the listbox,
select Name under Bound column
select 2cols under Text column
You can of course change the separator by putting a space or any other characters between double quotes in the sql statement
Ta da
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
 |
 |
Wkempees
|
| Posted: 07/25/2007, 9:42 AM |
|
And if you like to do the same on a Listbox that is already functioning:
Open the Listbox's Datasource [..]
click SELECT, look al lower right pane of VQB
where it says (using same example)
on one row Name
and on the next AccountType
Change AccountType to
concat(AccountType, " :: ", Name)
and in the next column (Alias) type AccountType
This will
a/ do the same as Andy's example
b/ allow for maximum result at minimum effort
c/ leave the DataSource type: Table and not change it to SQL
For MySQL users, you can also use
concat_ws(<separator>,<fieldname>[,<fieldname>])
which rewrites above example as concat_ws(" :: ", AccountType, Name)
Just thought I'd mention it.
I always start out with the simple version of a listbox and using above
described expand it.
Walter
|
|
|
|
|
