how do you set the control source in a textbox to a query field? 1

[cutestuff]

hi,

I need help! Please!
I have a textbox in a report that gets its field list from a query (qryDrawing). I do not know the proper syntax to reference the query field.
I am trying to do something like this:
=IIf(qryDrawing.[FIELD] = SA","YES","NO")

And I would like to set [FIELD] to the value of the combo box in my main form. (that is, [FIELD] = [Forms]![frmName]![cboName]).

So I tried doing this:
=IIf(qryDrawing.[Forms]![frmName]![cboName])="SA","YES","NO")

but it doesn't recognize qryDrawing. What is the syntax for this?

I hope this made sense.

Thank you so much in advance.

 

[dhookom]

Just use:
=IIf([FIELD] = "SA","YES","NO")
Make sure the name of the text box is not the name of a field in the report's record source.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cutestuff]

hi,

thanks for your reply.
My problem is that I have several fields in my query that I would like to pass on to [FIELD].

I have a combo box with a list of systems that a user selects from. However, in the query these systems are all stored as individual fields (system1, system2, etc).

That is why I was trying to do this:
IIf(qryDrawing.[Forms]![frmName]![cboName])="SA","YES","NO").

But it doesn't recognize qryDrawing. I even tried to do this:
=IIf([dbs].[QueryDefs]![qryDrawing]="SA","YES","NO")

But it doesn't recognize this either. I need it to know that I'd like it to grab a specific field in the query that is specified by what the user selects in the combo box.

Please help .

Thank you so much.

 

[dhookom]

I'm lost. Can you describe your significant tables and fields as well as which are included in your report's record source? Then tell us what you would like to display in your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cutestuff]

Sorry.

Okay,I'll try to describe it as best as I can.
I have a query called qryDrawings. In it there are column fields for each system number - say 0090, 0091, 0092, etc.(these are all column fields). These fields contain strings - either an SA, an S or an SR.

I have a main form where the user chooses a system number in a combo box (called cboSystem) and clicks on 'reports'.

The report gets its data/field list from qryDrawings. I would like one of the text box in the report to take what the user chose as a system number and then in the query grab the appropriate column field. For instance, if the user selected 0090 in the combo box, I would like the text box to display records for 0090 in qryDrawings. But since the system numbers are stored as individual column fields, I do not know how to do it (without writing a really long IIF statement).

I'm so sorry to be so confusing. I hope this cleared it up a little.

Thank you so much once again. Appreciate the help.

 

[dhookom]

The primary issue is your table structure. You or someone else has created an un-normalized table with data values as field names. If you can't change your structure, consider using a union query to normalize:

SElECT PKField, "0090" as SysNum, [0090] as SA_S_SR
FROM qryDrawings
UNION ALL
SElECT PKField, "0091", [0091]
FROM qryDrawings
UNION ALL
SElECT PKField, "0092", [0092]
FROM qryDrawings
UNION ALL
  --etc--
FROM qryDrawings;

You can then create a query that selects records based on a field value rather than a field name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cutestuff]

hi dhookum,

Thanks so much for your help!
I didn't create the table - they had it set up like that already. I was trying to find a way to make it work, but I found that it is easier to just use a union query.

thanks so much again!
Really, really appreciate it!