How to change the caption of a field based on the data in it

[mew0717]

Okay, that title may sound confusing, so let me elaborate.

I have a Form that, when the user selects 1 of 4 options, the appropriate data is returned in a query under the generic field name "Category".

What i'd ultimately like to do is make it so if the user selects Category 1 in the Form, the field name in that query is "Category 1", If they select "Category 2" in the form then the caption reads "Category 2".
Also, is it possible to make the field in the query not show if they pick "category 4"?

Will I need VBA for this? I bought some books for access 2007 (Which is what im currently using) but do not know very much about it.

Any help you could offer would be very much appreciated.

 

[David B]

I don't know a lot myself but I'll try.

I assume the options 1-4 are buttons or alike and they all run the same query.

You could set 4 Qry's 1 for each Category. Can you elaborate a little more lets see if between us we can nut this thing out.

I have learnt a lot by asking ?'s here and I am always reading other posts.
I figure if I read other posts I can always apply my new learning in my own work. I love this place it teaches me something new every day and I have always had great responses and fixes to all my own ?'s for help.

As I say give us a little more and see if we can nut it out.
david