Extract and use date from memo field 1

[Cardstang]

Hello all,

I'm using CR 2008 (Version 12) and I'm trying to extract a date from a memo field and then use the results in a formula.

The date is always after the words "REC DATE" in the memo field. I'm able to pull the date out by using the following formula:

mid(mid({memo.field},instr({memo.field},"REC DATE")+9),1,10)

The result seems to be a text field and is in the format of "dd/mm/yyyy". I don't have any issues extracting the date, but it's in a format that is unusable in formulas.

I'm trying to use the result in a formula that will calculate the time between an existing date field and the resulting field extracted from the memo field.

I've tried to convert it using cdate, but I receive an error 'Bad Date Format String'.

Any help is appreciated!

Thanks!

 

[lbass]

You should be able to simply wrap your formula in date(), but because you are getting the error, I think you need to check how your formula results display when placed in the detail section. Check to see if the memo field is sometimes null or if there are cases where there is no "REC DATE". Also check whether dates are always entered in the dd/MM/yyyy format--or whether sometimes they are entered like this: d/M/yyyy, for example.

-LB

 

[Cardstang]

The dates are always in the MM/dd/yyy format. And I did find some without a REC DATE.

I wrapped the formula in date(), and I still received the same error.

I added a check to see if it is null or not numeric to where it looks like the following:

if isnull({@CAPS}) or not(isnumeric({@CAPS}))then currentdate 
else

date(mid(mid({@CAPS},instr({@CAPS},"REC DATE")+9),1,10))

Every result comes back as the current date, so it's seeing the field as a text field, correct?

 

[lbass]

You shouldn't be checking for isnumeric, since "/" isn't numeric. Remove that and see what results you get.

-LB

 

[Cardstang]

I forgot to mention...thanks for the quick reply to my original post. And thanks for the quick reply to this one too.

Removing the check for numeric give the the same error.

if isnull({@CAPS}) then currentdate 
else

date(mid(mid({@CAPS},instr({@CAPS},"REC DATE")+9),1,10))

The formula @CAPS is to change to uppercase all of the memo field.

 

[lbass]

What do you mean? What result do you get?

Also, you really need to look at how the results display in the record selection formula--before you wrap it in date().

-LB

 

[Cardstang]

I receive the error 'Bad Date Format String' when I wrap it with date().

If the memo field contains "REC DATE", then the result is a date (ie. 06/02/2011), but it seems to read as a text field. If the field does not contain "REC DATE", the result is a random string of text 10 characters long.

If the date in the memo field (that follows "REC DATE") was in a different order (such as yyyy/mm/dd), would it make a difference? I can control how the date is entered in the memo field.

Thanks again for the quick responses.

 

[lbass]

No, the key thing is "If the field does not contain "REC DATE".

Change your formula to:

if isnull({@CAPS}) or
instr({@CAPS},"REC DATE") = 0 then
currentdate else
date(mid(mid({@CAPS},instr({@CAPS},"REC DATE")+9),1,10))

-LB

 

[Cardstang]

That did the trick! Thanks so much for your assistance.

 

[Cardstang]

Hello again,

The above formula has been doing the trick...no problems with it whatsoever.

Something I'd like to see if I can add to this is to add the time also.

The default time is going to be 00:00:00. If have an integer in the text field representing an hour, can I add that into the extraction and use it as a datetime?

For example, the current formula is:

date(mid(mid({@CAPS},instr({@CAPS},"REC DATE")+9),1,10))

If the 19th and 20th position after "REC DATE" is "15", can this also be extracted and become part of the forumla?

Here's what I've tried so far (with a resulting bad date/time format error)

datetime(mid(mid({@CAPS},instr({@CAPS},"REC DATE")+9),1,10)& Mid(MID({@CAPS}, INSTR({@CAPS},"REC DATE")+19),1,2)&":00:00")

Thanks in advance for the help!

 

[lbass]

Can you please show a sample of the memo field itself so that I can see the layout? Why would the date and have hours and not minutes and seconds displayed?

-LB

 

[Cardstang]

Here is a sample of the memo field:

Rec date 08/10/2011 15
gobbledygook, sentences, words, 
sentences, words, gobbledygook

Currently, it's just extracting the date. The folks entering the data in the memo field are only entering the date. I'm just trying to see if there is an easy way for them to add a time to it. By them only entering the hour, that makes things a bit easier for them. Could this work if they entered:
Rec date 08/10/2011 15:00:00 ?

I dont' have any entries in that format to mess around with.

 

[lbass]

Is this August 10 or October 8? If August 10, then you could just use:

datetime(mid({table.memo},10,13)+":00:00")

...assuming the memo starts with "Rec Date".

-LB

 

[Cardstang]

Hello LB,

That worked just great!

I'm going to try to write it so that I'll get it to pull the time if there is actually an entry for the time, and if not, then just fall to the default time. Right now, not every record has that entry and I'm now getting bad date/time format errors. I'll try doing it myself. I may be back for assistance.

Thanks for your help!