Condition on report forms

[Anishtain4]

I have a report, but there are some equivalents for the fields I get from my table, for example type=1 means water system and type=2 means sewer system. as you can see i have added the type field in the table, I tried =iff(type=1,"water","sewer") in the control source box, but when I open up the form it asks me the iff clause and return #Error on the field.
How should I manage this?

 

[Galaxiom]

The name substitution is better done with a RowSource in a combo. Much more efficient.

RowSourceType: Value List
RowSource: 1;Water;2;Sewer
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0;3cm

Or you can use a lookup table as the RowSource.

Another way is to use numbers that are positive, negative, zero and Null as the values and a Format property.

Assuming positive numbers for water, negative for sewer, zero for beer, Null for whiskey (The table field must be a number datatype.)

Format: "Water";"Sewer";"Beer";"Whiskey"

You can even set colours by putting the colour names in square brackets following the value for display.

 

[Anishtain4]

Thanks man, you are amazing, I love you. However I knew the second method but I didn't want to make another table since the database is online and it's easier to change only the forms on front end. I used the first method and that is really helpful.
I didn't follow you on formats haven't ever used them.

have moved the titles from header to the detail part because I can't put them on one row, for example address of customers take one line itself. Now my problem is that if one customer has not given any address the title remains there, is there any way to turn its visibility off?

Another problem that I have and is bothering now, is that every project will have a responsible which may have not been set at first, there are two tables for projects and responsible personnel, if I join the tables projects that has no responsible yet will be omitted but I wanna see them, what's your miracle for this?

 

[Galaxiom]

Use an outer join. (Right click on the join line in the query designer and choose the option "show all records from Project and the matching records from Responsible")

 

[Galaxiom]

I didn't follow you on formats haven't ever used them.

The Format property of a control or field is a versatile feature that allows the display of the value to be controlled. The best known are for date and times formats. Extra literal characters can also be added using a backslash before them.

The perhaps lesser known ability is the replacement of numbers with strings and the colouring of the displayed value.

In this case the format property has four arguments separated by semicolons. The first is applied to positive numbers, the second to negative numbers, the third to zero and the last to Null values.

A string in the argument will replace the value when it is in those ranges.

So in the example I gave, any positive number will be replaced by the word "Water".

This also provides a simple conditional formatting ability and is often used to turn negative numbers red for example.

So in the case of an account balance one might use the format property:

 ;[red];"Nil";"Error"[blue]

This will turn negative numbers red, display "Nil" for zero balances and "Error" in blue if there is no value.

 

[Anishtain4]

I used to use the conditional formatting on the format menu, where do you write the piece of code you mentioned? in the format property of the of the object? I tried that and couldn't make it

 

[Galaxiom]

It goes in the Format property but note that you would have to change the values that code for water and sewer so that could be distinguished as positive, negative or zero.

If you have the combo working then I would just go with that.