dblayout
|
| Posted: 07/04/2003, 11:39 AM |
|
I would like to be able to create a new field in my sql generate cursor. I have a field called ZIP that contains the zip+4 data. I would like to be able to do something like "left(zip, 5) as zip2" to create a new field (only in the result cursor) called zip2 that contains just the first 5 characters of the zip field. Is there anyway to do this in the SQL in CCS?
Thanks.
|
|
|
 |
rrodgers
|
| Posted: 07/04/2003, 7:48 PM |
|
You don't say what db you are using.
For MSSQL2k I do...
Select Zip, Substring(Zip,1,5) as ZipPlus4 From Zip Where blah blah blah.
You will have to set your datasource to use sql.
|
|
|
|
dblayout
|
| Posted: 07/04/2003, 8:24 PM |
|
I am using Access. But I need to know how to modify the Select from within CCS. I know the SQL syntax and how to do it if I were just keying in the SQL. I need to know how to do this in CCS. What CCS screen do I use to add this? I have my 'Where' clauses defined but I can add user defined fields there - at least I don't think so.
Thanks
Chris
|
|
|
|
rrodgers
|
| Posted: 07/05/2003, 8:22 AM |
|
>>What CCS screen do I use to add this?
You need to go to the datasource properties for the control.
Change the DataSource Type to SQL. Then you can change your sql statment to anything you want.
rob
|
|
|
|
dblayout
|
| Posted: 07/05/2003, 9:37 AM |
|
Thanks for the tip. I made that change but now nothing is ever returned from my search. I even simplified my SQL so I know there would be some data (I also tested it using the 'Review Data' option in the Build SQL window). Here is my sql statement:
SELECT distinct name, address, address2, city, state, zip, left(zip, 5) as zip2, [value], type, bldgsqft, deeddate, built
FROM dallas
ORDER BY name
What am I missing to get the data to return? Or is my SQL even being called? I have used this SQL technique to get data for a listbox & that seemed to work fine. This SQL is in my Grid datasource. Is that the correct place? Mt 'Record' in the Search section of my form still shows Table but the connection & data source are blank. Thanks for any help you can give.
Thanks
Chris
|
|
|
|
|
