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
  #1  
Old December 12th, 2009, 10:24 PM posted to microsoft.public.access.gettingstarted
BarbS via AccessMonster.com
external usenet poster
 
Posts: 11
Default If-Then-Else statements

I'm presently studying Access and having difficulty understanding building
event codes. What I'm 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

  #2  
Old December 12th, 2009, 11:04 PM posted to microsoft.public.access.gettingstarted
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default If-Then-Else statements

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 don't 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'll never work a day in your life."


"BarbS via AccessMonster.com" u36617@uwe wrote in message
news:a07ebc3e9801f@uwe...
I'm presently studying Access and having difficulty understanding building
event codes. What I'm 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



  #3  
Old December 12th, 2009, 11:45 PM posted to microsoft.public.access.gettingstarted
Daniel Pineault
external usenet poster
 
Posts: 658
Default If-Then-Else statements

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:

I'm presently studying Access and having difficulty understanding building
event codes. What I'm 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

.

  #4  
Old December 13th, 2009, 01:46 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default If-Then-Else statements

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!)



"Daniel Pineault" wrote in
message ...
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:

I'm presently studying Access and having difficulty understanding
building
event codes. What I'm 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

.


  #5  
Old December 13th, 2009, 04:41 PM posted to microsoft.public.access.gettingstarted
Daniel Pineault
external usenet poster
 
Posts: 658
Default If-Then-Else statements

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'm 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:

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!)



"Daniel Pineault" wrote in
message ...
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:

I'm presently studying Access and having difficulty understanding
building
event codes. What I'm 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

.


.

  #6  
Old December 13th, 2009, 05:07 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default If-Then-Else statements

Your understanding is correct, Daniel.

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

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


"Daniel Pineault" wrote in
message ...
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'm 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:

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!)



"Daniel Pineault" wrote in
message ...
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:

I'm presently studying Access and having difficulty understanding
building
event codes. What I'm 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

.


.



  #7  
Old December 13th, 2009, 08:48 PM posted to microsoft.public.access.gettingstarted
BarbS via AccessMonster.com
external usenet poster
 
Posts: 11
Default If-Then-Else statements

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
avail. Maybe I'm 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'm 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:
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

I'm presently studying Access and having difficulty understanding building
event codes. What I'm trying to do is suppress the printing of three

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


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

  #8  
Old December 14th, 2009, 01:20 AM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default If-Then-Else statements

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:
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
avail. Maybe I'm 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'm 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:
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

I'm presently studying Access and having difficulty understanding building
event codes. What I'm trying to do is suppress the printing of three

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


  #9  
Old December 14th, 2009, 04:12 AM posted to microsoft.public.access.gettingstarted
BarbS via AccessMonster.com
external usenet poster
 
Posts: 11
Default If-Then-Else statements

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

  #10  
Old December 14th, 2009, 01:05 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default If-Then-Else statements

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:
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.

You might need to hide the label also.

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


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

 




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 09:32 PM.


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