Extract data from string

[johnny51981]

Ref: https://www.accessforums.net/queries...memo-5542.html I have a very similar need. I am using an Access Database that is linked to an Excel data export as I don't have a direct link to the application nor does the application have reporting services.



I need to pull a date that is listed within a comment/memo field. So far, I am finding that it is always the final statement within the field and is preceded by "ReOpened on " and then the date. Downside is that the date format is m/d/yyyy, so I can't just do a straight up LEFT expression.

I have to snag that date so I can use it as a start in some aging metrics. Any thoughts?

[pbaldy]

Check out the InStrRev() function to find "ReOpened on " and use that in the Mid() function.

[CJ_London]

perhaps provide an example string to clarify but in principle it would be

mydate=mid(memostring,instrrev(memostring,"ReOpene d on ")+13)

[tb1150]

Is the problem here with the format of the date you're getting?

[June7]

Note that I split your post into a new thread. Should not 'hijack' threads. Always start your own thread with your question. For one thing, it will get more attention as a new question than as a reply to an existing thread.

Does any text follow the date string?

x represents the field, try:
Mid(x, InStr(x, "ReOpened on ")+12)

EDIT: Ajax, I tried +13 and that lost the month part.

[johnny51981]

Note that I split your post into a new thread. Should not 'hijack' threads. Always start your own thread with your question. For one thing, it will get more attention as a new question than as a reply to an existing thread.

Does any text follow the date string?

x represents the field, try:
Mid(x, InStr(x, "ReOpened on ")+12)

EDIT: Ajax, I tried +13 and that lost the month part.

This helped. I did have to include an IIF to weed out the #ERRORs that came up for those that did not contain the "ReOpened on " piece.

Apologies for the "hijack". I felt that it was the same issue and did not want to duplicate posts. Thank you for your assistance!!!

[CJ_London]

Can't count

[June7]

Same/similar topic or not, you did not see solution from first thread and if you want to get most attention to your question, start new thread. Extracting value from string is a common topic in general but there seem to be infinite variations on specific issue.

[johnny51981]

Different forums, different rules. Thanks again!

[June7]

Duplicate topic threads from same poster would not be appreciated. In other words, don't post the same issue to multiple threads in multiple subforums. Nor is the same question to other forum sites appreciated without letting readers know you have done that.

Welcome to the site!

[johnny51981]

Ok, now my challenge is to change it from Text to Date. Any thoughts?

[June7]

Use CDate() function.

CDate(Mid(x, InStr(x, "ReOpened on ")+12))

[johnny51981]

And to exclude the #Error?

[June7]

I thought you already handled the error. Post your query for analysis.

[johnny51981]

SELECT [Serviceability Project Data].Region, [Serviceability Project Data].KMA, [Serviceability Project Data].[Project  ID], [Serviceability Project Data].[RCJob ID], [Serviceability Project Data].[Location Number], [Serviceability Project Data].[Project Name], [Serviceability Project Data].[Customer Count], [Serviceability Project Data].[Creation Date], [Serviceability Project Data].SRODate, [Serviceability Project Data].[Last Modified By], [Serviceability Project Data].[Last Modified Date], [Serviceability Project Data].Name, [Serviceability Project Data].[House/Address Number], [Serviceability Project Data].[Street Address], [Serviceability Project Data].[Suite/Unit/Apt/Lot #], [Serviceability Project Data].City, [Serviceability Project Data].State, [Serviceability Project Data].Zip, [Serviceability Project Data].[Home Phone], [Serviceability Project Data].[Cell Phone], [Serviceability Project Data].[Other Phone], [Serviceability Project Data].[Primary Email], [Serviceability Project Data].[Secondary Email], [Serviceability Project Data].[CPR Number], [Serviceability Project Data].[Justification Note Coordinator], [Serviceability Project Data].[Justification Note Manager], [Serviceability Project Data].Manager, [Serviceability Project Data].Coordinator, [Serviceability Project Data].[Cost Per Customer], [Serviceability Project Data].[Co Pay], [Serviceability Project Data].[Sent To Manager Date], [Serviceability Project Data].[Manager Decision Date], [Serviceability Project Data].[Manager Decision Status], [Serviceability Project Data].[EVP Threshold - Based on Cust Count], [Serviceability Project Data].[Sent To EVPDate], [Serviceability Project Data].[EVPDecision Date], [Serviceability Project Data].[EVPDecision Status], [Serviceability Project Data].[EVPResponse Note], [Serviceability Project Data].[Aerial Plant Miles], [Serviceability Project Data].[UGPlant Miles], [Serviceability Project Data].[Extension Mileage], [Serviceability Project Data].[Actual Activation Date], [Serviceability Project Data].Node, [Serviceability Project Data].Latitude, [Serviceability Project Data].Longitude, [Serviceability Project Data].[Survey Date], [Serviceability Project Data].[Survey Note], [Serviceability Project Data].[Projected Serviceability Note], [Serviceability Project Data].[Estimated Construction Start Date], [Serviceability Project Data].[Construction Start Date], [Serviceability Project Data].[Construction Start Note], [Serviceability Project Data].[Construction Complete Date], [Serviceability Project Data].[Construction Complete Note], [Serviceability Project Data].[Scheduled Install Pending], [Serviceability Project Data].[Scheduled Install Date], [Serviceability Project Data].[Scheduled Install Note], [Serviceability Project Data].[Install Date Note], [Serviceability Project Data].[Actual Install Date], [Serviceability Project Data].CSGSystem, [Serviceability Project Data].CSGPrin, [Serviceability Project Data].[Franchise Agent], [Serviceability Project Data].[Labor Costs], [Serviceability Project Data].[Material Costs], [Serviceability Project Data].[Additional Costs], [Serviceability Project Data].[Total Costs], [Serviceability Project Data].[Customer Close Date], [Serviceability Project Data].[Cust Close Date Note], [Serviceability Project Data].[Cust Closed Reason], [Serviceability Project Data].[Cust Closed Reason Note], [Serviceability Project Data].[Project Close Date], [Serviceability Project Data].[Proj Close Reason Note], [Serviceability Project Data].[Actual Total Labor], [Serviceability Project Data].[Actual Total Materials], [Serviceability Project Data].[Actual Total Other], [Serviceability Project Data].[Labor PO], [Serviceability Project Data].[Labor PODate], [Serviceability Project Data].[Design PO], [Serviceability Project Data].Supervisor, [Serviceability Project Data].[Construction Contract Company], [Serviceability Project Data].[Survey Expiration], [Serviceability Project Data].[Account Number], [Serviceability Project Data].[Order Number], [Serviceability Project Data].[Number of date Changes], [Serviceability Project Data].[Original Estimated Construction Start Date], [Serviceability Project Data].[Original Estimated Construction Days], [Serviceability Project Data].[Original Estimated Construction Complete Date], [Serviceability Project Data].[Last Update Estimated Construction Start Date], [Serviceability Project Data].[Last Update Estimated Construction Days], [Serviceability Project Data].[Last Update Estimated Construction Complete Date], IIf([Manager] Is Null,"Unclaimed","Claimed") AS [Workbook Status], IIf([Sent To Manager Date]>0,([Sent To Manager Date]),IIf([Project Close Date]>0,([Project Close Date]),IIf([Sent To Manager Date] And [Project Close Date]="",""))) AS [Completed Survey Date], IIf([Completed Survey Date]<>0,"COMPLETE","PENDING") AS [Survey Status], IIf([Completed Survey Date]<>0,[Completed Survey Date]-[SRODate],(Date()-[SRODate])) AS [Survey Aging], IIf([Survey Aging]=3,"SLA",IIf([Survey Aging]<3,"Less Than SLA",IIf([Survey Aging]>3,"Greater Than SLA",""))) AS [Survey Aging Category], IIf([Sent To Manager Date]<>0,IIf([Construction Complete Date]>0,"COMPLETE","PENDING"),"N/A") AS [Construction Status], IIf([Sent To Manager Date]>0,IIf([Construction Complete Date] Is Null,(Date()-[Sent To Manager Date]),[Construction Complete Date]-[Sent To Manager Date]),0) AS [Construction Aging], IIf([Project Close Date] Is Null,"Open",IIf([Project Close Date] Is Not Null And [Cust Closed Reasons Status]="Constructed","Closed","Cancelled")) AS [SRO Status], [tbl01_Cust Closed Reasons].[Cust Closed Reasons Status], IIf([Project Close Date]<>0,([Project Close Date]-[SRODate]),(Date()-[SRODate])) AS [SRO Aging], IIf([Total Costs]=0,"",IIf([Total Costs]>=5000,"Greater Than $5K","Less Than $5K")) AS [Cost Category], [Serviceability Project Data].[Pole Licensing Costs], CDate(Mid([Proj Close Reason Note],InStr([Proj Close Reason Note],"ReOpened on ")+12)) AS [Project ReOpen Date Text]FROM [tbl01_Cust Closed Reasons] RIGHT JOIN [Serviceability Project Data] ON [tbl01_Cust Closed Reasons].[Cust Closed Reason] = [Serviceability Project Data].[Cust Closed Reason];