Option Values in Qeries (2000)

Topic

New Reply

Hello, how do I set the field on a query to represent the actual name of a selection from an option button opposed to the query returing value 1, 2 etc?

Many thanks Darren.

Reply | Quote

Replies

I’m assuming you mean that you want to return the label caption property that corresponds with the option button selected?
If so, there are at least a couple of options for you… Depending on how many option buttons are in the group…

You could use an IIF statement and nest the results… For example:
fldChoice: IIf([Forms]![frmName].[optGroupName]=1,[Forms]![frmName].[lblOneName].[Caption],IIf([Forms]![frmName].[optGroupName]=2, [Forms]![frmName].[lblTwoName].[Caption], [Forms]![frmName].[lblOneName].[Caption])

Or you could try something like the Choose function…
=Choose([Forms]![frmName].[optGroupName], [Forms]![frmName].[lblOneName].[Caption], [Forms]![frmName].[lblTwoName].[Caption],[Forms]![frmName].[lblThreeName].[Caption])

(of course, you’ll have to change the names I’ve used to your own… )

The Choose looks cleaner… but I haven’t tested it… Give both a try…
HTH…

Reply | Quote

I’m assuming you mean that you want to return the label caption property that corresponds with the option button selected?
If so, there are at least a couple of options for you… Depending on how many option buttons are in the group…

You could use an IIF statement and nest the results… For example:
fldChoice: IIf([Forms]![frmName].[optGroupName]=1,[Forms]![frmName].[lblOneName].[Caption],IIf([Forms]![frmName].[optGroupName]=2, [Forms]![frmName].[lblTwoName].[Caption], [Forms]![frmName].[lblOneName].[Caption])

Or you could try something like the Choose function…
=Choose([Forms]![frmName].[optGroupName], [Forms]![frmName].[lblOneName].[Caption], [Forms]![frmName].[lblTwoName].[Caption],[Forms]![frmName].[lblThreeName].[Caption])

(of course, you’ll have to change the names I’ve used to your own… )

The Choose looks cleaner… but I haven’t tested it… Give both a try…
HTH…

Reply | Quote

Another option, in addition to the suggestions from Trudi, would be to create a simple two field table with the values that correspond to the results of the option button, join to the table, and display the text that corresponds to the option choosen – you would use the value of the option group as a criteria in the query.

Reply | Quote

Thank you to you both, I am now at home so unable ti try until I return to work tomorrow. On the presumption they will work – thanks again.

Darren.

Reply | Quote

Thank you to you both, I am now at home so unable ti try until I return to work tomorrow. On the presumption they will work – thanks again.

Darren.

Reply | Quote

Another option, in addition to the suggestions from Trudi, would be to create a simple two field table with the values that correspond to the results of the option button, join to the table, and display the text that corresponds to the option choosen – you would use the value of the option group as a criteria in the query.

Reply | Quote