Adding/combining fields through a query

[woodyinoz]

Hi all,

I'm sure this is a simple problem.

Within a query I am trying to add 3 integer fields together to create an id but I don't actually want to sum the 3.
For example, if my 3 fields read -

803+403+01

I don't want the answer to be 1206. I want the answer to read 80340301.

Similarly If the 3rd field were to be replaced by an alpha field, therefore reading soemthing like -

803+402+M03

does anyone know how this can be done within a query to read 803402-M03?

Thanks all,

Woody.

 

[JoeNg]

I believe this can only be done in query if the data types are Alphas.

Else, you can create a dummy field, populate it by scanning the table and convert the numeric value to alpha and combine them together

 

[footpad]

Woody,

You can't do this with a QBE query, but I believe you can do it with a SQL query that uses the CAST function to convert the integers to strings. See the LOCALSQL.HLP file in C:\Program Files\Common Files\Borland Shared\BDE.

It may be faster, though, to use a script:

var 
   tc TCursor
endVar

   tc.open()
   tc.edit()
   scan tc :
      tc."TargetField" = string( tc."Field1" ) + 
                         string( tc."Field2" ) + 
                         string( tc."Field3" )
      tc.unlockRecord()
   endScan
   tc.endEdit()
   tc.close()

Notes:

1. If you want to have leading zeros used in the number to string conversion, use format() instead of string(). See Help for details, especially the topic called "Format Specifiers"

2. If you're changing the table's primary key, use the SQL query, not the script shown above. (You can use the script in this case, however, you'll need to call switchIndex() to change the active index before starting the sacn loop. Remember: Never change any fields in the active index during a SCAN loop; otherwise, bad things will happen.

Hope this helps...

-- Lance