CODEWITHSUNDEEP
ms-access
akc42
akc42

Reputation: 5001

How do I change column widths in a Query embedded within a report

In my Access application I have a report which contains a sub-report which is actually just a query.

The query in question is just a pass-through query which just executes a pre-prepared stored procedure. The query is created dynamically so that I can pass some date parameters from the form which I use to request the report (clicking a button).

When I return the results in SQL Server Management Studio, since the column names are quite narrow and the data is integers in the range 0 to 100, the 8 or 9 columns in this query are quite narrow.

In MS Access, when the query is embedded within a report Access seems to set a column width which means all the columns will not fit on the page, even through the paper is landscape.

Is there anyway I can do something to tell Access to make the column widths narrower?

Upvotes: 0

Views: 3377

Answers (2)

akc42
akc42

Reputation: 5001

The solution that I found which seems the best is to create a form using the form wizard which has the query as its datasource and which displays itself in datasheet view.

When you embed that within the report, the field widths of the datafields in the form can be adjusted to suit in layout mode.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91366

You can set the column widths of query like so:

Sub SetColumnWidth()
Dim qdf1 As DAO.QueryDef
Dim fld1 As DAO.Field

Set qdf1 = CurrentDb.QueryDefs("query3")

For i = 0 To qdf1.Fields.Count - 1
    Set fld1 = qdf1.Fields(i)
    fld1.CreateProperty "ColumnWidth", dbInteger
    'very narrow indeed
    fld1.Properties("ColumnWidth") = 200
    Set fld1 = Nothing
Next i

End Sub

Upvotes: 1

Related Questions