Splitting text field into date field and text field 1

[t16turbo]

HI guys,

I have a text field that contains a date/time and a text string.
e.g.:


'28/06/2005 13:45:23 This is an exmaple of the field'

I need to split this into the two parts, a date/time field and a text field.
I'm assuming I need to create 2 formula fields for this, but I'm not sure of the code required in each to get the diofferent parts.
I'd like the date/time part to be recognised as a Date.

can anybody help?

thanks,

 

[ShortyA]

t16turbo,
you could try :

@Formula1
// This will use chars 1 to 19 to make a datetime variable
datetimevalue (mid({MyTable.MyField},1,19));

@Formula2
// This will extract from 20 for another 17 chars({Table1.StringVar},20,17));
// You could create a SQL expression for this bit if you wanted and use SUBSTRING instead of MID

Hope this helps.
ShortyA

 

[t16turbo]

Thanks ShortyA,

I put this in, but it appears that I have some duff data somewhere, as a couple of the fields don't have the date part e.g.

'- Requirements'
being one example

Is there anyway I can check for the prescence of maybe the first two characters being numbers or similar to ensure that your datetimevalue formula works on correct strings?

 

[ShortyA]

You could try:

@Formula1
// This will use chars 1 to 19 to make a datetime variable
if mid({MyTable.MyField},1,1) in ['1','2','3','4','5','6','7','8','9','0'] then
datetimevalue (mid({MyTable.MyField},1,19)) else datevalue(1901,01,01);

It is a bit messy but it checks that the first character is a valid integer. If it is then the date is extracted and if it isn't it pushes out a very low date (it must be a date though so you cannot put in 'BAD DATE')

ShortyA

 

[t16turbo]

Thanks Shorty,

thats great!