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

[Guest]

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

 

[Jeff Boyce]

Richard

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

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]

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

 

[a.t.brooks]

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

 

[a.t.brooks]

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

 

[Guest]

sorry Jeff

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

Richard

 

[Jeff Boyce]

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