Hi,

I am new to VBA, so I apologize if this is too easy of a question. What I am trying to do is this.... I want var3 to return the expression "are really good" instead of returning the expression "Meatballs". How do you do this in VBA?

Is this a trick question?

OK. I'll bite...

var3 = Meatballs

no, its not. i'm simplifying the situation. let me give you the real variables i am using, maybe this will help.

PYOY-1CurrMonth
PYOY-2CurrMnth
YOYLastMnth
YOYLastQtr
YOYCurrQtr = "[qtr]=2 and [fy] = 2009" (based on d look up to tblfsclcal)
YOYLastWk
PYOY-3LastMnth
etc,,,

I have all these variables defined as a Dlookup on a table I created called tblFiscalCalendar. This table defines the company's week #, qtr #, and month# for every day.

Based on user input, I am setting the three temp variables: var1, var2, and var3.

var1 = yoy or pyoy, pyoy-1, pyoy-2, pyoy-3, etc.
var2 = curr or prior
var3 = month or week or qtr or year


then for a 4th variable, I am combining all three

var 4 = var1& var2 & var3

so as an example, the output is "YOYCurrQtr".

But really what I want to be is "[qtr]=2 and [fy] = 2009"

(YOYCurrQtr is a variable i have defined above)

It sounds like you're wanting to select a variable location by concatenating the values of multiple variables together and then use that product as the variable name whose value you need.

In C# you could use a pointer.

In VBA, I think you either have to use a CASE statement, a nested if, or an array. No pointers that I'm familiar with.

I can't think of ANY reason in Access that this would be necessary. I'm thinking you can maybe accomplish your goal with 1 query and a form tied to that query.

What is your goal? Maybe I can help achieve that without so much juggling of variables.

8^D