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 » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Problem with If.... Then.... Else.... Statement



 
 
Thread Tools Display Modes
  #1  
Old July 30th, 2007, 03:26 PM posted to microsoft.public.access.reports
Richard
external usenet poster
 
Posts: 1,419
Default Problem with If.... Then.... Else.... Statement

Hi

Trying to run a report which will look at a field and if a persons name
appears in that field the their signature will be visible on the report, if
it is not there name then another person signature will appear.
I have put the if statement below on the reports Details section in the On
Format event.
Every time I run the report I get the error

You have entered an expression that has no value.

when I run the de-bugger the line with the employee name (M McManus) is
highlighted in yellow.

The first part of the code (IsNull) is that sometimes the report can be run
with no data to show and I don't want the code to fall over.

Code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Reports![Certificate Details]![AnalystsName]) Then
Image107.Visible = True
Label105.Visible = True
Label106.Visible = True
Else
If [AnalystsName] = "M McManus" Then
Image107.Visible = True
Label105.Visible = True
Label106.Visible = True
Else
Image109.Visible = True
Label55.Visible = True
Label56.Visible = True
End If
End If
End Sub

any help greatfully received

Richard
  #2  
Old July 30th, 2007, 03:37 PM posted to microsoft.public.access.reports
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Problem with If.... Then.... Else.... Statement

Richard

Another approach might be to do the comparison in a query underlying your
report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"richard" wrote in message
...
Hi

Trying to run a report which will look at a field and if a persons name
appears in that field the their signature will be visible on the report,
if
it is not there name then another person signature will appear.
I have put the if statement below on the reports Details section in the On
Format event.
Every time I run the report I get the error

You have entered an expression that has no value.

when I run the de-bugger the line with the employee name (M McManus) is
highlighted in yellow.

The first part of the code (IsNull) is that sometimes the report can be
run
with no data to show and I don't want the code to fall over.

Code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Reports![Certificate Details]![AnalystsName]) Then
Image107.Visible = True
Label105.Visible = True
Label106.Visible = True
Else
If [AnalystsName] = "M McManus" Then
Image107.Visible = True
Label105.Visible = True
Label106.Visible = True
Else
Image109.Visible = True
Label55.Visible = True
Label56.Visible = True
End If
End If
End Sub

any help greatfully received

Richard



  #3  
Old July 30th, 2007, 04:10 PM posted to microsoft.public.access.reports
Richard
external usenet poster
 
Posts: 1,419
Default Problem with If.... Then.... Else.... Statement

Jeff

If I understand you right would this be as follows

Have the signatures stored as objects in a table??? (the signatures are a
JPEG file stored on the server. Not sure how to do this but will go away and
have a look) and then pull this object into the query based upon criteria for
the Analysts Name. This field can then be put into the report straight from
the query.


Thanks

Richard
"Jeff Boyce" wrote:

Richard

Another approach might be to do the comparison in a query underlying your
report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


  #4  
Old July 30th, 2007, 04:33 PM posted to microsoft.public.access.reports
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Problem with If.... Then.... Else.... Statement

No, that wasn't what I was suggesting. In the code you were comparing to a
string. Why not do that comparison in a query before getting to the report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"richard" wrote in message
...
Jeff

If I understand you right would this be as follows

Have the signatures stored as objects in a table??? (the signatures are a
JPEG file stored on the server. Not sure how to do this but will go away
and
have a look) and then pull this object into the query based upon criteria
for
the Analysts Name. This field can then be put into the report straight
from
the query.


Thanks

Richard
"Jeff Boyce" wrote:

Richard

Another approach might be to do the comparison in a query underlying your
report.

Regards

Jeff Boyce
Microsoft Office/Access MVP




  #5  
Old July 30th, 2007, 04:55 PM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 22
Default Problem with If.... Then.... Else.... Statement

Hi Richard
I've done something similar using the following
I have JPGsfile stored on my hard drive (of a photos of users) and the
path is in a table in the DB. Also in the folder is a default picture
where I don't have a photo for the user called NoPic.jpg

e.g. The table looks like this:
Name PictureLoc
Joe Bloggs c:/User Photos/JBloggs.jpg

I then have a textbox called PhotoLoc bound to the location in the
form. I've made it invisible so it won't appear on the form, but the
data is still there.
I also have a picture on form in the Details section. It can be
anything as it will be replaced when the form is rendered. I recommend
using the NoPic.jpg. Make sure it's the correct size for your report.
You can set the Size Mode property to stretch or clip depending on how
you want your images to look. I've named the picture PhotoFrame

Then I have the following code for the form:

'----------------------------------------
Function setPhotoPath()

Dim PhotoPath As String
On Error GoTo PhotoNotAvailable
PhotoPath = Me.PhotoLoc
Me.PhotoFrame.Picture = strPhotoPath

PhotoPictureNotAvailable:
PhotoPath = "C:\User Photos\NoPic.jpg"
Me.BoxFrame.Picture = PhotoPath

End Function
'--------------------------------------------

Finally, I have "=setPhotoPath()" (without quotation marks) in the On
Format section of the properties of the report details.

When I preview the report, the query is run to bring up the location
of the jpeg and the code replaces the picture.

Hope this helps. If you want an example DB, I can send you one.

Tony

  #6  
Old July 30th, 2007, 05:35 PM posted to microsoft.public.access.reports
[email protected]
external usenet poster
 
Posts: 22
Default Problem with If.... Then.... Else.... Statement

Hello Richard
Sorry, I've just ran that code and it looks dodgy. The code should
read:

Function setPhotoPath()

Dim PhotoPath As String
On Error GoTo PhotoNotAvailable
PhotoPath = Me.PhotoLoc
Me.PhotoFrame.Picture = PhotoPath
GoTo PhotoEnd

PhotoNotAvailable:
PhotoPath = "C:\Users Photos\nopic.jpg"
Me.PhotoFrame.Picture = PhotoPath

PhotoEnd:
End Function




  #7  
Old July 30th, 2007, 07:58 PM posted to microsoft.public.access.reports
Richard
external usenet poster
 
Posts: 1,419
Default Problem with If.... Then.... Else.... Statement

sorry Jeff

I have no idea how to go about what you are suggesting.

Richard

"Jeff Boyce" wrote:

No, that wasn't what I was suggesting. In the code you were comparing to a
string. Why not do that comparison in a query before getting to the report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"richard" wrote in message
...
Jeff

If I understand you right would this be as follows

Have the signatures stored as objects in a table??? (the signatures are a
JPEG file stored on the server. Not sure how to do this but will go away
and
have a look) and then pull this object into the query based upon criteria
for
the Analysts Name. This field can then be put into the report straight
from
the query.


Thanks

Richard
"Jeff Boyce" wrote:

Richard

Another approach might be to do the comparison in a query underlying your
report.

Regards

Jeff Boyce
Microsoft Office/Access MVP





  #8  
Old July 30th, 2007, 11:16 PM posted to microsoft.public.access.reports
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Problem with If.... Then.... Else.... Statement

Richard

Your data must be coming from somewhere, I assume a table.

I also assume you built your report directly on the table.

My recommendation was to first create a query that returns the data you wish
to have in your report, then base the report on that query.

In your query, you could use the IIF() function to test for "M McManus",
generating a True/False. Then, in your report, use the True/False to set
the visible property of the control.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"richard" wrote in message
...
sorry Jeff

I have no idea how to go about what you are suggesting.

Richard

"Jeff Boyce" wrote:

No, that wasn't what I was suggesting. In the code you were comparing to
a
string. Why not do that comparison in a query before getting to the
report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"richard" wrote in message
...
Jeff

If I understand you right would this be as follows

Have the signatures stored as objects in a table??? (the signatures are
a
JPEG file stored on the server. Not sure how to do this but will go
away
and
have a look) and then pull this object into the query based upon
criteria
for
the Analysts Name. This field can then be put into the report straight
from
the query.


Thanks

Richard
"Jeff Boyce" wrote:

Richard

Another approach might be to do the comparison in a query underlying
your
report.

Regards

Jeff Boyce
Microsoft Office/Access MVP







 




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