Solved: MS Access: Automatically remove certain characters

[babushka]

Hi,
I have this table with a field that accepts ID numbers. Unfortunately, the data is inputted from a barcode scanner which adds a ; to the beginning of the id numbers and a ? to the end of it. E.g. ;1234567?
Is there any way I could have Access filter out or remove those unwanted characters when the data is entered?
And could it be done without code because I'm kinda a new to Access.

Much thanks for any help

 

[slurpee55]

Are the numbers all the same length? If they are, it is very easy in a query. Say they are all 7 digits (not counting the ";" and the "?"). Then make a query of your table with the data and in the ID column in design view, enter this for the field (rather than ID)
Expr1: Mid([ID],2,7). You enter this directly into the row called Field.
Alternatively, you could copy the data, put it into Excel and do a text to columns, fixed width and, in 2 passes, remove the ";" and the "?", then copy the IDs back into Excel.

Ifn however, the IDs are of various lengths, we will have to tackle this in another manner.

 

[babushka]

Thanks for your reply.

This is my situation. I have two tables:
(1)has a [name] field with a list of names and an [id#] field with the corresponding id numbers;
(2)is a table with these fields - [items],[date],[item check out id],[item return id],[notes]

I made a relationship between [id#] in table (1) and [item check out id] in table (2).
I did that so that when I make a query with these fields--[name],[items],[date],[item check out id],[item return id],[notes]--the name will automatically display everytime someone swipes their ID in the [item check out id] fields because of that relationship.
Also, because of that, I am using the query as the primary "table" for data entry, although I am not sure if that is the best thing to do. I would much prefer to use a real table instead, but I haven't found out how to make those names automatically display when I swipe an ID card into the table (2).

So now, I was asked to adjust the query so the moment an ID is swiped it will appear in the [item check out id] field as just the numbers minus the ";" and "?". Oh, there are actually 14 numbers in the ID, and they are all the same length.

When I tried that mid() function, it only worked if I put the numbers in the [item check out id] field in table (2), and then made the query with that mid(). However, I can't enter the ID number directly into the query, but I understand why.

I hope I haven't made the situation even more complicated. There's so much I still have to learn. I never used Access before I got this assignment.

Any help is much appreciated. Thanks.

 

[slurpee55]

Well, the expression (you changed the number in Expr1: Mid([ID],2,7) so that it has whatever is correct rather than the 7, I take it) does not display the ID - that is the whole number with the ; and the ?. That is why you can't enter it into the form.
It is always easier - and better - to use an autonumber for your primary key, and you could just show the combined ID in another field.

 

[OBP]

I would suggest that the Barcode Scanner Value is put in to a Related table to the other 2 and the Number is left in it's original Format, run the Query or I can create you some VBA code (you don't need to know how it works) to put the correct Format in to either of the other 2 tables.

 

[babushka]

Okay, I'm kinda confused. So, if u don't mind, I decided to attach an example of what I have so far and perhaps anyone who takes a look might understand what I need to do.

Table 2 has example names and ID numbers corresponding to those names. I have kept the format of the ID number as it will appear when it is swiped.

Table 1 contains various other important columns. If you look at the relationship window, you will see the relationship I created.
There is also a query--Query1--that I created using certain fields from both tables.

Now, whenever someone wants to check out an item to borrow, they swipe their ID while the cursor is in the "ID (Check Out):" field in the Query1 window. Their matching name will automatically display, and they can select the item they are borrowing from the drop down menu in the "ITEMS" field in Query1. Then, once they're done, they return the item(s) and we swipe their ID into the "ID (Returning):" field of the same row.
All this checking out and returning activity is recorded in the Query1. That's because I didn't know how to make the name appear like that while using Table1, otherwise, I would prefer to record this activity in an actual table instead of a query.

So far it is working fine, but now people want me to tweak it so that when you swipe the ID to check out or return, the number appears in the respective fields not like this
;00000000000000? but like this 00000000000000.

I'm sorry for my long post again, but I feel that it is necessary since my problem is unique. I would like to be of as much help as possible.

Thanks slurpee55 and OBP for taking the time so far.

 

[slurpee55]

So the IDs are personal? I would have a list of all of them in a separate table, but NOT use it as the ID for the transaction - I would have each of those generate a new transaction ID automatically.

 

[babushka]

Oh, these are all school IDs, and we're using Access to keep a log of which student is in possession of what equipment. Not really personal if you know what I mean.

 

[OBP]

babushka, how does the Query capture the Swiped data?

 

[babushka]

Well, the supervisor hooked up the scanner to the computer, and the Query (or it could be Notepad, MS Word, Excel, whatever is open...) accepts the information from the scanner as if the numbers were inputted from the keyboard, I think.
So, wherever the cursor is placed, you swipe and that data is entered in that field.

 

[OBP]

babushka, have a look at the Form that I have added to this version.
It has a small field for entering the barcode Swipe and next to it a larger field that will display the Barcode as you want it. If you wanted to Save the Barcode in the "Proper" 14 character format you would reverse the fields and have the Barcode field as the "Unbound" one and the larger field would be the one from your table.

I have only tested the form by pasting the barcode in to the small field.

 

[babushka]

Sorry for the late reply.
Thanks OBP for that example. I am using a form now instead of the query.
All works fine now. I should mention that I'm using the form in datasheet view so you can see the previous entries, so when someone is returning the items they can find their name and swipe their id in the "ID (Returning)" field next to the "ID (Checking Out)" field where they originally swiped their id.
However, I'm not always there to monitor how other assistants use this form, but my supervisor has mentioned that it looks like some records have been accidently deleted. I think it was a result of people trying to change the settings of the database when they encountered a problem, or got an error message. And even though I am not so savvy with MS Access, I know a bit more than anyone else who uses it. So I guess what I am saying is, is there anyway I could make each line of entry automatically save or back up itself into another table, maybe into an Excel table. I don't want to automatically save or export the whole form every few minutes into another table because then if one entry was accidently deleted then when you save/export the form again it would have one less row of information than the previous form that was saved say 5 minutes ago or something. I don't know if I am explaining the situation clearly, there's a lot of noise around me, I can't hear myself think.
Basically, I've been searching for a way to automatically save the information on the form into some back up table, and make sure that it is not just a copy of the form where errors are copied as well, and no overwriting of the previous saved information as well.
Much thanks again for all the help. I will continue to google for now for some solution.

 

[OBP]

Babushka, as they are not that Access savy try this first, on the Access Main Menu go to Tools>Startup and untick "Display Database Window", that will hide all of the Tables, queries and forms etc.
Next, on the Form's Properties set the "Allow Deletions" to "No", that will prevent them from deleting Records on the Form.
You could also set the Form's "Allow Edits" property to "No" and use VBA to Switch it back on when the user enters the Booking in Fields if they are empty.
You can also use VBA to set the Fields to "Enabled" = "No" after the entries are made in them.
If that doesn't work well enough then I would suggest that you use the Security Wizard to secure the database and have 2 Groups "Users" who can only Enter and Edit data and Admin who is You who can do everything necessary to maintain the database.

 

[babushka]

I guess a combination of hiding the database window and setting Allow Deletions to No could help. Luckily I tried that on the Example database because I had trouble getting the window to appear again after I set it to hide. However, it looks like it is still possible to delete data in the individual fields. But I've been stressing to them about what to do and what not to do as well, so I just hope no new problems arise.
How does one learn VBA code? Is it useful elsewhere besides Access? I could probably google and find out right? I think I'll do that.
I'm sincerely grateful for your help. Thanks.

 

[OBP]

Books are good for learning VBA code, especially those that come with a CD of Examples.
It is also used in Excel and Word, but each one has some major differences to allow for the way the Programs work, but they also have a great deal in common.
If the Form is set allow deletions = no it only prevents deletion of Records not field data.
You could have some fairly simple VBA set the Fields to "Enabled" = "no" after entries have been made to prevent deletion of the field data.