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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|