can queries output be formatted as memo fields?

[Dave F]

I have a query, GetSQL which returns the SQL statements of all queries in my
db, for documentation purposes. Its SQL is:

SELECT msysObjects.Name AS QueryName, GetSQL([NAME]) AS SQLStatement,
msysObjects.DateCreate, msysObjects.DateUpdate
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5));

GetSQL is a UDF with the following code:

Option Compare Database

Function GetSQL(pstrQuery As String) As String
GetSQL = CurrentDb.QueryDefs(pstrQuery).SQL
End Function

When I run this query, long SQL statements get truncated. I assume that
this is because Access is using a text field by default. Is there a way to
get the SQL to be returned as a memo field, which I understand allows more
text characters than a text field?

Hope this is clear, thanks.

Dave

 

[Bill Mosca, MS Access MVP]

How are you displaying the results? If it is a report, set the text box
propertY, CanGrown to Yes.

 

[Dave F]

I'm just running the query. It's not tied to a report.
--
Brevity is the soul of wit.

How are you displaying the results? If it is a report, set the text box
propertY, CanGrown to Yes.

--
Bill Mosca, MS Access MVP

I have a query, GetSQL which returns the SQL statements of all queries in
my
db, for documentation purposes. Its SQL is:

SELECT msysObjects.Name AS QueryName, GetSQL([NAME]) AS SQLStatement,
msysObjects.DateCreate, msysObjects.DateUpdate
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5));

GetSQL is a UDF with the following code:

Option Compare Database

Function GetSQL(pstrQuery As String) As String
GetSQL = CurrentDb.QueryDefs(pstrQuery).SQL
End Function

When I run this query, long SQL statements get truncated. I assume that
this is because Access is using a text field by default. Is there a way
to
get the SQL to be returned as a memo field, which I understand allows more
text characters than a text field?

Hope this is clear, thanks.

Dave

 

[Ken Sheridan]

Dave:

It might be a manifestation of a known bug which appears to truncate text,
but doesn't actually do so. It normally associated with memo fields when
importing data from non-Access tables or when adding bound controls to a form
or report by dragging from the field list or by using the form or report
wizard. The effects are as you describe.

Try binding a form to the table and adding a text box from the tool box,
then setting its ControlSource to the SQLStatement field. If this is the
known bug you should get the full text in the control.

You can of course get the SQL of all queries by using the built in
Documenter. You can also get a lot more, provided you have shares in a paper
mill!

Ken Sheridan
Stafford, England

 

[Bill Mosca, MS Access MVP]

If it is just a query, check to see if all the characters are there by
selecting the record's field and pressing Shift+F2. If it's all there, all
you have to do is click on the row box on the left of the datasheet grid and
drag it down to show multiple lines in each row.

--
Bill Mosca, MS Access MVP

I'm just running the query. It's not tied to a report.
--
Brevity is the soul of wit.

How are you displaying the results? If it is a report, set the text box
propertY, CanGrown to Yes.

--
Bill Mosca, MS Access MVP

I have a query, GetSQL which returns the SQL statements of all queries
in
my
db, for documentation purposes. Its SQL is:

SELECT msysObjects.Name AS QueryName, GetSQL([NAME]) AS SQLStatement,
msysObjects.DateCreate, msysObjects.DateUpdate
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5));

GetSQL is a UDF with the following code:

Option Compare Database

Function GetSQL(pstrQuery As String) As String
GetSQL = CurrentDb.QueryDefs(pstrQuery).SQL
End Function

When I run this query, long SQL statements get truncated. I assume
that
this is because Access is using a text field by default. Is there a
way
to
get the SQL to be returned as a memo field, which I understand allows
more
text characters than a text field?

Hope this is clear, thanks.

Dave

 

[aaron.kempf]

Access MDB is for babies.

If you used SQL Server then you could use sp_helptext in order to do
the same thing.
Imagine that; doing everything in SQL instead of half in
Access-specific SQL and the other half in DAO.. lol

-Aaron

Dave:

It might be a manifestation of a known bug which appears to truncate text,
but doesn't actually do so. It normally associated with memo fields when
importing data from non-Access tables or when adding bound controls to a form
or report by dragging from the field list or by using the form or report
wizard. The effects are as you describe.

Try binding a form to the table and adding a text box from the tool box,
then setting its ControlSource to the SQLStatement field. If this is the
known bug you should get the full text in the control.

You can of course get the SQL of all queries by using the built in
Documenter. You can also get a lot more, provided you have shares in a paper
mill!

Ken Sheridan
Stafford, England

I have a query, GetSQL which returns the SQL statements of all queries in my
db, for documentation purposes. Its SQL is:

SELECT msysObjects.Name AS QueryName, GetSQL([NAME]) AS SQLStatement,
msysObjects.DateCreate, msysObjects.DateUpdate
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5));

GetSQL is a UDF with the following code:

Option Compare Database

Function GetSQL(pstrQuery As String) As String
GetSQL = CurrentDb.QueryDefs(pstrQuery).SQL
End Function

When I run this query, long SQL statements get truncated. I assume that
this is because Access is using a text field by default. Is there a way to
get the SQL to be returned as a memo field, which I understand allows more
text characters than a text field?

Hope this is clear, thanks.

Dave