Update a date field

0
votes

Date Field Name: EmailDate, Table Name: MissingT, Memo Field Name: Worklog.

I am trying to query the worklog and find a word "Effective date: " and get the rest of the characters on that same line following "Effective date: ". Then update "EmailDate" field with it. 

UPDATE MissingT SET MissingT.Emaildate = mid([Worklog],(InStr(1,[Worklog],'Effective Date: ')+16),13)
WHERE (((MissingT.[worklog]) Like  "*" & 'Effective Date: ' & "*" ));

I am getting a conversion error message.

Also, is there anyway to search the Worklog memo field from bottom up, rather than from top down?. I created another field called "TempD" and get the date in there first. So the date in TempD filed would look like "Wed, Dec 3, 2014". Then I did the query to update the emaildate with cdate function as below: UPDATE MissingT SET MissingT.emaildate = cdate(Format(Right([tempd],4) & "/" & Mid([tempd],6,3) & "/" & Mid([tempd],9,2))); ........But I am still getting conversion failure.

1
ms-accesssql-updatevba

1 Answers

1
votes

This might be a bit more tricky than your previous problem - Fnd 2nd occourance of a string in a Memo Field

Traveling bottom up, use StrReverse function.

? StrReverse("Hello World!")
!dlroW olleH

However if you wish to search a particular String's starting position, then you can use the InStrRev function, 

? InStrRev("Hello World! It is a wonderful day. It truly is", "It")
 37 
? InStr("Hello World! It is a wonderful day. It truly is", "It")
 14

The error you have is because Mid returns a String, but my understanding is that the field EmailDate is a Date type. So you need to wrap it using CDate or CDbl function to treat it as Date.