Modify Control Source Expression for change in field type

[MRenee]

I'm using the access template for Issue Tracking and making small modifications for my needs. One thing I did is change the ID field type from a number to a text and when I did that the below expression in one of the forms will no longer work:

=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

How can I modify this so it works with the "ID" field being text now instead of a number?

[twinnyfo]

MRenee,

First, Welcome to Bytes!

MRenee:
One thing I did is change the ID field type from a number to a text

You should never change a Field Data Type after there is data in it (especially a field named "ID").

MRenee:
when I did that the below expression in one of the forms will no longer work:

=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

This is no surprise.

I can hepp you get it to work this way:

=ColumnHistory([RecordSource],"Comments","[ID]='" & Nz([ID],0) & "'")

However, this does not address the issues you are having with why you would need to change an ID field to text. This is simply a poor design change, and one that will probably produce more challenging problems yonder down the road.

Hope this hepps!

[MRenee]

Thanks I'll give that a shot.

[twinnyfo]

Let us know how things turn out.

[MRenee]

That worked, thank you so much!!

[twinnyfo]

Glad we could be of service. Let us know if you need any more hepp!

[MRenee]

Ok, so that did break other things in the form that I didn't think would be affected by this code. So let me expand on what I'm wanting to do.
I changed the data type of the ID field because I want to be able to enter the ID in this manner 2019-01, 2019-02, 2019-03. The expression in the form tracks the history of comments that you enter in a comment box and I want to maintain that functionality. So how can I retain the comment history tracking function but also have the ID in the format that I want?

[twinnyfo]

I would recommend maintaining the ID as a numerical value (an auto-incrementing long integer) and have a separate field for the "CommentID" which have whatever format you design.

Some additional thoughts: if you are simply recording the number of comments per year (as it appears), what happens if you exceed 99 comments? In this case, simply having a date field that records the Date/Time of the comment is all you need. Then, simply list all the comments from 2019, for example.

I recognize that there may be much more going on than what that simplistic response may address. However, the key principle I would like you to embrace is that if you have an ID, the best way to do it is with an auto-incrementing field. You can design your CommentID or CustomerID, etc., however you wish. But the unique identifier for the record should be the simplest, easiest to manage. There are countless advantages to this method.

Hope this hepps!

Standing by to respond to any additional thoughts on this.

[NeoPa]

MRenee:
So let me expand on what I'm wanting to do.

Please don't. That isn't how this site is best used nor is it compatible with our rules.

What you should do is post a separate question, optionally with a link back to this one if you believe it may help (or even hepp ;-)).

Generally speaking long and multi-problem threads are complicated and hard to follow. This causes them to be of little value to all those out there with similar problems. So far we have a thread with a straightforward question and a matching answer post selected. That's great. Continuing to use this thread for other questions merely muddies the waters and leaves much valuable intelligence hard to find or discern.