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:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 (AT) corp (DOT) supernews.com>, Howard Schlossberg wrote:

Adam Price wrote:
As far back as FileMaker 2, I believe.

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

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

In article <11le55sfcqs3q3d (AT) corp (DOT) supernews.com>, Howard Schlossberg wrote:
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

In article <kDF5f.103$uo3.36794 (AT) newshog (DOT) newsread.com>, Adam Price wrote:

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 (AT) newshog (DOT) newsread.com>, Adam Price
<Adam-price (AT) rcn (DOT) com> wrote:

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 (AT) rcn (DOT) com> wrote

In article <201020052154008405%helpful_harry (AT) nom (DOT) de.plume.com>, Helpful
Harry wrote:

In article <5JmdnXW0zqjsPsreRVn-sg (AT) comcast (DOT) com>, Bill Marriott wrote:
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?