List field names in a query

T

Tony Wainwright

Hi guys

I have a query that I am using to create a list of customers details that I
would like to display on a form without using CBF. I would like to write a
select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
 
R

Ron Weiner

Tony

I am not even sure what you want to do is possible. For instance how could
Access tell what field names would be returned from a crosstab query? Here
is a can't fail way of doing what you want using a recordset and iterating
through its fields collection.

Public Sub GetFieldNames(strQueryName)
' Get names of all Columns a query will return
Dim rs As ADODB.Recordset, fld As ADODB.Field

Set rs = New ADODB.Recordset
rs.Open strQueryName, CurrentProject.Connection
For Each fld In rs.Fields
Debug.Print fld.Name
Next
rs.Close
Set rs = Nothing
End Sub


Ron W
 
G

Graham R Seach

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
J

JohnFol

I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));
 
T

Tony Wainwright

Thanks John

JohnFol said:
I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
J

Jeff Conrad

Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
J

Jeff Conrad

Just checking.
:)

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Picky, picky, picky! :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
G

Graham R Seach

Picky, picky, picky! :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the
field name from the MsysQueries table, I just can't find a link between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
T

The Goulds

Wow - so lessee, the query forms a list based on an existing query?

Graham R Seach said:
Picky, picky, picky! :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Graham R Seach said:
Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
T

The Goulds

ahh...Code Behind Forms.

The Goulds said:
what is CBF?

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
T

The Goulds

ummm...my version of these files did not contain all the tables w/in my
database
JohnFol said:
I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
G

Graham R Seach

<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

The Goulds said:
Wow - so lessee, the query forms a list based on an existing query?

Graham R Seach said:
Picky, picky, picky! :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jeff Conrad said:
Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
G

Graham R Seach

Unfortunately John, that query may return inconsistent values, because
sometimes MSysQueries.Expression contains a criteria expression.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

JohnFol said:
I think you want this

SELECT MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name)="qryCustDetails") AND
((MSysQueries.Expression)<>""));

Tony Wainwright said:
Hi guys

I have a query that I am using to create a list of customers details that
I would like to display on a form without using CBF. I would like to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table and
the field name from the MsysQueries table, I just can't find a link
between them.

Any help appreciated

Tony
Click to expand...
Click to expand...
 
T

The Goulds

Thanks Graham - I suspect the code can be modified to pull from tables as
opposed to queries?

Mike

Graham R Seach said:
<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

The Goulds said:
Wow - so lessee, the query forms a list based on an existing query?

Graham R Seach said:
Picky, picky, picky! :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers details
that
I would like to display on a form without using CBF. I would like
to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Wainwright

Please show me how - that's what I'm looking for
The Goulds said:
Thanks Graham - I suspect the code can be modified to pull from tables as
opposed to queries?

Mike

Graham R Seach said:
<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

The Goulds said:
Wow - so lessee, the query forms a list based on an existing query?

Picky, picky, picky! :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers
details that
I would like to display on a form without using CBF. I would like
to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 
G

Graham R Seach

Tony,

Just change "QueryDef" and "QueryDefs", to "TableDef" and "TableDefs",
respectively.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Tony Wainwright said:
Please show me how - that's what I'm looking for
The Goulds said:
Thanks Graham - I suspect the code can be modified to pull from tables as
opposed to queries?

Mike

Graham R Seach said:
<<...the query forms a list based on an existing query?>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Wow - so lessee, the query forms a list based on an existing query?

Picky, picky, picky! :)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham,

To be used in other places shouldn't this line of code:

sSQL = "SELECT " & sSQL & " FROM qryTable1"

be.....

sSQL = "SELECT " & sSQL & " FROM " & sQueryName

or was your code to provide the exact syntax for just the
query the OP mentioned?

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tony,

Funny, I wrote this just today.

Public Function GetFieldNames(sQueryName As String) As String
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sSQL As String

Set qdf = CurrentDb.QueryDefs(sQueryName)
For Each fld In qdf.Fields
'SELECT [AllFieldNames] FROM qryCustDetails;
sSQL = sSQL & "[" & fld.Name & "], "
Next fld

sSQL = Left(sSQL, Len(sSQL) - 2)
sSQL = "SELECT " & sSQL & " FROM qryCustDetails"
GetFieldNames = sSQL

Set fld = Nothing
Set qdf = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi guys

I have a query that I am using to create a list of customers
details that
I would like to display on a form without using CBF. I would like
to
write a select query that list the field names as in:

SELECT [AllFieldNames] FROM qryCustDetails;

I know that you can get the objets name from the MsysObjects table
and the
field name from the MsysQueries table, I just can't find a link
between
them.

Any help appreciated

Tony
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query field names changing to expr#: 8
Query field names 2
Using a field name as variable in a query 1
Including Table Name in Query Field Names 1
Old form references in MSysQueries and MSysObjects 1
query to retrieve list of field names belonging to an access table 3
dynamic calculated field in query 3
Renaming field names of Linked Table in query 8

Top