How to Add a word infront of Auto test number (1 Viewer)

sabeeh3 · May 16, 2019

Hi,
I will like to add "Test"before the Auto number. i have created a query and number is starting from 4000.
Can someone please suggest how it can be done?
Also
Is it possible to restrict a user to delete the rows? can we setup a password so all the users cannot delete?
I would appreciate if someone can help me out.

thanks

theDBguy · May 16, 2019

Hi. Welcome to the forum. In your query only, not in the table, you can add anything together using a calculated column. For example:

Code:

MyID: "Test" & Format([AutonumberFieldName],"0000")

To secure your database, so no one can delete records, you'll need to make sure users only use forms for interaction. You can then employ some sort of a login feature to determine which users are allowed to delete or not.

sabeeh3 · May 16, 2019

theDBguy said:
Hi. Welcome to the forum. In your query only, not in the table, you can add anything together using a calculated column. For example:

Code:

MyID: "Test" & Format([AutonumberFieldName],"0000")

To secure your database, so no one can delete records, you'll need to make sure users only use forms for interaction. You can then employ some sort of a login feature to determine which users are allowed to delete or not.

Thanks.
Write now when i click on my Queries. It gives me Field: Exper1:4000. Where do i need to add Test?
I have put the Access database in shared folder. How can i make sure users only user forms for interaction?
Thanks for your help

theDBguy · May 16, 2019

sabeeh3 said:
Thanks.
Write now when i click on my Queries. It gives me Field: Exper1:4000. Where do i need to add Test?
I have put the Access database in shared folder. How can i make sure users only user forms for interaction?
Thanks for your help

Hi. If you can post the SQL statement for your query, we can help you modify it to include the "test" part. As for creating a somewhat secured database, take a look at this previous discussion.

sabeeh3 · May 17, 2019

Thank you.
Here is SQL Statement
INSERT INTO Ticket_Tracker_Table ( ID )
SELECT 4000 AS Expr1;

If i put Test before 4000 and run the query. I get the pop up window, it says "Enter Parameter value. When i write Test4000, it says Data type mismatch in the criteria expression.

sabeeh3 · May 20, 2019

Hi,
Did you get a chance to look at the SQL statement?

The_Doc_Man · May 20, 2019

sabeeh3 - the confusion comes about because there are two ways to do this, but they require added complexity.

If you want to always see the word TEST appearing in something (and never want any other word) then you don't ever need to store the word TEST at all. You just create a query that contains something like

Code:

SELECT "Test" & ID FROM Ticket_Tracker_Table ;

If the word is variable in nature AND the ID is always a number, you cannot store them in the same slot so easily because one is text and the other is numeric. That's called a "mixed mode" and Access doesn't like that. In that case, you COULD store the value in a NON-NUMERIC field i.e. short text, using

Code:

INSERT INTO Ticket_Tracker_Table (ID) VALUES ("Test4000") ;

There are issues with that, since you can no longer treat the ID as a number. So if you needed to know that the next value was 4001, you have to now decompose the previous ID to figure that out.

Why don't you describe your needs, not for this record, but for the table as a whole? Try to avoid talking "code" and instead try telling us descriptively what you wanted to do.

sabeeh3 · May 20, 2019

Thank you very much for the detailed explanation.

I am trying to create a database where i want the ID to be a autonumber which i am able to do it but i wanted to add "Test" before the autonumber.

Write now the way it is, My number is starting from 4000 and then it been continue like 4001, 4002, etc. What i was just looking for is to add "Test" for every number like Test 4000, Test 40001, Test4002, etc.

mike60smart · May 20, 2019

Why?

Adding Test before a Unique number does not achieve anything.

The_Doc_Man · May 20, 2019

In general, adding an essentially constant string is something you don't store, particularly with autonumbers (since they must be NUMBERS). So you CAN'T store "Test" as part of the ID. That will ALWAYS be mixed-mode. But using a query to supply the constant text part using "&" for concatenation? That's easy and well within the function of a query - which includes not only sorting, filtering, and computations, but also formatting.

The other possibility is if TEST is not ALWAYS what you would prefix on that ID, you would have a text field for the IDPrefix and the autonumber ID, after which you would use something like

Code:

SELECT IDPrefix & CStr(ID), ... FROM ...

You could also do a FORMAT statement, but I'm keeping it simple for the moment.

sabeeh3 · May 20, 2019

The_Doc_Man said:
In general, adding an essentially constant string is something you don't store, particularly with autonumbers (since they must be NUMBERS). So you CAN'T store "Test" as part of the ID. That will ALWAYS be mixed-mode. But using a query to supply the constant text part using "&" for concatenation? That's easy and well within the function of a query - which includes not only sorting, filtering, and computations, but also formatting.

The other possibility is if TEST is not ALWAYS what you would prefix on that ID, you would have a text field for the IDPrefix and the autonumber ID, after which you would use something like

Code:

SELECT IDPrefix & CStr(ID), ... FROM ...

You could also do a FORMAT statement, but I'm keeping it simple for the moment.

Thanks a lot, i appreciate your help.

How to Add a word infront of Auto test number (1 Viewer)

sabeeh3

New member

theDBguy

I’m here to help

sabeeh3

New member

theDBguy

I’m here to help

sabeeh3

New member

sabeeh3

New member

The_Doc_Man

Immoderate Moderator

sabeeh3

New member

mike60smart

Registered User.

The_Doc_Man

Immoderate Moderator

sabeeh3

New member

Similar threads

Users who are viewing this thread