Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    21

    Exclamation Unanswered: HELP!! Field in query based on criteria...

    I've been searching for an answer and I really need help. Here's the deal:

    I have a table, ProjectPlans, containing spending plans for each month:

    |Project|Activity|1|2|3|....|12|

    So if the user chooses the week-ending date of 12/16/04 on a form, I need a query to pull the current month (based on that date-- 12), and use it as criteria for the FIELD of the spending plan. I know how to get the month number:

    month([forms]![weeklyreport]![combo40])

    but I can't seem to figure out a way to use this data as the field name in the ProjectPlans table.

    Any help??? I'm under a big deadline and appreciate ANY input!!!

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    it sounds as though you need to check your table design,
    but there is a soloution this will create a new query for each time you run
    Note *this is a dao soloution*

    Code:
    Sub BuildQuery()
        Dim strSQL As String
        Dim db As DAO.Database
        
        Set db = CurrentDb
    
        On Error Resume Next
        db.QueryDefs.Delete "qryTemp"
        On Error GoTo 0
        
        strSQL = "SELECT Project, Activity, " & Month([Forms]![weeklyreport]![combo40]) & _
            " FROM tblProject;"
        
        db.CreateQueryDef "qryTemp", strSQL
        
    End Sub
    Dave

  3. #3
    Join Date
    Jul 2004
    Posts
    21
    Thanks-- it sounds like what I'm looking for. I'm not sure where to put the code, however. Create a new module? As you can probably tell, I'm not very experienced in VBA, and could use a little guidance.

    Thanks again!!!

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Creste a new module and put the code there and then you can call this module from one of your buttons etc.
    make sure to add the DAO Reference

  5. #5
    Join Date
    Jul 2004
    Posts
    21
    Thanks-- I really appreciate your help!

Posting Permissions

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