Please help me!!!

[Psychobyrne]

Hi everyone,

i am a new member on the forum and the reason why I have joined specifically is because Microsoft Access is driving me crazy!

I have been trying to write a formula for a query and it just doesn't seem to work. If any of you have any ideas then I would greatly appreciate your help.

Basically what I am trying to do is create a field that will detect where one of seven tick boxes has been ticked, and then apply a number so that I can have a field that just shows the number that applies to that record rather than seven fields of true and falses.

What I have done is this:

Area Analysis: IIf([Product Mention]=Yes, [Area Analysis]=1), IIf([Product Mention with Explanation]=Yes, [Area Analysis]=2), IIf([Product Mention with Photograph]=Yes, [Area Analysis]=3), IIf([Product Mention with Quote]=Yes, [Area Analysis]=4), IIf([Product Mention, Photograph and Explanation]=Yes, [Area Analysis]=5), IIf([Product Mention, Explanation and Quote]=Yes, [Area Analysis]=6), IIf([Product Mention, Photograph, Explanation and Quote]=Yes, [Area Analysis]=7)

And it doesn't work. If any of you can see where I am going wrong or offer any help then I would be eternally gratefull.

Cheers

Nic

 

[Norie]

What's the name of the field?

Do you have multiple fields for what is basically the same type of data?

 

[HomerJ]

Is the end user expected to select only one option, or are they allowed to choose multiple boxes? If they are supposed to be limited to one choice, then a combo box or option group would seem to be a better approach than checkboxes.

 

[Psychobyrne]

the name of the field is Area Analysis, and I have loads of fields. The fields called Product Mention, Product Mention with Explanation etc are yes/no boxes, and Area Analysis is supposed to be a number field that says 1-7 depending on which of the yes/no boxes is selected.

Cheers for taking an interest!

 

[Psychobyrne]

I agree a combo box would have been better but, there are loads of other formulas attached to the tick boxes.

Basically the end user is supposed to just select one box, and depending on which box they select a formula comes into play to calculate the advertising value of a newspaper article. I cannot use a combo box (or at least I don't know how to) as each of the seven options has a different formula to calculate this value.

 

[HomerJ]

If they are limited to one option, then an option group with radio buttons seems like it would be an ideal solution. If you set up an option group with radio buttons, each radio button would have a unique number from 1 to 7. If I understand your situation correctly, that seems like it may be the ideal solution. If I'm off base, please let me know.

 

[Psychobyrne]

thanks HomerJ...just one question....what is a radio button?

 

[HomerJ]

Morning Nic.

Radio buttons are also known as Option Buttons on your forms toolbar. It looks like a black dot in a circle. The reason they are called radio buttons is like the preset buttons on your car radio, you can only select one option. If you choose a different button, the previous one is deselected. They are very useful when your end users are intended to select a single option from multiple choices.

Hope I described that well enough. If not, just let me know.

 

[Psychobyrne]

Hi HomerJ

Thanks again for all your help, I am still having problems though. I have found the Radio buttons now but there is a problem with using them.

To run the reports that I need I will be creating pivots in excell off the query, this has to be done as there are four tables that are all interconected in the Query and it is the only place where a lot of the info appears.

Adding these Radio Buttons only seems to add them to the Form and not the Query. Also, there are thirteen Forms all running off the same Query and these buttons would have to be added to each of them.

This is why I wanted to just add a formula into the query to let me know which of the yes/no boxes had been selected.

If you have any idea of where I am going wrong with the formula then I would be eternally grateful. If not well, thanks for all your help, you have certainly given me a lot to think about which will be useful when doing other Forms.

Cheers

Nic

 

[HomerJ]

Greetings Nic.

Sounds like a fairly complex setup you have there. Can you perhaps tell me what criteria your query is based on? A radio button simply gives you a number between 1 and whatever number of radio buttons you have. There may be a way to use that in selecting your criteria for your query.