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

--
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


"Dave F" <(E-Mail Removed)> wrote in message
news:B4EF8289-39EE-4D37-93B9-(E-Mail Removed)...
>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
>
> --
> Brevity is the soul of wit.

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


"Bill Mosca, MS Access MVP" wrote:

> How are you displaying the results? If it is a report, set the text box
> propertY, CanGrown to Yes.
>
> --
> Bill Mosca, MS Access MVP
>
>
> "Dave F" <(E-Mail Removed)> wrote in message
> news:B4EF8289-39EE-4D37-93B9-(E-Mail Removed)...
> >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
> >
> > --
> > Brevity is the soul of wit.
>
>
>

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

"Dave F" wrote:

> 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
>
> --
> Brevity is the soul of wit.

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


"Dave F" <(E-Mail Removed)> wrote in message
news:E5E740A3-655E-4292-BAA6-(E-Mail Removed)...
> I'm just running the query. It's not tied to a report.
> --
> Brevity is the soul of wit.
>
>
> "Bill Mosca, MS Access MVP" wrote:
>
>> How are you displaying the results? If it is a report, set the text box
>> propertY, CanGrown to Yes.
>>
>> --
>> Bill Mosca, MS Access MVP
>>
>>
>> "Dave F" <(E-Mail Removed)> wrote in message
>> news:B4EF8289-39EE-4D37-93B9-(E-Mail Removed)...
>> >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
>> >
>> > --
>> > Brevity is the soul of wit.
>>
>>
>>

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


Ken Sheridan wrote:
> 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
>
> "Dave F" wrote:
>
> > 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
> >
> > --
> > Brevity is the soul of wit.