By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

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

100+
P: 178
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?
2 Weeks Ago #1

✓ answered by cactusdata

Yes, split it into its parts:

Expand|Select|Wrap|Line Numbers
  1. Code = "0104191"
  2.  
  3. ShiftDate = DateSerial(Mid(Code, 5, 2), Mid(Code, 1, 2), Mid(Code, 3, 2))
  4. Shift = Val(Right(Code, 1))

Share this Question
Share on Google+
3 Replies


Seth Schrock
Expert 2.5K+
P: 2,944
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.
2 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,561
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.
2 Weeks Ago #3

P: 30
Yes, split it into its parts:

Expand|Select|Wrap|Line Numbers
  1. Code = "0104191"
  2.  
  3. ShiftDate = DateSerial(Mid(Code, 5, 2), Mid(Code, 1, 2), Mid(Code, 3, 2))
  4. Shift = Val(Right(Code, 1))
2 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.