Separating data into 2 separate fields

[Turborekker]

Hi Guys,

I am very new to Filemaker and am busy setting up a simple database. I have created 3 fields: 1) Date Time, 2) Date and 3) Time. I have a comma-delimmeted txt file with data that I have imported into this database (the date and time information from this txt file has gone into the "Date Time" field in the database. I need to separate the date information into the "Date" field and the time info into the "Time" field. I know I need to use a script which uses the left field command. Please could you advise me on how to go about this. I am using FM Pro 8.

Thanks

Shaun

[mr_vodka]

What is the exact format of the imported data?

[Turborekker]

?O,0828984177, ,0:00, 1, 12, 5437, 0,2007/03/15 07:52, 0:59, 1.89;

As you can see above the date and time are in the same "separated" group. I need to import the date and time into 2 separate fields.

[Raybaudi]

Hi

if the text contais always the same number of commas

and

if the time is expressed as hh:mm

then:

calculation for date field:

Let ([

value = GetValue ( Substitute ( date time ; "," ; ¶ ) ; 9 ) ;

dat = LeftWords ( value ; 1 );

d = Right ( dat ; 2 ) ;

m = Middle ( dat ; 6 ; 2 ) ;

y = Left ( dat ; 4 )

];

Date ( m ; d ; y )

)

calculation for time field:

Let ([

value = GetValue ( Substitute ( date time ; "," ; ¶ ) ; 9 ) ;

h = MiddleWords ( value ; 2 ; 1 ) ;

m = RightWords ( value ; 1 )

];

Time ( h ; m ; 0 )

)

[Turborekker]

Great, thanks alot for your help, I'll give this a go.