Page 1 of 1
[Solved] Combine fields in report
Posted: Mon Sep 10, 2012 3:44 pm
by rpriddle
Trying to create a compact report in Base. 100 records with field names "first" "last" "phone" etc. 5 fields required for a report.
The database stores "first" and "last" separately, but I'd like to have a field in the report that lists "<first> <last>". Is this possible? And if it is, how do I do it?
(Oh, and if this has been answered earlier, I apologize - I couldn't find it.)
TIA
Roger Priddle
Re: Combine fields in report
Posted: Tue Sep 11, 2012 2:12 pm
by Villeroy
Create a query with a concatenated field and use the query instead of the table.
SELECT "first" || ' ' || "last" AS "Full Name" , "other field" FROM "Some Table"
Re: Combine fields in report
Posted: Tue Sep 11, 2012 9:59 pm
by Villeroy
The double-pipe || is the SQL concatenation operator.
Single quotes denote literal strings. So ' ' means a space.
Double-quotes denote object names (tables, fields) and their alias names.
From the table named "Some Table" we select the field named "first", concatenate with a space, concatenate with the field named "last", name the resulting field as "Full Name", select some "other field" too.
SQL is the lingua franca of databases.
Re: Combine fields in report
Posted: Wed Sep 12, 2012 3:43 am
by rpriddle
Thanks for this - it wasn't covered when I learned Fortran in 1972 <grin>. SQL is all new to me - I've had no real reason to learn it before - but I will trust that you've solved my problem. Many thanks.
Re: [Solved] Combine fields in report
Posted: Mon Apr 20, 2015 12:44 am
by plotkinms
Try TRIM([first])&LEFT(" ";1)&TRIM([LAST])