Combine text to form a field name to use in a query

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?

Answer 1

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

Answer 2

Thanks Miriam. Your solution has worked.