Solved If single then Null otherwise keep selecting value (1 Viewer)

[Mercy Mercy]

Hi everyone. I have two related tables:-
1. tblGrades.
2. tblStreams.
The two tables must have values. By default tblStreams should have "SINGLE" implying single stream. My question is HOW WILL I MAKE IT IN REPORT "SINGLE" not to be seen. In the same tblStreams other values are
NORTH
SOUTH
EAST.
I want those values to be visible if selected but "SINGLE" not to be seen.
Thanks in advance.

 

[GaP42]

In the text control txtStreamType displaying the StreamType (?) you could try to build an expression like: IIF (txtStreamType = "Single", "", [StreamType])

 

[Josef P.]

Should only the value 'Single' not be displayed or the whole dataset with 'Single' not be included?

For the first case:
I assume that there is a 3rd table for the selection of SINGLE, NORTH, SOUTH and EAST.
You could perhaps write the display text in this table, which would then be empty for Single.

 

[Mercy Mercy]

In the text control txtStreamType displaying the StreamType (?) you could try to build an expression like: IIF (txtStreamType = "Single", "", [StreamType])

I get #Type!

 

[Mercy Mercy]

Should only the value 'Single' not be displayed or the whole dataset with 'Single' not be included?

For the first case:
I assume that there is a 3rd table for the selection of SINGLE, NORTH, SOUTH and EAST.
You could perhaps write the display text in this table, which would then be empty for Single.

Only value "SINGLE" should not be displayed because the school has grades which are only one streamed so there is no need to say GRADE 4 SINGLE, it should only show GRADE 4 in the Report

 

[gemma-the-husky]

Why store SINGLE at all if that's the default. You only need to store an override setting. Then you wouldn't have any issues.

 

[Mercy Mercy]

Why store SINGLE at all if that's the default. You only need to store an override setting. Then you wouldn't have any issues.

If I don't put the value it gives error because the two tables are related. This is what I get
"YOU MUST ENTER A VALUE IN IN THE TABLE "tblStream.StreamID" field. That is why I use "SINGLE" implying "SINGLE STREAM" or "ONE GRADE".
In a Report there is no need to show "SINGLE" because it is ONE GRADE.

 

[jdraw]

Mercy Mercy,
Could you step back a little and give an overview description of "the business" you are trying to support with this automation/database? I see Grades and Streams in #1. In #5, school...
It would be helpful to readers and responders - and you, if there was an overview describing the pieces and showing how the pieces fit with each other.
I know you are very familiar with your environment, but readers (me anyway) like to see an issue/problem/opportunity in context.

 

[GaP42]

I get #Type!

Create the txtStreamType control on your report alongside the existing display of the stream info. Build the expression in the txtStreamType. You can hide you existing display of the stream data when you see the Single text is not shown in the txtStreamType control. If this does not work you will need to provide more detail on the data types and the report

 

[KitaYama]

@Mercy Mercy
Why do you tend to type in all caps?

Why You Shouldn't Write in All Caps

Writing in all caps is the written equivalent of shouting, which is (generally) considered rude. So, think twice before pressing Caps Lock.

www.lifewire.com

 

[Mercy Mercy]

@Mercy Mercy
Why do you tend to type in all caps?

View attachment 108026

Why You Shouldn't Write in All Caps

Writing in all caps is the written equivalent of shouting, which is (generally) considered rude. So, think twice before pressing Caps Lock.

www.lifewire.com

Funny . No harm intended

 

[Pat Hartman]

If I don't put the value it gives error because the two tables are related.

I'm going to guess that you are using a table level lookup. You are seeing text but storing an ID. That is problem #1. Problem #2 is that you have a default of 0 rather than null for this field. Therefore when you add a row, if you don't choose an option, then the 0 gets stored and 0 is invalid. As long as the field is not marked as required, removing the "0" and allowing the field to default to null will solve the problem. Then you have to write an update query that will select all the "Single" values and replace them with null. BTW "" is NOT null. "" is a ZLS (zero length string) and because it is a string, it is invalid in your IIf()

IIF (txtStreamType = 4, Null, [StreamType])

I just arbitrarily picked 4. You have to look at the table to figure out what the ID actually is.

 

[Mercy Mercy]

I'm going to guess that you are using a table level lookup. You are seeing text but storing an ID. That is problem #1. Problem #2 is that you have a default of 0 rather than null for this field. Therefore when you add a row, if you don't choose an option, then the 0 gets stored and 0 is invalid. As long as the field is not marked as required, removing the "0" and allowing the field to default to null will solve the problem. Then you have to write an update query that will select all the "Single" values and replace them with null. BTW "" is NOT null. "" is a ZLS (zero length string) and because it is a string, it is invalid in your IIf()

IIF (txtStreamType = 4, Null, [StreamType])

I just arbitrarily picked 4. You have to look at the table to figure out what the ID actually is.

Fantastic. You are genius. Thanks so much.

 

[Pat Hartman]

You're welcome.

 

[Mercy Mercy]

In the text control txtStreamType displaying the StreamType (?) you could try to build an expression like: IIF (txtStreamType = "Single", "", [StreamType])

Thanks. This also works