Very Specific Report Field

[Kila]

I have a report field that is currently displaying:
=Trim([LastName] & " " & [Suffix] & ", " & [FirstName] & " " & [MiddleName] & ", " & [Degree])

For example

Doe Jr., John Ralph, MD
or

Doe , Jane Ellen, MD

I would like it to do a few things more based on those fields & others in the query if anyone has suggestions:

1. Get rid of the extra space after the last name if there is no Suffix (Jr., III, etc.)

2. Display the Contents of the field [IMGStatus] in parentheses ONLY if not Null...For example
Doe, Jane Ellen, MD (IMG)

3. If the contents of the field [PayAbbreviation] ="VAP", put an asterick next to the displayed name and display the whole row in the report (there are other displayed fields not in the example) in bold and italics...For example
*Doe, Jane Ellen, MD (IMG)

Thanks for any help you can offer...

 

[RuralGuy]

Have you tried using VBA in the DetailFormat event? There is hardly anything that *can't* be done with a control on your report from there.

 

[Kila]

No, I am willing to, but I am relatively new to VBA and VERY clumsy with writing it from scratch.

 

[RuralGuy]

Start by putting an *unbound* textbox on your report and let's name it txtPlay. Then right click on the Detail bar on the report and select properties. On the event tab of this property sheet press the "..." (build button) next to the On Format event and select code. In this event put:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.txtPlay = Trim([LastName] & " " & [Suffix] & ", " & [FirstName] & " " & [MiddleName] & ", " & [Degree])

End Sub

Then run the report and tell me what happens.

 

[Kila]

OK, it displayed the exact same text I had in [txtName].

Thanks...This is a GREAT start, I never would have thought of putting it in an unbound box. I would have spent 2 hours playing around with the "On Open" or "On Load" events on the [txtName field]

But my next question is, how do you put a series of If...Then...Else statements on the various parts of the displayed text in a coherent, consistent display?

 

[Kila]

I have been playing with this trick for a slightly different purpose (I will come back to the other as soon as I solve this problem) & have come across an strange phenomenon...

Why would [Date] work in

Me.txtRecAction = Trim([Date])

But [Date]...and [ActionComment] and [RRBText] do not work here....

Me.txtRecAction = IIf([Action] Like "Enter*", ([Date]), (IIf([Action] Like "Terminate - Transfer", ([ActionComment]), ([RRBText]))))

It does not seem to recognize these fields even though they are fields in the underlying query, but it DOES recognize them in the 1st statement above. What is the problem here?

Thanks!

 

[Kila]

I have been playing with these If...Then...Else Statements all day for the above question & sort of answered that one, but I am looking for the answer to the Name problem. How can I write a Trim AND/OR If...Then...Else Statement to accommodate any combination of:

LastName, Firstname MiddleName, Suffix, Title (IMG)

Where
1. The MiddleName may or may not be present (only need a space if it IS)
2. The Sufffix may or may not be present (only need a comma and a space if it IS)
3. The IMG status only needs to be displayed...in parentheses...if it is not null
4. The whole thing needs to be bold & have a * next to it only if PayStatus=VAP

I would like to do this without having to write an If...Then...Else statement for every possible comination of the 4 things above. Hmmmm. I have been pondering this all day.

Thanks for any suggestions.

 

[pbaldy]

Use that fact that the "+" concatenation operator will propagate nulls, while the "&" operator will not. Untested, but should be close:

Me.UnboundTextBox = LastName & ", " & FirstName & ( " " + MiddleName) & (", " + Suffix) & ", " & Title & ("(" + IMG + ")")

The bold thing you'll have to do in code, but it's simple.

 

[RuralGuy]

Sorry, I've been gone most of the day. I would build the string with a series of
If Not IsNull([MiddleName]) Then
StrName = StrName & [Middlename]
Else
StrName = StrName & " "
End If

Date is a reserved word and can really confuse Access when you don't use it properly. Here's some links you might want to bookmark:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[RuralGuy]

Hi Kila,
pbaldy is correct and the suggestion is a good one that I've used before. I would document it well so two years from now someone else can understand what the Null propagation technique accomplishes. Otherwise, I don't think speed is a issue here so a sequence of If...Else...EndIf statements would be easier to maintain.

 

[Kila]

Thank you so much everyone! I finally decided on a bit of a combination, since the IMG field has a few diffferent types of data in it, plus conditional formatting for the bold. This works just great!

If IMGStatus Like "IMG" Then
'Types IMG names in Name field, only showing exra spaces & commas if MiddleName & Suffix are not Null
    If txtPayStatus Like "VA Paid" Then
        Me.txtName = "* " & LastName & ", " & FirstName & (" " + MiddleName) & (", " + Suffix) & ", " & Degree & (" (" + IMGStatus + ")")
    Else
        Me.txtName = LastName & ", " & FirstName & (" " + MiddleName) & (", " + Suffix) & ", " & Degree & (" (" + IMGStatus + ")")
    End If
    
ElseIf IMG Like "US IMG" Then
    'Types US IMG names in Name field, only showing exra spaces & commas if MiddleName & Suffix are not Null
    If txtPayStatus Like "VA Paid" Then
        Me.txtName = "* " & LastName & ", " & FirstName & (" " + MiddleName) & (", " + Suffix) & ", " & Degree & (" (" + IMGStatus + ")")
    Else
        Me.txtName = LastName & ", " & FirstName & (" " + MiddleName) & (", " + Suffix) & ", " & Degree & (" (" + IMGStatus + ")")
    End If
    
Else
   'Types Non-IMG names in Name field, only showing exra spaces & commas if MiddleName & Suffix are not Null
    If txtPayStatus Like "VA Paid" Then
        Me.txtName = "* " & LastName & ", " & FirstName & (" " + MiddleName) & (", " + Suffix) & ", " & Degree
    Else
        Me.txtName = LastName & ", " & FirstName & (" " + MiddleName) & (", " + Suffix) & ", " & Degree
    End If
End If

I put some documentation about the extra spaces just in case, but if anyone got as far as that Code window to fix something, and knew what to do, I would be hugely amazed. Access is not my primary job, but it is something I have taught myself to do because it is very useful & powerful. As far as I know, very few people at our hospital use Access at all, much less the VBA part. The one IT person I know of who know VBA has since moved on.

My goal is to write this database so that it can can be used for years without tech support, because if I leave here, there is none for Access. I am sure this goal is fruitless...someone always wants to change something...and PRAY the thing does not crash!!! If that happens, they will probably call me wherever I am!

At any rate, after making the above changes, I tried to delete the old control that the unbound box replaced. Whenever I delete the control (txtName2) or the text in it's Control Source:
=Trim([LastName] & ", " & [FirstName] & " " & [MiddleName] & ", " & [Suffix] & ", " & [Degree])

I get the following error, even though the Code does not reference the control...

Microsoft Access can't find the field "LastName" referenced in your expression.

I get this message even if I leave the control there, but delete the text in the Control Source. Why might this be? Leaving it doesn't hurt anything, but just in case someone DOES try to edit the thing then I leave, I am trying to get rid of unused fields. Just as an FYI, changing the Name of the control does not seem to matter.

 

[RuralGuy]

How about trying a /decompile? http://www.granite.ab.ca/access/decompile.htm

 

[Kila]

WOW! That sure sped it WAAAY up! I have not yet made it to the problem above. On the Debug, I got the following message:

Compile Error: Variable not defined

for this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.RecordUpdated = Now()
  'tblNameList.txtModifiedBy = Environ("UserName")
End Sub


Private Sub subfrmActions_Exit(Cancel As Integer)
 [COLOR="Red"]tblNameList[/COLOR].RecordUpdated = Now()
 tblNameList.ModifiedBy = Environ("UserName")
End Sub


Private Sub subfrmRoster_Exit(Cancel As Integer)
 tblNameList.RecordUpdated = Now()
 tblNameList.ModifiedBy = Environ("UserName")
End Sub

How do I fix this? I wrote this portion a very long time ago in an earlier version of Access (97, I think). If I remember right, this was a way for a subform to notate the primary form's table with a modified date every time the subform was modified. If I remember right, I just could not get the subform to make this notation to the form, so I finally got it to notate the underlying table instead. It worked at the time, but I have not idea if it still does since I have not really been paying attention to it. How can I fix the error message? OR, is there a better way to accomplish this?

 

[Kila]

By the way, I forgot to mention before that I DID go back & take a few hours to replace all references to "Date" with "DateStart". Thanks for the suggestion.

 

[RuralGuy]

If tblNameList is the table to which the form is bound then you reference the field with [RecordUpdated]. Your code should be in the BeforeUpdate event of the form, not the exit event. I think you can also use tblNameList!ModifiedBy but you have to use a bang "!" and not a dot ".".

 

[Kila]

Thanks. Taking out the...

tblNameList.

...part seemed to do the trick. I was able to compile it after a few more corrections.

Now...back to solve the original problem!

 

[RuralGuy]

Still can't delete the original control?

 

[Kila]

NOW I get:

Microsoft Office Access cannot find the field 'IMGStatus' referred to in your expression

.

I get this only if I delete the txtName2 field, or the part in it referencing IMGStatus. If I just delete the LastName part of the text in the control, it looks for that.

It doesn't seem to hurt anything if it is there. I can reduce the box almost to nothing, make it invisible & stick it in a corner, but it's just weird. I'm trying to do this thing right. I have a bunch of these invisible boxes I would like to get rid of. The code is referencing them b/c for whatever reason it will not work if the code references the underlying query.

There is no code referencing this txtName2 field though. Ah, well, at least it WORKS!

 

[RuralGuy]

Access has a *nasty* habit of naming a control the same name as the field to which it is bound. Access then get confused when you try to reference it. Change the name of the controls and the Access can differentiate between controls and fields. I like to preface each control with the type of control: txt for TextBox, cbo for ComboBox, etc.

 

[Kila]

I discovered that habit awhile back. Since someone on this website taught me about normalizing & naming conventions a few databases ago, I change the names of all the controls I reference to txtFieldName to minimize the confusion. I think I missed the "Date" thing here because that particular table was designed before I learned that. (The report I am working on now is a new report in an old database) Sometimes if I have time I go back & change ALL the control names that the Wizard created, even if I do not reference them specifically.

BUT for some of these controls in this case, the code only works if I reference the control in the report (txtFieldName) and not the field in the query itself(FieldName). What's the difference?

And on a side note, why has Bill Gates not set up the Wizards to name the control names this way, and disabled the ability to put spaces in names. It is a pain to change them all, and sets up the new user to have trouble, especially when said user finally ventures into VBA and discovers the need to correct the mess on an old database to make the code work properly. I never would have learned the right way to do things if I had not stumbled into this website.