I have a database of about 8000 records that I imported from a corrupt
msworks db.  For the most part the transition is working, but I am
having trouble with one thing.

There are two date fields that came in as numbers in the format
yyyymmdd.  I would like to change them to mm/dd/yyyy.  I see that at
some stage in this process I will want to use TextToNum, but I don't
know how to rearrange the digits and insert slashes to get my field
ready.

In summary, for a product I inventoried on January 27, 1995, I have a
cell in my date_inventoried field that has the text: 19750127.  I would
like to change that to 01/27/1975.

The only way I can think of involves creating 3 dummy fields, diving by
powers of ten and using the Int function to discard pieces of the value,
then recombining


(field:	  Thought 	
date_inv1: Int(date_inv/10000)
date_inv2: A messier version of what is below for date_inv3
date_inv3: ((date_inv/100)-Int(date_inv/100))*100
date_inv_summary: 
   something like: Texttodate(date_inv2 & "/" & date_inv3 & "/" & date_inv1)

This seems like a huge mess with many opportunities for incorrect
syntax.  Is there a better way to do this?


Adam

Seems to me you'd do well with taking your 19750127 text field (I'll 
call it OrigDate) and using it to perform a replace for all records 
within a date field...something like:

date(
   middle(OrigDate, 5, 2),
   middle(OrigDate, 7, 2),
   left(OrigDate, 4)
)


Adam Price wrote:
> I have a database of about 8000 records that I imported from a corrupt
> msworks db.  For the most part the transition is working, but I am
> having trouble with one thing.
> 
> There are two date fields that came in as numbers in the format
> yyyymmdd.  I would like to change them to mm/dd/yyyy.  I see that at
> some stage in this process I will want to use TextToNum, but I don't
> know how to rearrange the digits and insert slashes to get my field
> ready.
> 
> In summary, for a product I inventoried on January 27, 1995, I have a
> cell in my date_inventoried field that has the text: 19750127.  I would
> like to change that to 01/27/1975.
> 
> The only way I can think of involves creating 3 dummy fields, diving by
> powers of ten and using the Int function to discard pieces of the value,
> then recombining
> 
> 
> (field:	  Thought 	
> date_inv1: Int(date_inv/10000)
> date_inv2: A messier version of what is below for date_inv3
> date_inv3: ((date_inv/100)-Int(date_inv/100))*100
> date_inv_summary: 
>    something like: Texttodate(date_inv2 & "/" & date_inv3 & "/" & date_inv1)
> 
> This seems like a huge mess with many opportunities for incorrect
> syntax.  Is there a better way to do this?
> 
> 
> Adam

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

I should have mentioned that I am using filemaker 6.0.4 - is it possible
to do that in this older version?

-Adam

In article <11le55sfcqs3q3d@corp.supernews.com>, Howard Schlossberg wrote:
> Seems to me you'd do well with taking your 19750127 text field (I'll 
> call it OrigDate) and using it to perform a replace for all records 
> within a date field...something like:
> 
> date(
>    middle(OrigDate, 5, 2),
>    middle(OrigDate, 7, 2),
>    left(OrigDate, 4)
> )
> 
> 
> Adam Price wrote:
>> I have a database of about 8000 records that I imported from a corrupt
>> msworks db.  For the most part the transition is working, but I am
>> having trouble with one thing.
>> 
>> There are two date fields that came in as numbers in the format
>> yyyymmdd.  I would like to change them to mm/dd/yyyy.  I see that at
>> some stage in this process I will want to use TextToNum, but I don't
>> know how to rearrange the digits and insert slashes to get my field
>> ready.
>> 
>> In summary, for a product I inventoried on January 27, 1995, I have a
>> cell in my date_inventoried field that has the text: 19750127.  I would
>> like to change that to 01/27/1975.
>> 
>> The only way I can think of involves creating 3 dummy fields, diving by
>> powers of ten and using the Int function to discard pieces of the value,
>> then recombining
>> 
>> 
>> (field:	  Thought 	
>> date_inv1: Int(date_inv/10000)
>> date_inv2: A messier version of what is below for date_inv3
>> date_inv3: ((date_inv/100)-Int(date_inv/100))*100
>> date_inv_summary: 
>>    something like: Texttodate(date_inv2 & "/" & date_inv3 & "/" & date_inv1)
>> 
>> This seems like a huge mess with many opportunities for incorrect
>> syntax.  Is there a better way to do this?
>> 
>> 
>> Adam
> 

Adam Price wrote:
> I should have mentioned that I am using filemaker 6.0.4 - is it possible
> to do that in this older version?

As far back as FileMaker 2, I believe.

> 
> -Adam
> 
> In article <11le55sfcqs3q3d@corp.supernews.com>, Howard Schlossberg wrote:
>> Seems to me you'd do well with taking your 19750127 text field (I'll 
>> call it OrigDate) and using it to perform a replace for all records 
>> within a date field...something like:
>>
>> date(
>>    middle(OrigDate, 5, 2),
>>    middle(OrigDate, 7, 2),
>>    left(OrigDate, 4)
>> )
>>
>>
>> Adam Price wrote:
>>> I have a database of about 8000 records that I imported from a corrupt
>>> msworks db.  For the most part the transition is working, but I am
>>> having trouble with one thing.
>>>
>>> There are two date fields that came in as numbers in the format
>>> yyyymmdd.  I would like to change them to mm/dd/yyyy.  I see that at
>>> some stage in this process I will want to use TextToNum, but I don't
>>> know how to rearrange the digits and insert slashes to get my field
>>> ready.
>>>
>>> In summary, for a product I inventoried on January 27, 1995, I have a
>>> cell in my date_inventoried field that has the text: 19750127.  I would
>>> like to change that to 01/27/1975.
>>>
>>> The only way I can think of involves creating 3 dummy fields, diving by
>>> powers of ten and using the Int function to discard pieces of the value,
>>> then recombining
>>>
>>>
>>> (field:	  Thought 	
>>> date_inv1: Int(date_inv/10000)
>>> date_inv2: A messier version of what is below for date_inv3
>>> date_inv3: ((date_inv/100)-Int(date_inv/100))*100
>>> date_inv_summary: 
>>>    something like: Texttodate(date_inv2 & "/" & date_inv3 & "/" & date_inv1)
>>>
>>> This seems like a huge mess with many opportunities for incorrect
>>> syntax.  Is there a better way to do this?
>>>
>>>
>>> Adam

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

In article <11le55sfcqs3q3d@corp.supernews.com>, Howard Schlossberg wrote:
> Seems to me you'd do well with taking your 19750127 text field (I'll 
> call it OrigDate) and using it to perform a replace for all records 
> within a date field...something like:
> 
> date(
>    middle(OrigDate, 5, 2),
>    middle(OrigDate, 7, 2),
>    left(OrigDate, 4)
> )
> 
> 

Sorry - I am a newbie here///

I understand the syntax of what you've given me, but I don't know what
to do with it.

1. I tried putting it in the replace box of the find/replace dialog.  No dice.
2. I tried making a new field (type: date) and putting it in the Data or in
the Calculated value boxes of the options dialog.  No dice.
3. I tried making a new field (type: calculation) and putting it there.  No
dice.

All these the first method I couldn't even quite do.  THe other two
resulted in a field with no entries.  What am I missing? 

Thanks for your help - I'll summarize this for the newsgroup once I get
it down. 

-Adam








> Adam Price wrote:
>> I have a database of about 8000 records that I imported from a corrupt
>> msworks db.  For the most part the transition is working, but I am
>> having trouble with one thing.
>> 
>> There are two date fields that came in as numbers in the format
>> yyyymmdd.  I would like to change them to mm/dd/yyyy.  I see that at
>> some stage in this process I will want to use TextToNum, but I don't
>> know how to rearrange the digits and insert slashes to get my field
>> ready.
>> 
>> In summary, for a product I inventoried on January 27, 1995, I have a
>> cell in my date_inventoried field that has the text: 19750127.  I would
>> like to change that to 01/27/1975.
>> 
>> The only way I can think of involves creating 3 dummy fields, diving by
>> powers of ten and using the Int function to discard pieces of the value,
>> then recombining
>> 
>> 
>> (field:	  Thought 	
>> date_inv1: Int(date_inv/10000)
>> date_inv2: A messier version of what is below for date_inv3
>> date_inv3: ((date_inv/100)-Int(date_inv/100))*100
>> date_inv_summary: 
>>    something like: Texttodate(date_inv2 & "/" & date_inv3 & "/" & date_inv1)
>> 
>> This seems like a huge mess with many opportunities for incorrect
>> syntax.  Is there a better way to do this?
>> 
>> 
>> Adam
> 

In article <kDF5f.103$uo3.36794@newshog.newsread.com>, Adam Price wrote:
> In article <11le55sfcqs3q3d@corp.supernews.com>, Howard Schlossberg wrote:
>> Seems to me you'd do well with taking your 19750127 text field (I'll 
>> call it OrigDate) and using it to perform a replace for all records 
>> within a date field...something like:
>> 
>> date(
>>    middle(OrigDate, 5, 2),
>>    middle(OrigDate, 7, 2),
>>    left(OrigDate, 4)
>> )
>> 
>> 
> 
> Sorry - I am a newbie here///
> 
> I understand the syntax of what you've given me, but I don't know what
> to do with it.
> 
Answered my own question:

Here goes: In order to convert a field of numbers in the format
yyyymmmmddd (we'lls call it date_old) to a date field with the format
mm/dd/yy (date_new)

1.  Make sure that date_old is defined as a *text* field.  This is very
important, and is why this didn't work for me at first.

2.  Create a new field (Date_new) that is defined as a calculation.  The
calculation is as Howard gave me:
    Date(  Middle(Date_old, 5, 2),
           Middle(Date_old, 7, 2),
           Left(Date_old, 4)
		)
Below the calculation entry box, make sure that "Calculation Result" is "Date"

3. Save and close the define fields dialog.  Allow FM to calculate all
those dates.
4.  Once they are all calculated, reopen the define fields box
5.  Change the field type of Date_new to date
6.  Close dialog
7.  Sort by the new field in browse view.  Any glaring errors should
show up at the beginning or end of this new date list.



Thanks buckets for your help, Howard.  This works just dandy.

-Adam

In article <yYG5f.104$uo3.36937@newshog.newsread.com>, Adam Price
<Adam-price@rcn.com> wrote:

> In article <kDF5f.103$uo3.36794@newshog.newsread.com>, Adam Price wrote:
> > In article <11le55sfcqs3q3d@corp.supernews.com>, Howard Schlossberg wrote:
> >>
> >> Seems to me you'd do well with taking your 19750127 text field (I'll 
> >> call it OrigDate) and using it to perform a replace for all records 
> >> within a date field...something like:
> >> 
> >> date(
> >>    middle(OrigDate, 5, 2),
> >>    middle(OrigDate, 7, 2),
> >>    left(OrigDate, 4)
> >> )
> > 
> > Sorry - I am a newbie here///
> > 
> > I understand the syntax of what you've given me, but I don't know what
> > to do with it.
> 
> Answered my own question:
> 
> Here goes: In order to convert a field of numbers in the format
> yyyymmmmddd (we'lls call it date_old) to a date field with the format
> mm/dd/yy (date_new)
> 
> 1.  Make sure that date_old is defined as a *text* field.  This is very
> important, and is why this didn't work for me at first.

Date_Old can be defined as a Text or Number field. Either will work
because FileMaker can interchange these two field types, ie. you can
use Number fields in text functions and Text fields in numerical / math
functions.

But that would have been the cause of your problem since "19750127" is
not obvious as a date.


Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

And you can avoid the need for an additional field by using the Replace 
command. Start out with date_old as a text field. Specify a calculated 
result in the replace. Then change date_old to a date field.

Bill

"Adam Price" <Adam-price@rcn.com> wrote in message 
news:yYG5f.104$uo3.36937@newshog.newsread.com...
> In article <kDF5f.103$uo3.36794@newshog.newsread.com>, Adam Price wrote:
>> In article <11le55sfcqs3q3d@corp.supernews.com>, Howard Schlossberg 
>> wrote:
>>> Seems to me you'd do well with taking your 19750127 text field (I'll
>>> call it OrigDate) and using it to perform a replace for all records
>>> within a date field...something like:
>>>
>>> date(
>>>    middle(OrigDate, 5, 2),
>>>    middle(OrigDate, 7, 2),
>>>    left(OrigDate, 4)
>>> )
>>>
>>>
>>
>> Sorry - I am a newbie here///
>>
>> I understand the syntax of what you've given me, but I don't know what
>> to do with it.
>>
> Answered my own question:
>
> Here goes: In order to convert a field of numbers in the format
> yyyymmmmddd (we'lls call it date_old) to a date field with the format
> mm/dd/yy (date_new)
>
> 1.  Make sure that date_old is defined as a *text* field.  This is very
> important, and is why this didn't work for me at first.
>
> 2.  Create a new field (Date_new) that is defined as a calculation.  The
> calculation is as Howard gave me:
>    Date(  Middle(Date_old, 5, 2),
>           Middle(Date_old, 7, 2),
>           Left(Date_old, 4)
> )
> Below the calculation entry box, make sure that "Calculation Result" is 
> "Date"
>
> 3. Save and close the define fields dialog.  Allow FM to calculate all
> those dates.
> 4.  Once they are all calculated, reopen the define fields box
> 5.  Change the field type of Date_new to date
> 6.  Close dialog
> 7.  Sort by the new field in browse view.  Any glaring errors should
> show up at the beginning or end of this new date list.
>
>
>
> Thanks buckets for your help, Howard.  This works just dandy.
>
> -Adam 

In article <201020052154008405%helpful_harry@nom.de.plume.com>, Helpful
Harry wrote:

> In article <yYG5f.104$uo3.36937@newshog.newsread.com>, Adam Price
><Adam-price@rcn.com> wrote:
> 
>> In article <kDF5f.103$uo3.36794@newshog.newsread.com>, Adam Price wrote:
>> > In article <11le55sfcqs3q3d@corp.supernews.com>, Howard Schlossberg wrote:
>> >>
>> >> Seems to me you'd do well with taking your 19750127 text field (I'll 
>> >> call it OrigDate) and using it to perform a replace for all records 
>> >> within a date field...something like:
>> >> 
>> >> date(
>> >>    middle(OrigDate, 5, 2),
>> >>    middle(OrigDate, 7, 2),
>> >>    left(OrigDate, 4)
>> >> )
>> > 
>> > Sorry - I am a newbie here///
>> > 
>> > I understand the syntax of what you've given me, but I don't know what
>> > to do with it.
>> 
>> Answered my own question:
>> 
>> Here goes: In order to convert a field of numbers in the format
>> yyyymmmmddd (we'lls call it date_old) to a date field with the format
>> mm/dd/yy (date_new)
>> 
>> 1.  Make sure that date_old is defined as a *text* field.  This is very
>> important, and is why this didn't work for me at first.
> 
> Date_Old can be defined as a Text or Number field. Either will work
> because FileMaker can interchange these two field types, ie. you can
> use Number fields in text functions and Text fields in numerical / math
> functions.
> 
> But that would have been the cause of your problem since "19750127" is
> not obvious as a date.
> 
Right  - I should have said "Make sure date_old in NOT a date field."

In article <5JmdnXW0zqjsPsreRVn-sg@comcast.com>, Bill Marriott wrote:
> And you can avoid the need for an additional field by using the Replace 
> command. Start out with date_old as a text field. Specify a calculated 
> result in the replace. Then change date_old to a date field.
> 
> Bill

How do I access that?  I tried using replace as part of a calculation,
but it fails with a circularity error.

Replace(  Date_sold,0,8,  Date(  Middle(Date_sold, 5, 2),
           Middle(Date_sold, 7, 2),
           Left(Date_sold, 4)
              ))


Understandable.

I would think that there would be some sort of replace command in the
edit menu, but there is only find/replace, which seems to deal with
literals, not the command set.  Is this new functionality since FM6.0?

Yes, sorry.

"Adam Price" <Adam-price@rcn.com> wrote in message 
news:pXN5f.121$uo3.40133@newshog.newsread.com...
> In article <5JmdnXW0zqjsPsreRVn-sg@comcast.com>, Bill Marriott wrote:
>> And you can avoid the need for an additional field by using the Replace
>> command. Start out with date_old as a text field. Specify a calculated
>> result in the replace. Then change date_old to a date field.
>>
>> Bill
>
> How do I access that?  I tried using replace as part of a calculation,
> but it fails with a circularity error.
>
> Replace(  Date_sold,0,8,  Date(  Middle(Date_sold, 5, 2),
>           Middle(Date_sold, 7, 2),
>           Left(Date_sold, 4)
>              ))
>
>
> Understandable.
>
> I would think that there would be some sort of replace command in the
> edit menu, but there is only find/replace, which seems to deal with
> literals, not the command set.  Is this new functionality since FM6.0? 

Adam Price wrote:
> I would think that there would be some sort of replace command in the
> edit menu, but there is only find/replace, which seems to deal with
> literals, not the command set.  Is this new functionality since FM6.0?

There is a Replace command on the Records menu.  This allows you to 
either repeat the literal text in the cur5rent field across all records, 
fill the current field with serial numbers across all records, or 
specify a calc whose results are to be populated across all records.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

In article <pXN5f.121$uo3.40133@newshog.newsread.com>, Adam Price
<Adam-price@rcn.com> wrote:

> In article <5JmdnXW0zqjsPsreRVn-sg@comcast.com>, Bill Marriott wrote:
> > And you can avoid the need for an additional field by using the Replace 
> > command. Start out with date_old as a text field. Specify a calculated 
> > result in the replace. Then change date_old to a date field.
> > 
> > Bill
> 
> How do I access that?  I tried using replace as part of a calculation,
> but it fails with a circularity error.
> 
> Replace(  Date_sold,0,8,  Date(  Middle(Date_sold, 5, 2),
>            Middle(Date_sold, 7, 2),
>            Left(Date_sold, 4)
>               ))
> 
> Understandable.
> 
> I would think that there would be some sort of replace command in the
> edit menu, but there is only find/replace, which seems to deal with
> literals, not the command set.  Is this new functionality since FM6.0?

The Replace command can be found in the Records menu.

But  
    *** BE VERY CAREFUL **

There's no Undo, so it's very easy to destroy data using the Replace
command and you should always MAKE A BACKUP FIRST (or at least test on
a very small Found Set that you can manually repair).

The process is:
    
     - click in the field you want to use Replace on

     - if you want to Replace the field's data in all found 
       records with the same static data, type the new static
       data into the field

     - choose Replace from the Records menu

     - in the window that appears you will see that it has
       defaulted to "replace contents of FieldX with {typed data}"

     - if that is correct you can click Replace and FileMaker will
       enter the typed data into that field for EVERY record in the
       Found Set

     - other options in the window allow you to replace the data
       with either a Serial number (which increments with each
       record) or using a calculation.

** ALWAYS DOUBLE-CHECK ***
Make sure the Replace is doing EXACTLY what you want.


In your case you can click in the existing Date_sold field and use the
calculation option with

      Date( Middle(Date_sold, 5, 2),
            Middle(Date_sold, 7, 2),
            Left(Date_sold, 4)
          )


Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

In article <211020050854570556%helpful_harry@nom.de.plume.com>, Helpful
Harry wrote:

> In article <pXN5f.121$uo3.40133@newshog.newsread.com>, Adam Price
><Adam-price@rcn.com> wrote:
> 
>> In article <5JmdnXW0zqjsPsreRVn-sg@comcast.com>, Bill Marriott wrote:
>> > And you can avoid the need for an additional field by using the Replace 
>> > command. Start out with date_old as a text field. Specify a calculated 
>> > result in the replace. Then change date_old to a date field.
>> > 
>> > Bill
>> 
>> How do I access that?  I tried using replace as part of a calculation,
>> but it fails with a circularity error.
>> 
>> Replace(  Date_sold,0,8,  Date(  Middle(Date_sold, 5, 2),
>>            Middle(Date_sold, 7, 2),
>>            Left(Date_sold, 4)
>>               ))
>> 
>> Understandable.
>> 
>> I would think that there would be some sort of replace command in the
>> edit menu, but there is only find/replace, which seems to deal with
>> literals, not the command set.  Is this new functionality since FM6.0?
> 
> The Replace command can be found in the Records menu.
> 
> But  
>     *** BE VERY CAREFUL **


Crap.  Don't I know it.  I messed this up this morning, because it
actually wasn't my database, it was my mother's, and we made some little
mistake somewhere doing this over the phone.  She isn't that computer
savvy, and we spent three hours trying to find a backup - in fact we
can't even find the one we are working on on the hard drive - I think my
dad had opened it directly out of mail and never re-saved it.

We looked for four hours over the phone, then gave up.

We are having to go back to a version from a few days ago - not the end
of the world, but very frustrating.


I am not used to the FM system where changes are saved instantly - I was
expecting that if we made a mistake, we would close without saving. 
oops.


Not your (collective) faults, though - I do appreciate your help.

-Adam

Hey, it is almost impossible to help non savvy people without some visual
interface. I helped my Dad a lot and Timbuktu is a great program for seeing
their screen and helping. Saves a lot of hair from being pulled out.
Just my 2�.


On 10/20/05 4:19 PM, in article 6ZS5f.123$uo3.43685@newshog.newsread.com,
"Adam Price" <Adam-price@rcn.com> wrote:

> Crap.  Don't I know it.  I messed this up this morning, because it
> actually wasn't my database, it was my mother's, and we made some little
> mistake somewhere doing this over the phone.

~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.com



----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----