Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop through query fields with DAO. (2000)

    I'm trying to use some code I wrote a while ago but it doesn't seem to work. Now I'm wondering if it ever did.
    When it gets to the line 'For Each qfield In qdef.Fields' it drops out to the last 'End if'.

    If I modify it to work with tables it works fine.


    Sub InspectMyQueries()
    'cycle through the queries
    'retrieve those used to make tables for arcview - begin with 'p'
    'get any fields in those queries longer than 10 characters

    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim qfield As DAO.Field
    Dim strInfo As String

    Set db = CurrentDb

    For Each qdef In db.QueryDefs
    If Left(qdef.Name, 1) = "p" Then
    'Debug.Print qdef.Name

    For Each qfield In qdef.Fields

    If Len(qfield.Name) > 10 Then

    strInfo = qdef.Name & vbTab & qfield.Name
    Debug.Print strInfo

    End If

    Next

    End If

    Next qdef

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop through query fields with DAO. (2000)

    Your code works fine in my test database. Please note that it only returns information about fields whose name is longer than 10 characters in queries whose name starts with 'p'. Perhaps there are currently no such fields/queries in your database?

    Added later: one other thing to keep in mind is that the Fields collection for action queries (update queries, delete queries etc.) is empty.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through query fields with DAO. (2000)

    I've just read you 'added later' bit. The queries in question are indeed action queries (make table queries) so I guess that's why its not working <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    When I first used it I must have run it on select queries to check they were okay before I turned them into make table queries.

    Never mind, I guess I can just run the 'table' version.

    Can you explain why the fields collection is empty though.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop through query fields with DAO. (2000)

    The Fields collection represents the columns displayed in the datasheet view of the query. In a Select query, fields used only to set criteria and/or to specify the sort order (the Show check box is cleared) do not belong to the Fields collection. Action queries do not display columns at all, so their Fields collection is empty.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through query fields with DAO. (2000)

    Thats worth knowing, I'd have probably been caught out at some point with a select query otherwise.

    Thanks again, for making everything crystal clear as usual <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •