Comment on Null Field

[NDD]

I have a field
SSEEndDate
I have some records in which this field is empty. What I would like to do is add a comment to explain why the field is empty on a report if possible. Click on it or move the mouse on it or whatever and the comment would show up.

If this possible?

 

[BlueIshDan]

Private Sub txtSSEEndDate_OnClick()
If IsNull(txtSSEEndDate.Value) Then: MsgBox "msg"
End Sub

 

[BlueIshDan]

OH wait, it's a report lol. well idk then

 

[BlueIshDan]

Tested it, it works

Use IsNull for dates and

Len(txtField.Value & vbNullString) > 0 for other values.

 

[GinaWhipp]

Hmm, are you going to enter the reason the date is absent somewhere that is in a table included in the Reports RecordSource?

 

[NDD]

Hmm, are you going to enter the reason the date is absent somewhere that is in a table included in the Reports RecordSource?

I wasn't planning to. The reason is the same for all of them. I just don't want to have to explain to the auditor.

 

[NDD]

Private Sub txtSSEEndDate_OnClick()
If IsNull(txtSSEEndDate.Value) Then: MsgBox "msg"
End Sub

Thanks Dan. I'll try it out.

 

[NDD]

Dan,
I'm not sure where to put the code.

 

[NDD]

And I put the text of my message between the quotes where you have "msg", right?

 

[NDD]

I'm trying to do this on a text box with a short date format.

 

[GinaWhipp]

@NDD.

That code is not for a Report.

Hmm, I just got an idea that might work, post the SQL of the query and let me know what the name of the Date field is and what you want the message to say. Remember, it's a date field, so let's not make the message a mile long.

 

[NDD]

@NDD.

That code is not for a Report.

Hmm, I just got an idea that might work, post the SQL of the query and let me know what the name of the Date field is and what you want the message to say. Remember, it's a date field, so let's not make the message a mile long.

Gina,
I don't know what the SQL of the query is. I have the query open.

 

[NDD]

My query is named

ALLPersonnelQuery

 

[GinaWhipp]

Okay, while in Design View of the query look to the upper left hand corner of the Ribbon/Toolbar. From the drop down select SQL View and then copy/paste what you see there here.

 

[NDD]

The Date Field is SSEEndDate

I would like the msg to say "In Prior to SSE"

 

[NDD]

My query SQL was here and I learned something today.

 

[NDD]

Good Lord! Is that normal?

 

[GinaWhipp]

Okay, just need the SQL of the query now... I posted on how to get that.

 

[NDD]

That's it in #16

 

[GinaWhipp]

Hmm, I'm going to leave that query alone though I will mentions one word...

Subreport

Okay, now moving on the how to get this work...

Step 1

IMPORTANT! Make a copy of your query so you always have a back up in case you run into a problem.

Go back to SQL View and paste the red part from below where I have it into your view. Once you have confirmed all is working you can delete the copy of the query.

SELECT Personnel.ID, Personnel.LastName, Personnel.FirstName, Personnel.Classification, Personnel.Field, Personnel.Status, Personnel.Group, Personnel.PlantStatus, Personnel.WorkGroup, Personnel.Code, Personnel.CellPhone, Personnel.PersonnelEmail, Personnel.WorkEmail, Personnel.SSE, Personnel.SSEEndDate, IIf(IsNull([SSEEndDate]),"In Prior to SSE","") AS MissingDate, etc...

Step 2

IMPORTANT! Make a copy of your report so you always have a back up in case you run into a problem.

While your report is in Design View add the field MissingDate from the Field List to your Report and place it on top of the SSEEndDate field. Expand wide enough to show your message and bold or change Font color if you like. Run to make sure all is right in the world. Once done you can delete the copy of your report.

You should now have what you want and I'm going to get some coffee.