By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Modify Control Source Expression for change in field type

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. =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?
1 Week Ago #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))
This is no surprise.

I can hepp you get it to work this way:
Expand|Select|Wrap|Line Numbers
  1. =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!

Share this Question
Share on Google+
8 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,328
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:
Expand|Select|Wrap|Line Numbers
  1. =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))
This is no surprise.

I can hepp you get it to work this way:
Expand|Select|Wrap|Line Numbers
  1. =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!
1 Week Ago #2

P: 4
Thanks I'll give that a shot.
1 Week Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,328
Let us know how things turn out.
1 Week Ago #4

P: 4
That worked, thank you so much!!
1 Week Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,328
Glad we could be of service. Let us know if you need any more hepp!
1 Week Ago #6

P: 4
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?
1 Week Ago #7

twinnyfo
Expert Mod 2.5K+
P: 3,328
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.
1 Week Ago #8

NeoPa
Expert Mod 15k+
P: 31,606
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.
1 Week Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.