Using column name in SQL Select statement

Discussion in 'Microsoft Access Queries' started by Mal, Jun 12, 2005.

  1. Mal

    Mal Guest

    Hi all.

    I've got a table linked to Excel where every three consecutive columns are for a
    specific calendar month, ie. F4, F5 & F6 are for July, F7 to F9 represent August
    etc etc. I'm setting this up for the next f/y and thought it'd be cute to be
    able to automatically pull stats out of it for both the current & last completed
    month by using the system date/month to select the appropriate columns.
    Something like;

    SELECT F&MONTH(NOW())*3, F&MONTH(NOW())*3+1.....

    Tragically, I'm as competent with Access SQL as is the average zuchini and can't
    get it to work. Is it even possible? And if 'Yes', could some nice person point
    me in the right direction.


    cheers


    Mal
     
    Mal, Jun 12, 2005
    #1
    1. Advertisements

  2. Mal wrote:
    >I've got a table linked to Excel where every three consecutive columns are for a
    >specific calendar month, ie. F4, F5 & F6 are for July, F7 to F9 represent August
    >etc etc. I'm setting this up for the next f/y and thought it'd be cute to be
    >able to automatically pull stats out of it for both the current & last completed
    >month by using the system date/month to select the appropriate columns.
    >Something like;
    >
    >SELECT F&MONTH(NOW())*3, F&MONTH(NOW())*3+1.....



    You will have to construct the SQL statement in VBA (using a
    string expression much like what you posted above).

    Dim strSQL As String
    strSQL = "SELECT F" & ((MONTH(Date) - 6) * 3 + 1) & _
    ", F" &" & ((MONTH(Date) - 6) * 3 + 1) & _
    & . . .


    You never said where/how you were going to use the SQL
    statement, so I'm just guessing now. To use that as a
    form/report's RecordSource, put the code in the
    form/report's Open event followed by the line:

    Me.RecordSource = strSQL

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Jun 12, 2005
    #2
    1. Advertisements

  3. Mal

    Mal Guest

    Marshall Barton wrote:
    > Mal wrote:
    >
    >>I've got a table linked to Excel where every three consecutive columns are for a
    >>specific calendar month, ie. F4, F5 & F6 are for July, F7 to F9 represent August
    >>etc etc. I'm setting this up for the next f/y and thought it'd be cute to be
    >>able to automatically pull stats out of it for both the current & last completed
    >>month by using the system date/month to select the appropriate columns.
    >>Something like;
    >>
    >>SELECT F&MONTH(NOW())*3, F&MONTH(NOW())*3+1.....

    >
    >
    >
    > You will have to construct the SQL statement in VBA (using a
    > string expression much like what you posted above).
    >
    > Dim strSQL As String
    > strSQL = "SELECT F" & ((MONTH(Date) - 6) * 3 + 1) & _
    > ", F" &" & ((MONTH(Date) - 6) * 3 + 1) & _
    > & . . .
    >
    >
    > You never said where/how you were going to use the SQL
    > statement, so I'm just guessing now.


    I'd planned to send the output straight to Excel to fill in a Pivot
    Table. I won't go into the details. It's ugly.

    > To use that as a
    > form/report's RecordSource, put the code in the
    > form/report's Open event followed by the line:
    >
    > Me.RecordSource = strSQL
    >


    Great. Thanks Marshall, I'm on my way.


    Mal
     
    Mal, Jun 13, 2005
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul James

    using the results of a SQL SELECT(COUNT) statement in VBA

    Paul James, Sep 23, 2004, in forum: Microsoft Access Queries
    Replies:
    7
    Views:
    2,524
    Paul James
    Sep 24, 2004
  2. Guest

    select count(distinct(column name)) from table name

    Guest, Feb 17, 2006, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    4,408
    John Vinson
    Feb 17, 2006
  3. PaulDenver

    IIf statement vs Select Case statement

    PaulDenver, Sep 4, 2006, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    274
    Steve Schapel
    Sep 4, 2006
  4. PaulDenver

    IIf statement vs Select Case statement

    PaulDenver, Sep 4, 2006, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    425
    Guest
    Sep 6, 2006
  5. Guest

    SQL: Select a column name "Short Name"

    Guest, Oct 29, 2007, in forum: Microsoft Access Queries
    Replies:
    6
    Views:
    1,035
    John Spencer
    Nov 8, 2007
Loading...

Share This Page