is it possible to split up a number in one field into 2 new fields?

[DJRhino1175]

In a table I have a field labeled LotNo which contains a number, only it's a date+shift and looks like this "0104191", I'm being asked to make a report that is grouped by shift. Is there a way to split this number into Date "01/04/19" and shift (1, 2, or 3) using a query?

[Seth Schrock]

If your number does actually does start with a 0, then your number is stored as text. This makes it easy to split up the different parts. The Left() function allows you to specify how many characters you want from the left side of the string. The Right() function allows you specify how many characters you want from the right side. For example, Right(LotNo, 1) will get you the shift. I'd put it in a query and then base your report off the query. It makes it a little more straight forward to then group your report on the field.

[NeoPa]

Hi DJ.

I suspect you're now at the stage where understanding the difference between a number, on the one hand, and a string value that simply contains only digits, on the other, is quite important.

When communicating with others, as is common and necessary when asking for help on a subject, it is very important that you learn and use terminology that expresses your situation reliably and accurately. You will obviously benefit from that as experts will find it easier to answer your questions.

In this case Seth has spotted your error and given a full answer that is correct, but you can't always rely on that. Often the clues that Seth spotted just aren't there and we're left giving advice that's inappropriate and would obviously not work.

It's all part of the learning process you're on but I would say you should be thinking of getting on top of that at this stage.

All the best -Ade.

[cactusdata]

Yes, split it into its parts:

Code = "0104191"
 
ShiftDate = DateSerial(Mid(Code, 5, 2), Mid(Code, 1, 2), Mid(Code, 3, 2))
Shift = Val(Right(Code, 1))