Extract data from string

[June7]

Ouch! Abbreviated version:

SELECT [Serviceability Project Data].*, [tbl01_Cust Closed Reasons].[Cust Closed Reasons Status],
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],
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],
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];

Not seeing the IIf() you said was used to handle lack of "Reopened on " text.

[johnny51981]

I have columns in a very specific order that is different than what is provided to me, which is why all of the columns are visible in the query. But I get what you are saying on that part. HA!

I've been working on this query off and on, so I had the iif in there for the lack of "ReOpened on " text, but have since removed it (clearly).

Ok, adding this causes there to be a #Error on those that don't have the "ReOpened on"

[June7]

Why does field order matter in query?

Would like to have seen how you structured the IIf().

Something like:

IIf(InStr([Proj Close Reason Note], "Reopened on ")=0, Null, CDate(Mid([Proj Close Reason Note],InStr([Proj Close Reason Note],"ReOpened on ")+12))) AS [Project ReOpen Date Text]

[johnny51981]

The query results are exported to an Excel report. Some of the fields from the table are excluded or in a different order than they are in in the table. And the Excel report has some Cell Reference specific components.

My IIf() didn't look anything like what you have provided, however I am still getting the #Error from using what you have provided. It is still giving me a return on those that have the "ReOpened On " in the Proj Close Reason Note, and it is a date. But where it does not have it, is where I get the #Error.

[June7]

According to Allen Browne in http://allenbrowne.com/QueryPerfIssue.html#Nz

(Note: JET's IIf() is much more efficient than the similarly named function in VBA. The VBA one wastes time calculating both the True and False parts, and generates errors if either part does not work out (even if that part is not needed.) The JET IIf() does not have these problems.)

So unless he is wrong, I do not understand why you would still get the error.

Is it possible the field could have the "ReOpened On " text and not be followed by a date string or there is something after the date string?

Consistency is critical with string manipulation. Would hate to have to go to a custom function to deal with this.

[johnny51981]

I filtered the raw data and all of the "ReOpened On" texts are followed by a date with nothing following the date.

I had to get a little sneaky with it, but got it to work by creating a YES/NO statement if the [Proj Close Reason Note] contained the "ReOpened on ". Then added an additional Iif layer to what you provided to me to say IF the new YES/NO statement contained "YES", then give me the date. If "NO" then just do nothing. So, those pieces look like this:

IIf([Proj Close Reason Note] Like "*ReOpened on *","YES","NO") AS [Project ReOpened],
IIf([Project ReOpened]="YES",IIf(InStr([Proj Close Reason Note],"Reopened on ")=0,Null,CDate(Mid([Proj Close Reason Note],InStr([Proj Close Reason Note],"ReOpened on ")+12)))) AS [Project ReOpen Date]

Ran some test filters within the Datasheet view and all of Proj Close Reason Notes that contain the "ReOpened on " are flagged as YES and have the ReOpened Date populated. Those that don't are flagged as NO and null in the ReOpened Date column.

I think this [finally] solves my need...though probably not as streamlined as it could be.

Thanks again for your assistance!!!