A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

If-Then-Else statements



 
 
Thread Tools Display Modes
  #11  
Old December 14th, 2009, 03:24 PM posted to microsoft.public.access.gettingstarted
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default If-Then-Else statements

Barb,
I agree with Ken Sheridan. The code we suggested is OK, so the
PaymentID may be something other than Null.
Is it Null ?
Is it "" ?
Is it 0 (zero)?

A trick I use in these situations is to set up the Format for the
PaymentID control...
#.00 ; -#.00 ; .00 ; \Null

If either a Positive or negative value = display normally (123.45
or -123.45)
If zero = display as .00
If Null = Display as "Null"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


"BarbS via AccessMonster.com" [email protected] wrote in message
news:[email protected]
Thank you John, for your reply. I'm using MS Access 2007. I believe the
PaymentID is NULL, but I tried your function just in case, that still
didn't
work. I also tried including all the labels, that didn't work either.
I'm
lost at what to do next.

John Spencer wrote:
You might need to hide the label also.

What version of Access are you using?
Are you sure the value of PaymentID is NULL?
Is it possible that it is a zero-length string?

You could use something like the following to handle Nulls, zero-length
strings, and strings that consist of multiple spaces.

Me.PaymentID.Visible = Len(Trim(Me.PaymentID & ""))0
Me.PaymentAmount.Visible = Len(Trim(Me.PaymentID & ""))0
Me.PaymentDate.Visible = Len(Trim(Me.PaymentID & ""))0

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you and the others for helping me try and figure this out.
Obviously I 'm just learning the code, but I've tried all suggestions to
no

[quoted text clipped - 27 lines]
End If
End Sub


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1



Ads
  #12  
Old April 19th, 2010, 05:54 AM posted to microsoft.public.access.gettingstarted
Jake Davis
external usenet poster
 
Posts: 1
Default If-Then-Else statements (hiding labels for empty fields)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID Control Value Is Null

Me.PaymentID_Label.Visible = Not IsNull([PaymentID])
Me.PaymentAmt_Label.Visible = Not IsNull([PaymentID])
Me.PaymentDate_Label.Visible = Not IsNull([PaymentID])

End Sub

This is the solution for the rptCitationsAndPaymentsModified report to hide the LABELS when there is no data in them for the associated Text Box in the Tophill.accdb project.

We test PaymentID Text Box for a Null Value, meaning we have NO DATA because the joker hasn't paid his or her ticket. When that check comes back true, then we hide the LABELS not the text boxes themselves(because they're already empty!). Thus when we do the print preview on the report, it omits the three LABELS for Payment ID, Payment Amt, and Payment Date because PaymentID text box has no data to display.

In theory you could take this even further, hiding ALL labels so you end up with blank lines in the report between people who have actually paid their ticket but then you are hiding text boxes (with data) AND labels.

I'm not claiming to be new or revolutionary, I just riffed on the code and took a look at what we were hiding. It wasn't the TEXT BOX it was the LABEL. So changing the VB code to reflect got me the desired resolution. We didn't need to hide the text box because it was already empty, just it's label.







Al Campagna wrote:

Barb,I agree with Ken Sheridan.
14-Dec-09

Barb,
I agree with Ken Sheridan. The code we suggested is OK, so the
PaymentID may be something other than Null.
Is it Null ?
Is it "" ?
Is it 0 (zero)?

A trick I use in these situations is to set up the Format for the
PaymentID control...

If either a Positive or negative value = display normally (123.45
or -123.45)
If zero = display as .00
If Null = Display as "Null"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you will never work a day in your life."

Previous Posts In This Thread:

On Saturday, December 12, 2009 4:24 PM
BarbS via AccessMonster.com wrote:

If-Then-Else statements
I am presently studying Access and having difficulty understanding building
event codes. What I am trying to do is suppress the printing of three
controls in a report if they are Null. This is how I wrote the code, but it
is not working. Can someone please help. Thank you,

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'
If IsNull([PaymentID]) Then
[PaymentID] , [PaymentAmt], [PaymentDate].Visible = False

Else: [PaymentID].Visible = True

End If
End Sub

--
Message posted via http://www.accessmonster.com

On Saturday, December 12, 2009 5:04 PM
Al Campagna wrote:

Barb,Check Help for syntax assistance on the For-Else-Then statement.
Barb,
Check Help for syntax assistance on the For-Else-Then statement.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([PaymentID]) Then
[PaymentID].Visible = False
[PaymentAmt].Visible = False
[PaymentDate].Visible = False
Else
[PaymentID].Visible = True
[PaymentAmt].Visible = True
[PaymentDate].Visible = True
End If
End Sub

If you do not make Amt and Date visible again, in the Else statement,
they will never be visible again during that report printout.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you will never work a day in your life."

On Saturday, December 12, 2009 5:45 PM
Daniel Pineault wrote:

How about:Private Sub Detail_Format(Cancel As Integer, FormatCount As
How about:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'
If IsNull([PaymentID]) = True Then
Me.PaymentID.Visible = False
Me.PaymentAmt.Visible = False
Me.PaymentDate.Visible = False
Else
Me.PaymentID.Visible = True
Me.PaymentAmt.Visible = True
Me.PaymentDate.Visible = True
End If
End Sub

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"BarbS via AccessMonster.com" wrote:

On Sunday, December 13, 2009 7:46 AM
Douglas J. Steele wrote:

Or shorter:Private Sub Detail_Format(Cancel As Integer, FormatCount As
Or shorter:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'

Me.PaymentID.Visible = Not IsNull([PaymentID])
Me.PaymentAmt.Visible = Not IsNull([PaymentID])
Me.PaymentDate.Visible = Not IsNull([PaymentID])

End Sub


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

On Sunday, December 13, 2009 10:41 AM
Daniel Pineault wrote:

Douglas,If I understand properly (please correct me)it will evaluate the Is
Douglas,

If I understand properly (please correct me)

it will evaluate the Is Null() and then inverse the boolean to apply the
visible property.

so if PaymentID is null then is will return True and therefor the visible
property will be set to Not True (therefore False).

Always learning. Thank you for the clean and easy code! I am sure I will be
able to use this principle in many other places.
--
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Douglas J. Steele" wrote:

On Sunday, December 13, 2009 11:07 AM
Douglas J. Steele wrote:

Your understanding is correct, Daniel.
Your understanding is correct, Daniel.

Note that while it is shorter code, I do not know that it is necessarily any
more efficient. Not only that, but remember that others may be looking at
your code months or years later, and it is important that they be able to
figure it out too! For that reason, I usually put a comment in the code to
identify what is being done.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

On Sunday, December 13, 2009 2:48 PM
BarbS via AccessMonster.com wrote:

Thank you and the others for helping me try and figure this out.
Thank you and the others for helping me try and figure this out.
Obviously I 'm just learning the code, but I have tried all suggestions to no
avail. Maybe I am not clearly saying what I need to do. I need to suppress
the printing of three controls in a report when the PaymentID control value
is null. The three controls are as you have listed below, PaymentID,
PaymentAmt, and PaymentDate. I am entering this code in the Report Detail
section, Event, On Format and the three commands and their labels are still
showing on the report (null). Do you have any ideas of what I might be doing
wrong? Again, thank you for your help,

Daniel Pineault wrote:

--
Message posted via http://www.accessmonster.com

On Sunday, December 13, 2009 7:20 PM
John Spencer wrote:

You might need to hide the label also.What version of Access are you using?
You might need to hide the label also.

What version of Access are you using?
Are you sure the value of PaymentID is NULL?
Is it possible that it is a zero-length string?

You could use something like the following to handle Nulls, zero-length
strings, and strings that consist of multiple spaces.

Me.PaymentID.Visible = Len(Trim(Me.PaymentID & ""))0
Me.PaymentAmount.Visible = Len(Trim(Me.PaymentID & ""))0
Me.PaymentDate.Visible = Len(Trim(Me.PaymentID & ""))0

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

BarbS via AccessMonster.com wrote:

On Sunday, December 13, 2009 10:12 PM
BarbS via AccessMonster.com wrote:

Thank you John, for your reply. I am using MS Access 2007.
Thank you John, for your reply. I am using MS Access 2007. I believe the
PaymentID is NULL, but I tried your function just in case, that still did not
work. I also tried including all the labels, that did not work either. I am
lost at what to do next.

John Spencer wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1

On Monday, December 14, 2009 7:05 AM
KenSheridan via AccessMonster.com wrote:

One possibility is that the value of the PaymentID column is zero rather
One possibility is that the value of the PaymentID column is zero rather than
being Null. I think by default Access gives columns of number data type a
DefaultValue property of zero, so this could be the case if the column to
which the control in the report is bound is a foreign key in a table
referencing the primary key of a Payments table or similar. If the display
control is looking up the value from the referenced table then it will appear
empty as there is no row in the referenced table with key value of zero.

If this could explain the behaviour try testing for zero rather than Null:

Me.PaymentID.Visible = (Me.PaymentID 0)
Me.PaymentAmount.Visible = (Me.PaymentID 0)
etc.

Alternatively, if the above scenario is the case, bind the control to the
primary key of the referenced table (Payments), not the foreign key of the
referencing table. The query would need to use an outer join of course and
return the referenced primary key, not the foreign key which references it.
The control would then be Null if there is no matching row in the referenced
table, so testing for Null should work.

Ken Sheridan
Stafford, England

BarbS wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1

On Monday, December 14, 2009 10:24 AM
Al Campagna wrote:

Barb,I agree with Ken Sheridan.
Barb,
I agree with Ken Sheridan. The code we suggested is OK, so the
PaymentID may be something other than Null.
Is it Null ?
Is it "" ?
Is it 0 (zero)?

A trick I use in these situations is to set up the Format for the
PaymentID control...

If either a Positive or negative value = display normally (123.45
or -123.45)
If zero = display as .00
If Null = Display as "Null"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you will never work a day in your life."


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Reflection Effect
http://www.eggheadcafe.com/tutorials...on-effect.aspx
  #13  
Old April 19th, 2010, 04:25 PM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default If-Then-Else statements (hiding labels for empty fields)

Jake

Are you trying to do this in a report?

Be aware that there's a Can Grow and a Can Shrink property associated with
controls. If there's no value (i.e., Null) in a control, that control can
be made to shrink/disappear, using this property.

However, if you have labels that are NOT attached to their respective
controls (e.g., a label control not attached to "its" textbox control), the
labels won't shrink.

The simple solution is to attach your labels to their controls, so that the
Can Shrink property applies to both...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jake Davis wrote in message ...
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
Control Value Is Null

Me.PaymentID_Label.Visible = Not IsNull([PaymentID])
Me.PaymentAmt_Label.Visible = Not IsNull([PaymentID])
Me.PaymentDate_Label.Visible = Not IsNull([PaymentID])

End Sub

This is the solution for the rptCitationsAndPaymentsModified report to
hide the LABELS when there is no data in them for the associated Text Box
in the Tophill.accdb project.

We test PaymentID Text Box for a Null Value, meaning we have NO DATA
because the joker hasn't paid his or her ticket. When that check comes
back true, then we hide the LABELS not the text boxes themselves(because
they're already empty!). Thus when we do the print preview on the report,
it omits the three LABELS for Payment ID, Payment Amt, and Payment Date
because PaymentID text box has no data to display.

In theory you could take this even further, hiding ALL labels so you end
up with blank lines in the report between people who have actually paid
their ticket but then you are hiding text boxes (with data) AND labels.

I'm not claiming to be new or revolutionary, I just riffed on the code and
took a look at what we were hiding. It wasn't the TEXT BOX it was the
LABEL. So changing the VB code to reflect got me the desired resolution.
We didn't need to hide the text box because it was already empty, just
it's label.







Al Campagna wrote:

Barb,I agree with Ken Sheridan.
14-Dec-09

Barb,
I agree with Ken Sheridan. The code we suggested is OK, so the
PaymentID may be something other than Null.
Is it Null ?
Is it "" ?
Is it 0 (zero)?

A trick I use in these situations is to set up the Format for the
PaymentID control...

If either a Positive or negative value = display normally (123.45
or -123.45)
If zero = display as .00
If Null = Display as "Null"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you will never work a day in your life."

Previous Posts In This Thread:

On Saturday, December 12, 2009 4:24 PM
BarbS via AccessMonster.com wrote:

If-Then-Else statements
I am presently studying Access and having difficulty understanding
building
event codes. What I am trying to do is suppress the printing of three
controls in a report if they are Null. This is how I wrote the code, but
it
is not working. Can someone please help. Thank you,

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'
If IsNull([PaymentID]) Then
[PaymentID] , [PaymentAmt], [PaymentDate].Visible = False

Else: [PaymentID].Visible = True

End If
End Sub

--
Message posted via http://www.accessmonster.com

On Saturday, December 12, 2009 5:04 PM
Al Campagna wrote:

Barb,Check Help for syntax assistance on the For-Else-Then statement.
Barb,
Check Help for syntax assistance on the For-Else-Then statement.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([PaymentID]) Then
[PaymentID].Visible = False
[PaymentAmt].Visible = False
[PaymentDate].Visible = False
Else
[PaymentID].Visible = True
[PaymentAmt].Visible = True
[PaymentDate].Visible = True
End If
End Sub

If you do not make Amt and Date visible again, in the Else statement,
they will never be visible again during that report printout.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you will never work a day in your life."

On Saturday, December 12, 2009 5:45 PM
Daniel Pineault wrote:

How about:Private Sub Detail_Format(Cancel As Integer, FormatCount As
How about:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'
If IsNull([PaymentID]) = True Then
Me.PaymentID.Visible = False
Me.PaymentAmt.Visible = False
Me.PaymentDate.Visible = False
Else
Me.PaymentID.Visible = True
Me.PaymentAmt.Visible = True
Me.PaymentDate.Visible = True
End If
End Sub

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"BarbS via AccessMonster.com" wrote:

On Sunday, December 13, 2009 7:46 AM
Douglas J. Steele wrote:

Or shorter:Private Sub Detail_Format(Cancel As Integer, FormatCount As
Or shorter:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'

Me.PaymentID.Visible = Not IsNull([PaymentID])
Me.PaymentAmt.Visible = Not IsNull([PaymentID])
Me.PaymentDate.Visible = Not IsNull([PaymentID])

End Sub


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

On Sunday, December 13, 2009 10:41 AM
Daniel Pineault wrote:

Douglas,If I understand properly (please correct me)it will evaluate the
Is
Douglas,

If I understand properly (please correct me)

it will evaluate the Is Null() and then inverse the boolean to apply the
visible property.

so if PaymentID is null then is will return True and therefor the visible
property will be set to Not True (therefore False).

Always learning. Thank you for the clean and easy code! I am sure I will
be
able to use this principle in many other places.
--
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Douglas J. Steele" wrote:

On Sunday, December 13, 2009 11:07 AM
Douglas J. Steele wrote:

Your understanding is correct, Daniel.
Your understanding is correct, Daniel.

Note that while it is shorter code, I do not know that it is necessarily
any
more efficient. Not only that, but remember that others may be looking at
your code months or years later, and it is important that they be able to
figure it out too! For that reason, I usually put a comment in the code to
identify what is being done.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

On Sunday, December 13, 2009 2:48 PM
BarbS via AccessMonster.com wrote:

Thank you and the others for helping me try and figure this out.
Thank you and the others for helping me try and figure this out.
Obviously I 'm just learning the code, but I have tried all suggestions to
no
avail. Maybe I am not clearly saying what I need to do. I need to
suppress
the printing of three controls in a report when the PaymentID control
value
is null. The three controls are as you have listed below, PaymentID,
PaymentAmt, and PaymentDate. I am entering this code in the Report Detail
section, Event, On Format and the three commands and their labels are
still
showing on the report (null). Do you have any ideas of what I might be
doing
wrong? Again, thank you for your help,

Daniel Pineault wrote:

--
Message posted via http://www.accessmonster.com

On Sunday, December 13, 2009 7:20 PM
John Spencer wrote:

You might need to hide the label also.What version of Access are you
using?
You might need to hide the label also.

What version of Access are you using?
Are you sure the value of PaymentID is NULL?
Is it possible that it is a zero-length string?

You could use something like the following to handle Nulls, zero-length
strings, and strings that consist of multiple spaces.

Me.PaymentID.Visible = Len(Trim(Me.PaymentID & ""))0
Me.PaymentAmount.Visible = Len(Trim(Me.PaymentID & ""))0
Me.PaymentDate.Visible = Len(Trim(Me.PaymentID & ""))0

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

BarbS via AccessMonster.com wrote:

On Sunday, December 13, 2009 10:12 PM
BarbS via AccessMonster.com wrote:

Thank you John, for your reply. I am using MS Access 2007.
Thank you John, for your reply. I am using MS Access 2007. I believe the
PaymentID is NULL, but I tried your function just in case, that still did
not
work. I also tried including all the labels, that did not work either. I
am
lost at what to do next.

John Spencer wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1

On Monday, December 14, 2009 7:05 AM
KenSheridan via AccessMonster.com wrote:

One possibility is that the value of the PaymentID column is zero rather
One possibility is that the value of the PaymentID column is zero rather
than
being Null. I think by default Access gives columns of number data type a
DefaultValue property of zero, so this could be the case if the column to
which the control in the report is bound is a foreign key in a table
referencing the primary key of a Payments table or similar. If the
display
control is looking up the value from the referenced table then it will
appear
empty as there is no row in the referenced table with key value of zero.

If this could explain the behaviour try testing for zero rather than Null:

Me.PaymentID.Visible = (Me.PaymentID 0)
Me.PaymentAmount.Visible = (Me.PaymentID 0)
etc.

Alternatively, if the above scenario is the case, bind the control to the
primary key of the referenced table (Payments), not the foreign key of the
referencing table. The query would need to use an outer join of course
and
return the referenced primary key, not the foreign key which references
it.
The control would then be Null if there is no matching row in the
referenced
table, so testing for Null should work.

Ken Sheridan
Stafford, England

BarbS wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200912/1

On Monday, December 14, 2009 10:24 AM
Al Campagna wrote:

Barb,I agree with Ken Sheridan.
Barb,
I agree with Ken Sheridan. The code we suggested is OK, so the
PaymentID may be something other than Null.
Is it Null ?
Is it "" ?
Is it 0 (zero)?

A trick I use in these situations is to set up the Format for the
PaymentID control...

If either a Positive or negative value = display normally (123.45
or -123.45)
If zero = display as .00
If Null = Display as "Null"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you will never work a day in your life."


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Reflection Effect
http://www.eggheadcafe.com/tutorials...on-effect.aspx



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.