how to hide a empty txtbox and show a label??

[_Bigred]

Hello Guys,

I have a report and I'm trying to do the following:


If a record has a null value for "Worker" then I want to show a label
called "Vacant". I've tried to put the following in various placed (using
the code button) but can't seem to figure it out.

The report is based on a join query from 2 tables, one table "MasterData"
contains the people and "Jobs" contains the various information about the
jobs (payrange, hiredate etc...)

TIA,
_Bigred


Private Sub Report_Open(Cancel As Integer)
If IsNull(Me!Worker) Then
Me!Worker.Visible = False
Me!Vacant.Visible = True
Else
Me!Worker.Visible = True
Me!Vacant.Visible = False
End If
End Sub

 

[Allen Browne]

Why not just change the Control Source of the text box to:
=Nz([Worker], "Vacant")

You may need to change the Name property of the text box too. Access gets
confused if it has the same Name as a field, but is bound to something else.

 

[_Bigred]

Well after doing that and having trouble, I realized that Worker is based on
a expression in the query
Worker:[MasterData!LASTNAME]&","&[MasterData!FIRSTNAME]

I remembered I had conditional formatting set to hide a "," that is on the
report for 2 jobs that don't have people in them.

1) How do I fix the report so the expression I put in the source query
doesn't generate a "," for jobs that don't have people in them? (So I'm not
just hiding them with color via conditional formatting).

Hopefully this doesn't make the fix too time consuming for you.
Thanks for your time & attention
_Bigred

Why not just change the Control Source of the text box to:
=Nz([Worker], "Vacant")

You may need to change the Name property of the text box too. Access gets
confused if it has the same Name as a field, but is bound to something
else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Guys,

I have a report and I'm trying to do the following:


If a record has a null value for "Worker" then I want to show a label
called "Vacant". I've tried to put the following in various placed (using
the code button) but can't seem to figure it out.

The report is based on a join query from 2 tables, one table "MasterData"
contains the people and "Jobs" contains the various information about the
jobs (payrange, hiredate etc...)

TIA,
_Bigred


Private Sub Report_Open(Cancel As Integer)
If IsNull(Me!Worker) Then
Me!Worker.Visible = False
Me!Vacant.Visible = True
Else
Me!Worker.Visible = True
Me!Vacant.Visible = False
End If
End Sub

 

[Allen Browne]

Try:
Worker: [MasterData!LASTNAME] & ", " + [MasterData!FIRSTNAME]

That should eliminate anyone who has no FirstName.

Alternatively, you probably have a WorkerID field, so you could use this in
the text box if you include the fields themselves in the query:
=IIf([WorkerID] Is Null, Null, [LASTNAME] & ", " + [FIRSTNAME]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well after doing that and having trouble, I realized that Worker is based
on
a expression in the query
Worker:[MasterData!LASTNAME]&","&[MasterData!FIRSTNAME]

I remembered I had conditional formatting set to hide a "," that is on
the report for 2 jobs that don't have people in them.

1) How do I fix the report so the expression I put in the source query
doesn't generate a "," for jobs that don't have people in them? (So I'm
not just hiding them with color via conditional formatting).

Hopefully this doesn't make the fix too time consuming for you.
Thanks for your time & attention
_Bigred

Why not just change the Control Source of the text box to:
=Nz([Worker], "Vacant")

You may need to change the Name property of the text box too. Access gets
confused if it has the same Name as a field, but is bound to something
else.

Hello Guys,

I have a report and I'm trying to do the following:


If a record has a null value for "Worker" then I want to show a label
called "Vacant". I've tried to put the following in various placed
(using the code button) but can't seem to figure it out.

The report is based on a join query from 2 tables, one table
"MasterData" contains the people and "Jobs" contains the various
information about the jobs (payrange, hiredate etc...)

TIA,
_Bigred

 

[_Bigred]

I will try this, and post back in a little while.

thanks again,
_Bigred

Try:
Worker: [MasterData!LASTNAME] & ", " + [MasterData!FIRSTNAME]

That should eliminate anyone who has no FirstName.

Alternatively, you probably have a WorkerID field, so you could use this
in the text box if you include the fields themselves in the query:
=IIf([WorkerID] Is Null, Null, [LASTNAME] & ", " + [FIRSTNAME]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well after doing that and having trouble, I realized that Worker is based
on
a expression in the query
Worker:[MasterData!LASTNAME]&","&[MasterData!FIRSTNAME]

I remembered I had conditional formatting set to hide a "," that is on
the report for 2 jobs that don't have people in them.

1) How do I fix the report so the expression I put in the source query
doesn't generate a "," for jobs that don't have people in them? (So I'm
not just hiding them with color via conditional formatting).

Hopefully this doesn't make the fix too time consuming for you.
Thanks for your time & attention
_Bigred

Why not just change the Control Source of the text box to:
=Nz([Worker], "Vacant")

You may need to change the Name property of the text box too. Access
gets confused if it has the same Name as a field, but is bound to
something else.

Hello Guys,

I have a report and I'm trying to do the following:


If a record has a null value for "Worker" then I want to show a label
called "Vacant". I've tried to put the following in various placed
(using the code button) but can't seem to figure it out.

The report is based on a join query from 2 tables, one table
"MasterData" contains the people and "Jobs" contains the various
information about the jobs (payrange, hiredate etc...)

TIA,
_Bigred

 

[_Bigred]

Hello Allen,

I got that issue all squared away, thank you for your help.

_Bigred

I will try this, and post back in a little while.

thanks again,
_Bigred

Try:
Worker: [MasterData!LASTNAME] & ", " + [MasterData!FIRSTNAME]

That should eliminate anyone who has no FirstName.

Alternatively, you probably have a WorkerID field, so you could use this
in the text box if you include the fields themselves in the query:
=IIf([WorkerID] Is Null, Null, [LASTNAME] & ", " + [FIRSTNAME]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well after doing that and having trouble, I realized that Worker is
based on
a expression in the query
Worker:[MasterData!LASTNAME]&","&[MasterData!FIRSTNAME]

I remembered I had conditional formatting set to hide a "," that is on
the report for 2 jobs that don't have people in them.

1) How do I fix the report so the expression I put in the source query
doesn't generate a "," for jobs that don't have people in them? (So I'm
not just hiding them with color via conditional formatting).

Hopefully this doesn't make the fix too time consuming for you.
Thanks for your time & attention
_Bigred



Why not just change the Control Source of the text box to:
=Nz([Worker], "Vacant")

You may need to change the Name property of the text box too. Access
gets confused if it has the same Name as a field, but is bound to
something else.

Hello Guys,

I have a report and I'm trying to do the following:


If a record has a null value for "Worker" then I want to show a label
called "Vacant". I've tried to put the following in various placed
(using the code button) but can't seem to figure it out.

The report is based on a join query from 2 tables, one table
"MasterData" contains the people and "Jobs" contains the various
information about the jobs (payrange, hiredate etc...)

TIA,
_Bigred