Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Create a new field from an autonumber and a text field 1
- Thread starter lewie
- Start date
Why convert it to a string? Check out the Format command in help.
If you tell us specifically what you're trying to combine, and the format you want it to end up in, it will be quite easy for us to tell you how to go about it.
And, yes, you should _definitely_ do this in a query--this concatenated value should _not_ be stored in a table.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Affordable Development, Professionally Done
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
If you tell us specifically what you're trying to combine, and the format you want it to end up in, it will be quite easy for us to tell you how to go about it.
And, yes, you should _definitely_ do this in a query--this concatenated value should _not_ be stored in a table.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Affordable Development, Professionally Done
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Thread starter
- #3
Lewie,
Well, assuming you'll never get to 1000 in the autonumber field (remember you use an autonumber every time you start to create a record, whether that record is used, deleted, or abandoned before it is fully created), just put this in one of the columns of a query based on that table:
NewID: [type] & format([AutoID],"000"
Also, you should change the name of the field "Type", as that is almost sure to be an access reserved word, and it will cause problems for you at some point.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Professional Development for Clients Large and Small
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
Well, assuming you'll never get to 1000 in the autonumber field (remember you use an autonumber every time you start to create a record, whether that record is used, deleted, or abandoned before it is fully created), just put this in one of the columns of a query based on that table:
NewID: [type] & format([AutoID],"000"
Also, you should change the name of the field "Type", as that is almost sure to be an access reserved word, and it will cause problems for you at some point.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Professional Development for Clients Large and Small
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
-
1
- #5
You should never depend on any particular value being in an autonum. Better to make your own numeric column, and take care of incrementing it yourself (gee, I wish Access had triggered procedures). Good advice about not storing the concatenated value. The way you are attempting to use the Autonum is a misuse of the feature.
Peleg
PelegNOSPAM@PStrauss.net
Peleg
PelegNOSPAM@PStrauss.net
- Thread starter
- #6
When data is entered into the form the autonumber gets installed automatically and it's always unique. I am not using any modules and am not experienced at coding yet so this would seem to be the best way. What is the specified purpose of auto numb.
Thanks.
Lewie
Thanks.
Lewie
That's a good question. People who don't understand the Relational Model think they make grand Primary Keys(PK). In fact, using them as PKs breaks the model and you loose most of the advantages of having a Relational Database in the first place. OK, so Access isn't 100% relational, I know, but it has some of the features of one, and you loose a lot of those features when you break the model.
Other people think they are a neat way to generate things like part numbers, etc. Not hardly. Since you really can't control the value an autonum gets, this seems to be problem.
So, what's left? Right, not much. I use them for one thing, and only one thing, and I admit it is a bit of a cheat. Consider a PK that is the concatenation of several columms, especially text columns. Now, write a few Joins. Write a where-clause with the PK. Messy. I use autonum in order to have a single column to use in Joins and where-clauses. I never show the column to the user on any form, and I never even mention to them that it is even there. It is indexed, of course, but is *not* the PK. I never make use of the value, I don't ever care what the actual value is, except that it is unique. I don't do any calculations on it, as that wouldn't make any sense since I assume I don't know the value in the first place. I act as if it where some random or non-displayable value instead of some long integer.
I've tried not using autonums in this kind of situation, and I ended up going back to using them because it just makes my life so much simpler. As long as they are not my PK, I don't see how it breaks the model.
Now, when the PK is exactly one column, I don't use autonums as there is no advantage to it at all in this case.
No coding is required to choose a proper PK. What is required is a good understanding of your data and of the Relational Model. You can write Access apps, indeed, any database app, without such understanding. I run into them all the time and I have pulled much of my hair out dealing with them. When an app is done without the prerequisit knowledge, you end up with garbage in your data, duplicate rows, information that should be connected to one row connected the wrong row, and all manner of problems related to bad design and implementation. You can't expect to get it right without a certain minimum foundation. Sorry, that's just the way it is.
But there are many good books and web sites that you can study to get yourself there. Just start searching. It can be done; I've done it. I learned a good bit of Access and the Relational Model by studying various sites and books, and I have done the same thing for Web site programming (HTML, JavaScript, CSS, ASP). You just gotta put in the time studying, that's all. Note I don't say "Read", but "Study", as there is a difference.
Peleg
PelegNOSPAM@PStrauss.net
Other people think they are a neat way to generate things like part numbers, etc. Not hardly. Since you really can't control the value an autonum gets, this seems to be problem.
So, what's left? Right, not much. I use them for one thing, and only one thing, and I admit it is a bit of a cheat. Consider a PK that is the concatenation of several columms, especially text columns. Now, write a few Joins. Write a where-clause with the PK. Messy. I use autonum in order to have a single column to use in Joins and where-clauses. I never show the column to the user on any form, and I never even mention to them that it is even there. It is indexed, of course, but is *not* the PK. I never make use of the value, I don't ever care what the actual value is, except that it is unique. I don't do any calculations on it, as that wouldn't make any sense since I assume I don't know the value in the first place. I act as if it where some random or non-displayable value instead of some long integer.
I've tried not using autonums in this kind of situation, and I ended up going back to using them because it just makes my life so much simpler. As long as they are not my PK, I don't see how it breaks the model.
Now, when the PK is exactly one column, I don't use autonums as there is no advantage to it at all in this case.
No coding is required to choose a proper PK. What is required is a good understanding of your data and of the Relational Model. You can write Access apps, indeed, any database app, without such understanding. I run into them all the time and I have pulled much of my hair out dealing with them. When an app is done without the prerequisit knowledge, you end up with garbage in your data, duplicate rows, information that should be connected to one row connected the wrong row, and all manner of problems related to bad design and implementation. You can't expect to get it right without a certain minimum foundation. Sorry, that's just the way it is.
But there are many good books and web sites that you can study to get yourself there. Just start searching. It can be done; I've done it. I learned a good bit of Access and the Relational Model by studying various sites and books, and I have done the same thing for Web site programming (HTML, JavaScript, CSS, ASP). You just gotta put in the time studying, that's all. Note I don't say "Read", but "Study", as there is a difference.
Peleg
PelegNOSPAM@PStrauss.net
- Thread starter
- #8
Well i made a new field in my sample test db called tapeID and I used an update query [type table]!typename & Format([qa table]![auto numb],"000000" but it still gives me a type mismatch error. Does the format statement convert the auto numb to text so it can be concatonated.
BTW I am inheriting 3 db's written while someone was learning and they are rather large and rather crucial to our business. I am just trying to figure out how to bring them more in line. Generating unique tape id's they used make table and queries. 90 tables and at least 100 queries for about 25 classifications of tapes. I am just trying to figure out how to do it simpler and maybe reduce the size of the db's because we work over a lan also. Next we will try to archive the data. Do you know the relationship of what takes up the most memory. Anyway I am new to the business also just doing some minor stuff before. I have several books. Alot of them use autonumber for a unique pk.
None of these tables have pk's. so conversion to SQL would be a bear. I am just trying to nibble away so I make a test db dealing with what i am trying to do and if I get a good method then i will implement it into the db ect.
Thanks for the help.
but it still gives me a type mismatch error. Does the format statement convert the auto numb to text so it can be concatonated.BTW I am inheriting 3 db's written while someone was learning and they are rather large and rather crucial to our business. I am just trying to figure out how to bring them more in line. Generating unique tape id's they used make table and queries. 90 tables and at least 100 queries for about 25 classifications of tapes. I am just trying to figure out how to do it simpler and maybe reduce the size of the db's because we work over a lan also. Next we will try to archive the data. Do you know the relationship of what takes up the most memory. Anyway I am new to the business also just doing some minor stuff before. I have several books. Alot of them use autonumber for a unique pk.
None of these tables have pk's. so conversion to SQL would be a bear. I am just trying to nibble away so I make a test db dealing with what i am trying to do and if I get a good method then i will implement it into the db ect.
Thanks for the help.
Well, instead of:
[type table]!typename & Format([qa table]![auto numb],"000000"
I'd try:
[type table]!typename & cstr([qa table![auto numb])
because cstr will always convert the number to a string, no matter how many digits.
But still, your version is not causing the problem, I think, even if it is somewhat risky.
Now, the most likely cause of your problem is that you failed to properly quote the string that you are creating. Strings have to be in quotes in an SQL statement, and you can use single or double quotes, as long as you use the same one at the beginning and end of the string.
So, what you should be using is something that looks like this:
"'" & [type table]!typename & cstr([qa table![auto numb]) & "'"
Note how you can quote a single quote by enclosing it in double quotes. The reverse is also true.
Another way:
"""" & [type table]!typename & cstr([qa table![auto numb]) & """"
I know that looks weird, but it works. I forget why.
"I have several books. Alot of them use autonumber for a unique pk." Well, I've seen misinformation in print before. And now, so have you!
"None of these tables have pk's. so conversion to SQL would be a bear." No, that's not the problem. The problem here is that I wouldn't bet you a plug nickel that your data is reliable. And yes, since this app is "rather crucial to our business", I *would* be loosing sleep over it if I were you. You have an important app, written by amatuers. Look at it this way: would you hire a kid with a hammer to build you an addition to your house? Well, you did. Yep, methinks you got problems. And if you don't already know that you gotta quote strings in SQL, you aren't going to be able to wip this one into shape in any reasonable time.
Doing a realiable, industrial-strength Access app is not something that you, or anyone else, can do right out of the gate. I shudder to think about the crap I foisted on the world 9 years ago. No matter it was the best I could do at the time, it was still crap. I ought to know, I think, because my Access experience goes back 9 years, my db experience is a few years more than than (I've even had to roll my own simple DBMS when I was a wee lad), and I've been programming, and nothing but programming, almost 28 years now. I know good when I see it, I know garbage when I see it. I can see the pitfalls and problems from quite a distance because I've fallen into them myself a few times. It just what comes from doing one thing for more than half your life.
Best advice I can give you: don't touch the thing again. Go find yourself an experienced Access programmer and pay him to either fix or rewrite the app. And then sleep well.
Look, if it was just a school project or something, I wouldn't be so harse. But you are betting your business on this thing and it doesn't seem to be bet that's in your favor. You don't seem to be an Access professional. You are running a business. That's what you know how to do. Stick to it. Get someone who know Access to do your app. It will be expensive, but going bankrupt because you can't reliable track your business is more costly.
As a great Hollywood hero once said, "A man's got to know his limitations."
Peleg
PelegNOSPAM@PStrauss.net
[type table]!typename & Format([qa table]![auto numb],"000000"
I'd try:
[type table]!typename & cstr([qa table![auto numb])
because cstr will always convert the number to a string, no matter how many digits.
But still, your version is not causing the problem, I think, even if it is somewhat risky.
Now, the most likely cause of your problem is that you failed to properly quote the string that you are creating. Strings have to be in quotes in an SQL statement, and you can use single or double quotes, as long as you use the same one at the beginning and end of the string.
So, what you should be using is something that looks like this:
"'" & [type table]!typename & cstr([qa table![auto numb]) & "'"
Note how you can quote a single quote by enclosing it in double quotes. The reverse is also true.
Another way:
"""" & [type table]!typename & cstr([qa table![auto numb]) & """"
I know that looks weird, but it works. I forget why.
"I have several books. Alot of them use autonumber for a unique pk." Well, I've seen misinformation in print before. And now, so have you!
"None of these tables have pk's. so conversion to SQL would be a bear." No, that's not the problem. The problem here is that I wouldn't bet you a plug nickel that your data is reliable. And yes, since this app is "rather crucial to our business", I *would* be loosing sleep over it if I were you. You have an important app, written by amatuers. Look at it this way: would you hire a kid with a hammer to build you an addition to your house? Well, you did. Yep, methinks you got problems. And if you don't already know that you gotta quote strings in SQL, you aren't going to be able to wip this one into shape in any reasonable time.
Doing a realiable, industrial-strength Access app is not something that you, or anyone else, can do right out of the gate. I shudder to think about the crap I foisted on the world 9 years ago. No matter it was the best I could do at the time, it was still crap. I ought to know, I think, because my Access experience goes back 9 years, my db experience is a few years more than than (I've even had to roll my own simple DBMS when I was a wee lad), and I've been programming, and nothing but programming, almost 28 years now. I know good when I see it, I know garbage when I see it. I can see the pitfalls and problems from quite a distance because I've fallen into them myself a few times. It just what comes from doing one thing for more than half your life.
Best advice I can give you: don't touch the thing again. Go find yourself an experienced Access programmer and pay him to either fix or rewrite the app. And then sleep well.
Look, if it was just a school project or something, I wouldn't be so harse. But you are betting your business on this thing and it doesn't seem to be bet that's in your favor. You don't seem to be an Access professional. You are running a business. That's what you know how to do. Stick to it. Get someone who know Access to do your app. It will be expensive, but going bankrupt because you can't reliable track your business is more costly.
As a great Hollywood hero once said, "A man's got to know his limitations."
Peleg
PelegNOSPAM@PStrauss.net
pelegs I'm giving you a star because I think the advice you give is spot on. Access is wonderfully easy to use which is both a gift and pitfall to inexperienced users. Once you get beyond a certain stage in Access it is essential to have theoretical knowledge of relational databases. The fact that Access is not a real RDBMS does not matter, most of Codd's rules still apply! And if anyone reading this doesn't know about Codd - go and and find out.
lewie - this is no reflection on you, more an appeal to common sense. What does concern me however is your statement "dbs...are rather large and rather crucial to our business". Access database are not very robust when they get large and certainly start to respond slowly. I'd extend peles's advice and if the database is really large suggest you consider getting advice from a SQL Server specialist. (No, I'm not one!)
Simon Rouse
lewie - this is no reflection on you, more an appeal to common sense. What does concern me however is your statement "dbs...are rather large and rather crucial to our business". Access database are not very robust when they get large and certainly start to respond slowly. I'd extend peles's advice and if the database is really large suggest you consider getting advice from a SQL Server specialist. (No, I'm not one!)
Simon Rouse
- Thread starter
- #11
- Status
Similar threads
- Locked
- Question