none
Combine text to form a field name to use in a query RRS feed

  • Question

  • I have a table GL_ACCOUNT_BLALANCES with fields

    YEAR

    GL_BALANCE_JAN

    GL_BALANCE_FEB

    How can I create a parameter query to get the month from the user and concatenate "GL_BALANCE_"&[MONTH]

    to get the result based on the user input?

    Thursday, February 4, 2016 9:54 AM

Answers

  • You cant do that with straight SQL... but look into the Switch function:

    Switch function

    You can do something like this (pretty cumbersome):

    YourResult: Switch([Month] = "Jan", GL_BALANCE_JAN, [Month] = "Feb",GL_BALANCE_FEB ...)

    Or in VBA on a form with a textbox for the month...

    strSQL = "SELECT GL_BALANCE_" & Me.txtMonth & " FROM YourTable etc..."
    
    SomeForm.Recordsource = strSQL

    That said... if your table were structured differently, with a SINGLE column for GL_BALANCE and a BalanceDate column, your SQL would simply be something like this:

    SELECT GL_BALANCE FROM MyTable WHERE Month(BalanceDate) = [Enter Month (1-12)]


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, February 4, 2016 10:25 AM
    • Marked as answer by Koshyt Thursday, February 4, 2016 11:59 AM
    Thursday, February 4, 2016 10:24 AM

All replies

  • You cant do that with straight SQL... but look into the Switch function:

    Switch function

    You can do something like this (pretty cumbersome):

    YourResult: Switch([Month] = "Jan", GL_BALANCE_JAN, [Month] = "Feb",GL_BALANCE_FEB ...)

    Or in VBA on a form with a textbox for the month...

    strSQL = "SELECT GL_BALANCE_" & Me.txtMonth & " FROM YourTable etc..."
    
    SomeForm.Recordsource = strSQL

    That said... if your table were structured differently, with a SINGLE column for GL_BALANCE and a BalanceDate column, your SQL would simply be something like this:

    SELECT GL_BALANCE FROM MyTable WHERE Month(BalanceDate) = [Enter Month (1-12)]


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, February 4, 2016 10:25 AM
    • Marked as answer by Koshyt Thursday, February 4, 2016 11:59 AM
    Thursday, February 4, 2016 10:24 AM
  • Thanks Miriam. Your solution has worked.

    Thursday, February 4, 2016 11:59 AM