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

Splitting a Filed/Column

peeaurjee
P: 34
Hi!
I have a column in the Table of my database named 'Duration of Labour Contract' and inside this i have entered the data like that:

01/01/2006 - 31/12/2009

Now i need to split this column into two, in which one should be named 'dlcfrom' and 'dlcto' by isolating the two dates. On the form it should look like that:
From : 01/01/2006 To: 31/12/2009

So is it possible to split a column into two or I will do it manually by cutting and pasting and editing the whole 1200 records :( any other solution please if is it there.
Dec 11 '07 #1
Share this Question
Share on Google+
9 Replies


100+
P: 167
Hi there!
Don't worry, I solved this problem and attached the database to this reply so you can see how it's done.
I copy/pasted your date range and did the split.

Note that ContractStart and ContractEnd fields are Date/Time type, while your data range is Text!
All you need to do is to make an Update query like i did and find a way to run it (I added a button for that).

Importaint: You must check that all your original entries are entered in the same way and text length with the spaces around "-" !!!
(01/01/2006 - 31/12/2008)

Best, H.
Dec 11 '07 #2

peeaurjee
P: 34
hi friend!

Thank you very much but i still don't have any idea what you've done and also i am seeing no attachment. Where did you attach the sample data base?
Dec 11 '07 #3

100+
P: 167
Hi
Sorry I havent attached the database before...
Here it is...
Attached Files
File Type: zip Splitting.zip (17.8 KB, 66 views)
Dec 11 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
(1) The easiest way to do this, to my mind, would be to use calculated fields in a query, then base your form on the query. This is always the preferred method, rather than basing the form directly on the table. If you've already done this, skip to Step # 3.

(2) If you haven't already done this, simply use the query wizard and include all fields from the table. Now go into Design View for your form and change the RecordSource from your table to your query.

(3) Go into Design View for your new query. In a blank "Field" box enter:
Expand|Select|Wrap|Line Numbers
  1. dlcfrom: Left([YourCombinedDatesField],instr([YourCombinedDatesField], " ")-1)
(4) Repeat this with a new blank field and enter:
Expand|Select|Wrap|Line Numbers
  1. dlcto: Right([YourCombinedDatesField],InstrRev([YourCombinedDatesField], " ")-3)
(5) Now on your form or in reports you can simple refer to these two fields as if they actually existed in your table.

For you appearance on you form you could use something like:

Expand|Select|Wrap|Line Numbers
  1. Me.DisplayDates = "From: " & Me.dlcfrom & : To: " & dlcto
Note that in this method the only requirements is that the original field has at least one space after the first "date" and at least one space before the second "date."

Linq ;0)>
Dec 11 '07 #5

peeaurjee
P: 34
Hi
Sorry I havent attached the database before...
Here it is...
Oh Thank You very very much. It was really helpful and it did work for me. The only thing i did different is that i kept the Data Type of the ContractStart and ContractEnd as Text because most of the fields are also in the following format.

ContractDuration: 01/01/2007 - Unlimited

Thus i got the same result i wanted. Thanks to you once again.
Dec 11 '07 #6

peeaurjee
P: 34
(1) The easiest way to do this, to my mind, would be to use calculated fields in a query, then base your form on the query. This is always the preferred method, rather than basing the form directly on the table. If you've already done this, skip to Step # 3.

(2) If you haven't already done this, simply use the query wizard and include all fields from the table. Now go into Design View for your form and change the RecordSource from your table to your query.

(3) Go into Design View for your new query. In a blank "Field" box enter:
Expand|Select|Wrap|Line Numbers
  1. dlcfrom: Left([YourCombinedDatesField],instr([YourCombinedDatesField], " ")-1)
(4) Repeat this with a new blank field and enter:
Expand|Select|Wrap|Line Numbers
  1. dlcto: Right([YourCombinedDatesField],InstrRev([YourCombinedDatesField], " ")-3)
(5) Now on your form or in reports you can simple refer to these two fields as if they actually existed in your table.

For you appearance on you form you could use something like:

Expand|Select|Wrap|Line Numbers
  1. Me.DisplayDates = "From: " & Me.dlcfrom & : To: " & dlcto
Note that in this method the only requirements is that the original field has at least one space after the first "date" and at least one space before the second "date."

Linq ;0)>
Thank you Linq....That will be a new experiance for me. I am just trying it and will tell you the result.
Dec 11 '07 #7

100+
P: 167
This was my first succesfully answered thread. I received here a lot and I want to give something back. I'm glad I could help...
Wish you luck with your project!
h.
Dec 11 '07 #8

missinglinq
Expert 2.5K+
P: 3,532
Even if you don't use it for this project, knowing how to use calculated fields in queries is a handy tool to have in your skill set!

Having your "dates" as text is fine as long as you're sure you'll never have to do any kind of date manipulation with them, and to be honest, Access is really fairly forgiving even then. Many functions such as DateDiff() and DateAdd() work fine with text "dates" as long as the text looks like a legimate date.

Linq ;0)>
Dec 11 '07 #9

peeaurjee
P: 34
Even if you don't use it for this project, knowing how to use calculated fields in queries is a handy tool to have in your skill set!

Having your "dates" as text is fine as long as you're sure you'll never have to do any kind of date manipulation with them, and to be honest, Access is really fairly forgiving even then. Many functions such as DateDiff() and DateAdd() work fine with text "dates" as long as the text looks like a legimate date.

Linq ;0)>
o.O May your sayings come true Mr. Linq that date manipulation will not make any problem because i am going to make an increment system according to the dlcfrom field that is the hiring date of an employee. I have put a thread regarding to this issue. Please if you can have a look at this.

http://www.thescripts.com/forum/thread747333.html
Dec 11 '07 #10

Post your reply

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