bowtellj
Posts: 65
|
| Posted: 08/14/2007, 4:20 PM |
|
I am trying to understand the query builder and am having no luck joining FirstName and LastName. Normally I would use firstname + ' ' + lastname AS fullname
How do I join these in the query builder.
_________________
Thanks
James
Just another Newbie! - Apache 2.24, PHP 5.2.3, MySQL 5.0.41, Windows XP SP2. CCS 3.2.0.2/4.0.2
|
 |
 |
wkempees
|
| Posted: 08/14/2007, 5:09 PM |
|
Search the forum (using "Search"in top menu) for "concatenate"
several topics will turn up.
Important to know your DB type, I suspect it is MsSql (as you use the +)
In the field part of the VQB (lower right pane)
left part you can construct your concatenated field list,
right part you define an 'alias' for this concatenatedfield
the alias is what you will be using throughout design
For instance:
originaly started out with a grid through builder having Name
Open VQB
type Firstname+' '+Lastname in field.
type Name in alias, this way you will keep the generated grid fully
functional.
Hope you get my drift, late night typing.
Walter
"bowtellj" <bowtellj@forum.codecharge> schreef in bericht
news:246c238d0e84f6@news.codecharge.com...
>I am trying to understand the query builder and am having no luck joining
> FirstName and LastName. Normally I would use firstname
> + ' '
> + lastname AS fullname
>
> How do I join these in the query builder.
>
> _________________
> Thanks
>
> James
>
> Just another Newbie! - Apache 2.24, PHP 5.2.3, MySQL 5.0.41, Windows XP
> SP2.
> CCS 3.2.0.2
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 08/14/2007, 6:42 PM |
|
bowtellj
The MySQL function is CONCAT(str1,str2,...) . I would also recommend that you download the MySQL CHM. It is very helpful in situations like this one.
|
|
|
|
bowtellj
Posts: 65
|
| Posted: 08/14/2007, 9:15 PM |
|
Thanks, concat(firstname,' ',lastname) worked a treat.
wkempees, started learning mssql but has a change of heart and moved towards the light. 
_________________
Thanks
James
Just another Newbie! - Apache 2.24, PHP 5.2.3, MySQL 5.0.41, Windows XP SP2. CCS 3.2.0.2/4.0.2
|
|
|
|
wkempees
|
| Posted: 08/15/2007, 3:41 AM |
|
'The Light' as in illumination I suppose not as in weight or capabilities!
"bowtellj" <bowtellj@forum.codecharge> schreef in bericht
news:246c27dd3535ef@news.codecharge.com...
> Thanks, concat(firstname,' ',lastname) worked a treat.
>
> wkempees, started learning mssql but has a change of heart and moved
> towards
> the light.
> _________________
> Thanks
>
> James
>
> Just another Newbie! - Apache 2.24, PHP 5.2.3, MySQL 5.0.41, Windows XP
> SP2.
> CCS 3.2.0.2
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
jerrym
Posts: 52
|
| Posted: 08/17/2007, 2:35 AM |
|
walter
i am having trouble concatenating 2 columns from a table for a listbox. i read 1 of your post helping someone with a similar problem. here is mine:
datasource type: table/view
datasource: Equipment
bound column: EquipID
text column: Expr1
datatype: integer
now in VQB in lower right pane for the Select statement;
EquipID
EquipID + " - " + EquipmentNumber alias Expr1
i get an error below;
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/CASE/Classes.asp, line 3338
would you assist please - DB is MS SQL
many thanks in advance.
|
|
|
|
wkempees
|
| Posted: 08/17/2007, 4:49 AM |
|
two tips to start off:
1: use single quotes not double quotes, MsSQL uses single quotes
2: you are concatenating mixed types, could work but could also be the
problem.
I am not using MsSQL so no hands-on advice here.
In VQB you have a button that will show you the SQL, press that copy and
paste the SQL
to feed it to your SQL editor, there even is a view result button.
Experiment.
Walter
"jerrym" <jerrym@forum.codecharge> schreef in bericht
news:246c56bd9064ea@news.codecharge.com...
> walter
>
> i am having trouble concatenating 2 columns from a table for a listbox. i
> read
> 1 of your post helping someone with a similar problem. here is mine:
>
> datasource type: table/view
> datasource: Equipment
> bound column: EquipID
> text column: Expr1
> datatype: integer
>
> now in VQB in lower right pane for the Select statement;
> EquipID
> EquipID + " - " + EquipmentNumber alias Expr1
>
> i get an error below;
>
> Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
> /CASE/Classes.asp, line 3338
>
> would you assist please - DB is MS SQL
> many thanks in advance.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/17/2007, 4:52 AM |
|
two tips to start off:
1: use single quotes not double quotes, MsSQL uses single quotes
2: you are concatenating mixed types, could work but could also be the
problem.
I am not using MsSQL so no hands-on advice here.
In VQB you have a button that will show you the SQL, press that copy and
paste the SQL
to feed it to your SQL editor, there even is a view result button.
Experiment
Desired result:
SELECT EquipID, EquipID + ' - ' + EquipmentNumber AS Expr1
FROM Equipment
Walter
(sorry doubly posted)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
|
