Splitting a Filed/Column

[peeaurjee]

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.

[hjozinovic]

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.

[peeaurjee]

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?

[hjozinovic]

Hi
Sorry I havent attached the database before...
Here it is...

[missinglinq]

(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:

dlcfrom: Left([YourCombinedDatesField],instr([YourCombinedDatesField], " ")-1)

(4) Repeat this with a new blank field and enter:

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:

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)>

[peeaurjee]

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.

[peeaurjee]

(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:

dlcfrom: Left([YourCombinedDatesField],instr([YourCombinedDatesField], " ")-1)

(4) Repeat this with a new blank field and enter:

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:

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.

[hjozinovic]

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.

[missinglinq]

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)>

[peeaurjee]

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