Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010

    Unanswered: Choose field in query based on date


    I have tried to find a post on here that will help me with this but i can't seem to find anything, I think due to the fact that I cannot use very specific wording to search on!

    Basically I have a table in Access which is linked from an Excel spreadsheet and it has several dates as field headings (e.g. 20 May 2010, 21 May 2010, ...) And I need to pull into a query some of the other headings and then JUST the field that is today's date.

    I thought I could either set something up that uses Date() or something that asks for the current date when you open the query, but I can't figure out how to set either of those up?

    If anyone knows how I could do this, I would appreciate the help.



  2. #2
    Join Date
    Jan 2002
    Bay Area
    I have one solution that may be of help to you. Date headings from the linked spreadsheet are displayed in a list box, and a query is created based on the date, which is a field name, selected from the list box.
    It may be that my solution does not work in a shared database environment, and I'll leave that for others to advise you if that is an issue.
    Attached is the database with a form to build and run a query, and an Excel workbook that I linked and tested in "c:\Temp". Date headings in the Excel worksheet must be text, so I typed the single quote character in front of each date heading. Example '21 May 2010
    Good luck.
    Function Pop_ListBox()
    Dim db As Database
    Dim rs As Recordset
    Dim fld As Field
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Qty", dbOpenSnapshot)
        For Each fld In rs.Fields
            If InStr(fld.Name, "20") > 0 Then
                Forms.Form1.lstMyListBox.AddItem Item:=fld.Name
            End If
        Next fld
        Set rs = Nothing
        Set db = Nothing
    End Function
    Function Do_Query(qryField As String)
    Dim db As Database
    Dim qdf As QueryDef, qryName As String, sqlText As String
        qryName = "ProdQtyRcd"
        Set db = CurrentDb
        For Each qdf In db.QueryDefs     'delete the query to be created, if found
            Select Case qdf.Name
                Case qryName
                    DoCmd.DeleteObject acQuery, qdf.Name
                Case Else
                    'do nothing
            End Select
        Next qdf
        sqlText = "SELECT Qty.[Product Code], " & _
        "Qty.[" & qryField & "] " & _
        "FROM Qty " & _
        "WHERE (((Qty.[" & qryField & "]) Is Not Null))" & _
        "ORDER BY Qty.[Product Code]; "
        Set qdf = db.CreateQueryDef(qryName, sqlText)
        DoCmd.OpenQuery qryName
        Set qdf = Nothing
        Set db = Nothing
    End Function
    Attached Files Attached Files
    Last edited by JerryDal; 05-27-10 at 17:05. Reason: unnecessary line of code

  3. #3
    Join Date
    May 2010
    Hi, thanks, but I was hoping for a much simpler solution like does any one know if there is a method of choosing fields in a query based on their position in the table, e.g. choose field 1, 2, 3 & 4?

Tags for this Thread

Posting Permissions

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