Dynamic Set Query Feild Properties

[Guest]

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

 

[Guest]

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.

 

[Guest]

How is it possible (the cumbersome and limited way)?

 

[Guest]

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.