INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
- Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here
|
Tips -N- Tricks
|
How to pre-format certain field in result of query (either for views or SELECT statement) by TomasDill
Posted: 5 Sep 00
|
When you need to create SQL with secial numeric field size (for example, UDF or just expression field), add constant value like '000.000' to the expression field in query.
Following are samples of such query:
SELECT *, sum(nMyNumber) + 000000.0000 as MySum from ....
MySum field in result will always have N(11,4) despite format of nMyNumber field.
SELECT *, MyFunction() + 000000.00 as MyField from ...
MyField will always have N(9,2) despite format of number returned by function.
Same rules work also for character fields. 'PADR' function is useful here. Unfortunately, no way to convert character fields and expressions into memo for query by such simple way, so all character expressions are limited to 255 characters unless array used as result of query. You may do this, however, using UNION in SELECT-SQL by specifying first SQL statement to query empty cursor that contains pre-formatted fields.
It DOES NOT works for currency or other field types. Use converting functions for other types. It DOES NOT works to cut format of numeric fields. Use this only to enhance format or fix it to the similar length for all records.
Did not investigated speed penalties of this method. (Don't know whether '0' really added to result values taking a time, or just ignored.) For character fields speed reduced because PADR fnction called for each result row.
|
Back to Microsoft: FoxPro FAQ Index
Back to Microsoft: FoxPro Forum
|
|
|
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
