Datasources and Text Boxes

[TheBlondOne]

Ok this is probably simple but its early Monday morning and i just don't understand it ,

What i've got is a table in SQL with 3 columns, the first colum is ID, the second column is description and the third column is value.

In my report i've got several text boxes which I need to display the value from the description column if the ID column is a certain number

i.e

=iif(Fields.ID.Value = 1, Fields!Description.Value,"")

This is the code i'd expected to be able to insert into the first text box to return te first description value.

in the second text box i've put

=iif(Fields.ID.Value = 2, Fields!Description.Value,"")

and so on through the remaining text boxes.

The problem i've got is that the code is only working for the final text box, i.e the very last row in the table, so the code

=iif(Fields.ID.Value = 20, Fields!Description.Value,"")

actualy returns the correct value but the others do not return anything.

Has anyone got any ideas as to why this isn't working or what i'm doing wrong.

TIA

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[xlbo]

If the textbox is on it's own, then it is not bound to a data source. As such, you cannot use the syntax you have described - you must also use an aggregate e.g.

=FIRST(Fields.ID.Value, "DataSource Name")

However, you also mention a table - if the data is in a table then what you have described should work

Please can you confirm how your data is set up - is it in a table ? if so how is it arranged ? or is it a set of text boxes ? If so, do they sit within another component ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TheBlondOne]

Hi Geoff thanks for the reply,

The data is in a SQL table and the way i've had to design the report is to use a lot of text boxes, these are all individual boxes on the canvas and are not sat within other components.

I realised I couldn't attach a datasource to the text box (is there away around this) but thought i could still use the formula's i used above.

With the code you used can you use second, third, fourth etc or can you only use first?

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[xlbo]

There is no way that I know of to attach a datasource to a textbox - hence you have to use the aggregate functions. There is no 2nd / 3rd etc aggregate function

You may have to do some thinking about how you get your data - what is the purpose of the report ? what is the significance of displaying the description for a certain ID ? are the IDs not tied to the descriptions ? When you perform the query, will you get a bucnh of blanks and only a few IDs with a description or does it always return all Ids and associated descriptions.

There's probably a way around this but it will be dependant on what you are trying to achieve....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TheBlondOne]

Thanks for the replies, due to time constraints i've just included individual tables rather than text boxes and that works fine.

Again thanks for the replies.



-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[Catadmin]

You can, however, go into the Expressions of the textbox and use a different datasource than the one the table is bound to. Unfortunately, it does make your expression code extremely complicated, but I've done it before.

The downside is that you have to have a parameter passing a value from the table-bound datasource to the other datasource to get it to work correctly.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"