Separating a text input to 3 fields

[BigBuedro]

Apologies if this is one that gets asked ALL of the time (i can't find an answer if it is).

I'm using Access 2010 but my knowledge is VERY limited. I have a form based on a table, with a field where I have to input a 12 character long alphanumeric seriel number (via a bar code scan)

It is ALWAYS 2 letters, 9 numbers and 1 letter.

I need to be able to record the seriel number in the field but then also split it into 3 other fields (2, 9, 1) at the point at which it is entered.

Could someone explain (in basic terms) how i can do this?

Let me know if any further info is required.

Any help appreciated.

 

[DCrake]

Part1 = Left(String,2)
Part2 = Mid(String,3,9)
Part3 = Right(String,1)

 

[John Big Booty]

Given that you have the serial number stored as a complete record there is really no need to break it down in your table (this would be a breach of the rules of data normalisation). You can however use the various available String manipulation functions to extract the various portions of the serial number as required for display or comparison purposes.

 

[BigBuedro]

DCrake - thanks for your help, however (at the risk of sounding really thick) i don't know where to enter the info you've given me (my knowledge really is that limited)!

John Big Booty - the seriel number is made up of a 2 letter PART NUMBER, a 9 character Unique Identifying Number and a 1 letter code (either F, M or G) that refers to where it was manufactured.
The reason i need to break it down as well as record it as a whole is:
1) for display purposes
2) so that the 2 letter part number can then be used to refer to another table containing part number conversion (e.g. an XK could become an AB, AK, or an AU when it's had some work done to it) and the user will need to select from a dropdown which of the finished parts it's become
3) the 1 letter code at the end will similarly refer to a table that will say where it was manufactured

I hope this explains it a bit more and doesn't confuse the issue (TMI).

I will probably have further questions once i get to these stages, if i manage to get it broken into 3.

Thanks to you both for your help so far.

 

[DCrake]

If you have the string all in one field in a table then that OK

Create a new query and have the first three columns as such

Col1:Left([KeyField],2)
Col2:Mid([KeyField,3,9)
Col3:Right([KeyField,1)
any more columns needed

The you can use this as the basis of the rest of your work.

 

[BigBuedro]

Thanks David,

I now have a query as such:

Field:___InputPart_______ModuleIDNo______Location
Table:__TestRecords_____TestRecords_____TestRecords
Sort:
Show:
Criteria: Left("SerielNo",2) Mid("SerielNo",3,9) Right("SerielNo",1)


I'm gonna sound thick (but hey i can't help it), how do i now 'attach' this the SerielNo field (where it's scanned into) so that when the number is input it actually runs the query and populates the 3 other fields?

Sorry for all the questions.

 

[BigBuedro]

Just figured out how to attach a screenshot of my query (I think)

 

[John Big Booty]

DCrake - thanks for your help, however (at the risk of sounding really thick) i don't know where to enter the info you've given me (my knowledge really is that limited)!

John Big Booty - the seriel number is made up of a 2 letter PART NUMBER, a 9 character Unique Identifying Number and a 1 letter code (either F, M or G) that refers to where it was manufactured.
The reason i need to break it down as well as record it as a whole is:
1) for display purposes
2) so that the 2 letter part number can then be used to refer to another table containing part number conversion (e.g. an XK could become an AB, AK, or an AU when it's had some work done to it) and the user will need to select from a dropdown which of the finished parts it's become
3) the 1 letter code at the end will similarly refer to a table that will say where it was manufactured

I hope this explains it a bit more and doesn't confuse the issue (TMI).

I will probably have further questions once i get to these stages, if i manage to get it broken into 3.

Thanks to you both for your help so far.

All those things can be achieved with the serial number stored as a single field in your record, as I said before to break down and store various portions of the serial number in the same record is in breach of the rules of normalisation. All those points that you raised can be achieved on the fly using the code presented by DCrake.

 

[boblarson]

how do i now 'attach' this the SerielNo field (where it's scanned into) so that when the number is input it actually runs the query and populates the 3 other fields?

For a PROPER database design, you do not store the same data more than once. So, you should EITHER store the three fields or the one with the parts together. You should NOT (as you've already been told by a couple of long-time developers) store them both. The thing that appears to have escaped your thinking process when told of this is that you can build a BASE QUERY to have the split there and then you can use that in 99% of the places you would use the table. So there is no need to store the three parts. It is redundant and it is potentially a referential integrity nightmare.

 

[BigBuedro]

Good morning guys,

I'm not deliberately ignoring anyone's advice and I don't WANT to store the data more than once (certainly if it's going to be a problem), it's just that i'm clearly not understanding what i'm supposed to be doing.

All those things can be achieved with the serial number stored as a single field in your record, as I said before to break down and store various portions of the serial number in the same record is in breach of the rules of normalisation. All those points that you raised can be achieved on the fly using the code presented by DCrake.

I've clearly misunderstood where i'm supposed to put the code presented by DCrake:
"Create a new query and have the first three columns as such..."

That's why I then posted what i'd done, so that someone could say "you've messed that up" (assuming I had, which I guess I have).

Please excuse my stupidity.

I'm more than happy to store the entire seriel number as it's scanned in (e.g. XK103100562M) in just ONE place (Table=TestRecords, Field=SerielNo) where do I put DCrake's code or build the BASE QUERY that boblarson has suggested?

(In my defence I stated from the beginning my knowledge was limited and that's why I came here for help).

 

[John Big Booty]

If you want to break the serial number for display purposes on a form you can put the code in the On Current event of the form it might look something like;

Me.TextBoxName1 = Left([KeyField],2)
Me.TextBoxName2 = Mid([KeyField,3,9)
Me.TextBoxName3 = Right([KeyField,1)

You can also use it in a query to compare it with other values.