Visible Condition on a Label

[Bob V]

My Label [lblOverdue] in set to No Visible on my form, When I select my
Combo Box [cmbClientsDue] and the 8th Column shows a date more than 30 days
but less than 60 days I want my label to appear, Any Help
Please............Bob

 

[Allen Browne]

Right-click the label and Change To | Text Box.

It now has a Control Source, which you can set to an expression such as
this:
=IIf(DateDiff("d", [cmbClientsDue], Date()) Between 30 And 60, "Overdue",
Null)

This assumes that the bound column of the combo is actually a date/time
value. If the combo is unbound, set its Format property to (say) Short Date,
so Access knows the data type.

It is possible to code the visible property of a label, but that's slower,
and doesn't work on a continuous form.

 

[Bob V]

Thanks Allen , Just cant get it to show Overdue . My Combo box is the result
of this Query
MyDate is Column 7 and MaxOfBillDate is Column 8
Thanks for the help...Bob
SELECT tblOwnerInfo.OwnerID,
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1)
AS SortVal,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
Name, qTotalDueForPayment.TotalDue AS DueAmount,
qPaidAmountForPayment.PaidTotal AS Paid,
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0) AS
Payable, IIf(Not IsNull(),"e") AS EmailAlert1,
Format([MaxOfBillDate],"d-mmm-yy") AS MyDate,
qPaidAmountForPayment.MaxOfBillDate,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
NameTest
FROM (tblOwnerInfo LEFT JOIN qPaidAmountForPayment ON tblOwnerInfo.OwnerID =
qPaidAmountForPayment.OwnerID) LEFT JOIN qTotalDueForPayment ON
tblOwnerInfo.OwnerID = qTotalDueForPayment.OwnerID
WHERE (((tblOwnerInfo.Status) Like 'Active*'))
ORDER BY
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1),
IIf(Not IsNull([Email]),"e") DESC ,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ', ')
& " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName);

[QUOTE="Allen Browne"]
Right-click the label and Change To | Text Box.

It now has a Control Source, which you can set to an expression such as
this:
=IIf(DateDiff("d", [cmbClientsDue], Date()) Between 30 And 60, "Overdue",
Null)

This assumes that the bound column of the combo is actually a date/time
value. If the combo is unbound, set its Format property to (say) Short
Date, so Access knows the data type.

It is possible to code the visible property of a label, but that's slower,
and doesn't work on a continuous form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[QUOTE="Bob V"]
My Label [lblOverdue] in set to No Visible on my form, When I select my
Combo Box [cmbClientsDue] and the 8th Column shows a date more than 30
days but less than 60 days I want my label to appear, Any Help
Please............Bob[/QUOTE]
[/QUOTE]

 

[Allen Browne]

So the value you want to refer to is in the 8th column of the combo?

Instead of:
[cmbClientsDue]
you will need to us:
[cmbClientsDue].Column(7)
Note that the first column is number zero.

There are a couple of other pitfalls here. Combo columns are Text: there
could be issues if there is no value in the column (i.e. the date is
missing.)

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

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

Thanks Allen , Just cant get it to show Overdue . My Combo box is the
result of this Query
MyDate is Column 7 and MaxOfBillDate is Column 8
Thanks for the help...Bob
SELECT tblOwnerInfo.OwnerID,
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1)
AS SortVal,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
Name, qTotalDueForPayment.TotalDue AS DueAmount,
qPaidAmountForPayment.PaidTotal AS Paid,
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0)
AS Payable, IIf(Not IsNull(),"e") AS EmailAlert1,
Format([MaxOfBillDate],"d-mmm-yy") AS MyDate,
qPaidAmountForPayment.MaxOfBillDate,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
NameTest
FROM (tblOwnerInfo LEFT JOIN qPaidAmountForPayment ON tblOwnerInfo.OwnerID
= qPaidAmountForPayment.OwnerID) LEFT JOIN qTotalDueForPayment ON
tblOwnerInfo.OwnerID = qTotalDueForPayment.OwnerID
WHERE (((tblOwnerInfo.Status) Like 'Active*'))
ORDER BY
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1),
IIf(Not IsNull([Email]),"e") DESC ,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName);

[QUOTE="Allen Browne"]
Right-click the label and Change To | Text Box.

It now has a Control Source, which you can set to an expression such as
this:
=IIf(DateDiff("d", [cmbClientsDue], Date()) Between 30 And 60, "Overdue",
Null)

This assumes that the bound column of the combo is actually a date/time
value. If the combo is unbound, set its Format property to (say) Short
Date, so Access knows the data type.

It is possible to code the visible property of a label, but that's
slower, and doesn't work on a continuous form.

[QUOTE="Bob V"]
My Label [lblOverdue] in set to No Visible on my form, When I select my
Combo Box [cmbClientsDue] and the 8th Column shows a date more than 30
days but less than 60 days I want my label to appear, Any Help
Please............Bob[/QUOTE]
[/QUOTE]
[/QUOTE]

 

[Bob V]

Thanks Allen works perfect, Can I add in, if only Column(3) >1
Thanks for the help...Bob

So the value you want to refer to is in the 8th column of the combo?

Instead of:
[cmbClientsDue]
you will need to us:
[cmbClientsDue].Column(7)
Note that the first column is number zero.

There are a couple of other pitfalls here. Combo columns are Text: there
could be issues if there is no value in the column (i.e. the date is
missing.)

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

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

Thanks Allen , Just cant get it to show Overdue . My Combo box is the
result of this Query
MyDate is Column 7 and MaxOfBillDate is Column 8
Thanks for the help...Bob
SELECT tblOwnerInfo.OwnerID,
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1)
AS SortVal,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName)
AS Name, qTotalDueForPayment.TotalDue AS DueAmount,
qPaidAmountForPayment.PaidTotal AS Paid,
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0)
AS Payable, IIf(Not IsNull(),"e") AS EmailAlert1,
Format([MaxOfBillDate],"d-mmm-yy") AS MyDate,
qPaidAmountForPayment.MaxOfBillDate,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName)
AS NameTest
FROM (tblOwnerInfo LEFT JOIN qPaidAmountForPayment ON
tblOwnerInfo.OwnerID = qPaidAmountForPayment.OwnerID) LEFT JOIN
qTotalDueForPayment ON tblOwnerInfo.OwnerID = qTotalDueForPayment.OwnerID
WHERE (((tblOwnerInfo.Status) Like 'Active*'))
ORDER BY
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0))>1,0,1),
IIf(Not IsNull([Email]),"e") DESC ,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ',
') & " " &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName);

[QUOTE="Allen Browne"]
Right-click the label and Change To | Text Box.

It now has a Control Source, which you can set to an expression such as
this:
=IIf(DateDiff("d", [cmbClientsDue], Date()) Between 30 And 60,
"Overdue", Null)

This assumes that the bound column of the combo is actually a date/time
value. If the combo is unbound, set its Format property to (say) Short
Date, so Access knows the data type.

It is possible to code the visible property of a label, but that's
slower, and doesn't work on a continuous form.

My Label [lblOverdue] in set to No Visible on my form, When I select my
Combo Box [cmbClientsDue] and the 8th Column shows a date more than 30
days but less than 60 days I want my label to appear, Any Help
Please............Bob
[/QUOTE]
[/QUOTE]
[/QUOTE]

 

[Allen Browne]

Thanks Allen works perfect,
Can I add in, if only Column(3) >1

Yes. You'll end up with an expression like this:

=IIf((DateDiff("d", [cmbClientsDue].Column(7), Date()) Between 30 And 60)
AND ([cmbClientsDue].Column(3) > 1), "Overdue", Null)

 

[Bob V]

Thanks Allen Great, is it possible to run these 3 codes together?
Hope I have got them right......Bob
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between Month(Date) -
2, 1 And Month(Date) - 2, 31)
AND ([cmbClientsDue].Column(3) > 1), "1 Month Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between Month(Date) -
3, 1 And Month(Date) - 3, 31)
AND ([cmbClientsDue].Column(3) > 1), "2 Months Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between Month(Date) -
4, 1 And Month(Date) - 4, 31)
AND ([cmbClientsDue].Column(3) > 1), "3 Months Overdue", Null)

Thanks Allen works perfect, Can I add in, if only Column(3) >1

Yes. You'll end up with an expression like this:

=IIf((DateDiff("d", [cmbClientsDue].Column(7), Date()) Between 30 And 60)
AND ([cmbClientsDue].Column(3) > 1), "Overdue", Null)

 

[Allen Browne]

It is possible to combine expression, but I can't make sense of that.

I got stuck with the comma in the middle of the Between expression:
Between Month(Date) - 2,

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

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

Thanks Allen Great, is it possible to run these 3 codes together?
Hope I have got them right......Bob
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 2, 1 And Month(Date) - 2, 31)
AND ([cmbClientsDue].Column(3) > 1), "1 Month Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 3, 1 And Month(Date) - 3, 31)
AND ([cmbClientsDue].Column(3) > 1), "2 Months Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 4, 1 And Month(Date) - 4, 31)
AND ([cmbClientsDue].Column(3) > 1), "3 Months Overdue", Null)

Thanks Allen works perfect, Can I add in, if only Column(3) >1

Yes. You'll end up with an expression like this:

=IIf((DateDiff("d", [cmbClientsDue].Column(7), Date()) Between 30 And 60)
AND ([cmbClientsDue].Column(3) > 1), "Overdue", Null)

 

[Bob V]

Allen, I thought that was how you got the first day of last month and the
last day of last month!
Thanks..............Bob

It is possible to combine expression, but I can't make sense of that.

I got stuck with the comma in the middle of the Between expression:
Between Month(Date) - 2,

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

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

Thanks Allen Great, is it possible to run these 3 codes together?
Hope I have got them right......Bob
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 2, 1 And Month(Date) - 2, 31)
AND ([cmbClientsDue].Column(3) > 1), "1 Month Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 3, 1 And Month(Date) - 3, 31)
AND ([cmbClientsDue].Column(3) > 1), "2 Months Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 4, 1 And Month(Date) - 4, 31)
AND ([cmbClientsDue].Column(3) > 1), "3 Months Overdue", Null)

Thanks Allen works perfect, Can I add in, if only Column(3) >1


Yes. You'll end up with an expression like this:

=IIf((DateDiff("d", [cmbClientsDue].Column(7), Date()) Between 30 And
60) AND ([cmbClientsDue].Column(3) > 1), "Overdue", Null)

 

[Bob V]

Allen if Column(7) has no date I am getting #Error........Thanks Bob
=IIf((DateDiff("d",cbOwnerName.Column(7),Date()) Between 30 And 60) And
(cbOwnerName.Column(5)>1),"1 Month Overdue",Null)

Allen, I thought that was how you got the first day of last month and the
last day of last month!
Thanks..............Bob

It is possible to combine expression, but I can't make sense of that.

I got stuck with the comma in the middle of the Between expression:
Between Month(Date) - 2,

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

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

Thanks Allen Great, is it possible to run these 3 codes together?
Hope I have got them right......Bob
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 2, 1 And Month(Date) - 2, 31)
AND ([cmbClientsDue].Column(3) > 1), "1 Month Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 3, 1 And Month(Date) - 3, 31)
AND ([cmbClientsDue].Column(3) > 1), "2 Months Overdue", Null)
=IIf((DateDiff("m", [cmbClientsDue].Column(7), Date()) Between
Month(Date) - 4, 1 And Month(Date) - 4, 31)
AND ([cmbClientsDue].Column(3) > 1), "3 Months Overdue", Null)

Thanks Allen works perfect, Can I add in, if only Column(3) >1


Yes. You'll end up with an expression like this:

=IIf((DateDiff("d", [cmbClientsDue].Column(7), Date()) Between 30 And
60) AND ([cmbClientsDue].Column(3) > 1), "Overdue", Null)

 

[Allen Browne]

Yes: my 2nd reply flagged that as a potential issue.

To work around that, you might need an IIf() expression.
(Gets a bit messy.)