If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamic Set Query Feild Properties
I designed a query with over 60 columns I need too enter information in each
columns for the 'Description Property' is there a way to add info into query properties dynamically (may by in the sql or???) Thanks Aron |
#2
|
|||
|
|||
Hi, Aron.
is there a way to add info into query properties dynamically (may by in the sql or???) It's possible to add comments into the SQL, but it's rather cumbersome and rather limited when compared to this capability in other DBMS's. It's better to add a description for each field, which will be displayed in the Status Bar whenever the query field or control bound to the field has the focus. I'm not sure whether you would like to programmatically assign the Description Property or do it manually, but here is the code for manually assigning this property to each field in the query: Public Sub addDescToQueryFlds() On Error GoTo ErrHandler Dim qry As QueryDef Dim fld As DAO.Field Dim prp As DAO.Property Dim sDesc As String Dim idx As Long Set qry = CurrentDb().QueryDefs("qryMyQuery") For idx = 0 To (qry.Fields.Count - 1) Set fld = qry.Fields(idx) sDesc = InputBox("Description for " & fld.Name, "Field Descripton") fld.Properties("Description") = sDesc Next idx CleanUp: Set prp = Nothing Set fld = Nothing Set qry = Nothing Exit Sub ErrHandler: If (Err.Number = 3270) Then Err.Clear Set prp = fld.CreateProperty("Description", dbText, sDesc) fld.Properties.Append prp Resume Next Else MsgBox "Error in addDescToQueryFlds( )." & _ vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description End If Err.Clear GoTo CleanUp End Sub HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "Aron" wrote: I designed a query with over 60 columns I need too enter information in each columns for the 'Description Property' is there a way to add info into query properties dynamically (may by in the sql or???) Thanks Aron |
#3
|
|||
|
|||
How is it possible (the cumbersome and limited way)?
"'69 Camaro" wrote: Hi, Aron. It's possible to add comments into the SQL, but it's rather cumbersome and rather limited when compared to this capability in other DBMS's. |
#4
|
|||
|
|||
Hi, Aron.
How is it possible (the cumbersome and limited way)? Create a calculated field (AKA alias) to hold the comment: SELECT *, "Sales in 1st Half" AS Comment FROM tblSales WHERE (SalesMonth = 6) UNION SELECT *, "Sales in 2nd Half" AS Comment FROM tblSales WHERE (SalesMonth 6) Whatever the comment is, it must be valid SQL syntax. The comment will be repeated for every record. As I wrote, it's cumbersome and limited. Not very useful when compared to other DBMS's comment syntax. For example, here's Oracle SQL with comments: SELECT last_name, -- select the name salary + NVL(commission_pct, 0),-- total compensation job_id, -- job e.department_id -- and department FROM employees e, -- of all employees departments d WHERE e.department_id = d.department_id AND salary + NVL(commission_pct, 0) -- whose compensation -- is greater than (SELECT salary + NVL(commission_pct,0) -- the compensation FROM employees WHERE last_name = 'Pataballa') -- of Pataballa. ; Oracle can use the /* Comment */ syntax, too. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "Aron" wrote: How is it possible (the cumbersome and limited way)? "'69 Camaro" wrote: Hi, Aron. It's possible to add comments into the SQL, but it's rather cumbersome and rather limited when compared to this capability in other DBMS's. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Dynamic crosstab query report | Richardson | Setting Up & Running Reports | 1 | August 31st, 2004 10:13 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |