 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006Parsing memo field into texy fields
I need to separate the text in a memo field by the psuedo field headings (not my design) A portion of the memo field looks like this Date(mm/dd/yy): Time(23:15): Event Summary: Tigger met?(Y is yes): and so on... I'm using queries with trims, mids, and instr to break this text out into mutliple fields. Please let me know if there is a better way Here's the the queries SQL:INSERT INTO tbltmpParsed ( PlantName, ProductionUnit, RecordID, Author, [Event Date], [Event Time], [Equipment Description], Summary, Rci, [RCI Triggers], GUPE, FUPE, OUPE, NM, CriC, SUSD, EPRelease, CAS, SL, ce, a.ses, Audit, ENV, safe, [proc], qual, reli, AU, secu, Success, OTJ, Reported, Projected, [Pos Cause], [Act Res Ver], [Ver Cause to Complete], [Actions Completed], [Additional Actions], DiscMeth, CompType, FugTagNo, ProcStream, GovReg, CompLocDesc, [Verify Tag], [Repair Att], [Repair Att Dt], [Repair Mthd], [Leak Stop], [Leak Still], Comments, Status, UniqueID, ModifyDT, Equipment, NoteType ) SELECT tblObservation1.PlantName, tblObservation1.ProductionUnit, tblObservation1.RecordID, tblObservation1.Author, IIf(IsDate(Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event Date",1)+22,12))),Format(Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event Date",1)+22,12)),"Short Date"),Format([tblObservation1]![CreateDT],"Short Date")) AS [Event Date], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event Time",1)+25,InStr(1,[tblObservation1]![Observation],"Equipment Description",1)-(InStr(1,[tblObservation1]![Observation],"Event Time",1)+25))) AS [Event Time], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Equipment Description",1)+23,InStr(1,[tblObservation1]![Observation],"Event Summary",1)-(InStr(1,[tblObservation1]![Observation],"Equipment Description",1)+23))) AS [Equipment Description], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event Summary",1)+14,InStr(1,[tblObservation1]![Observation],"RCI Trigger Met",1)-(InStr(1,[tblObservation1]![Observation],"Event Summary",1)+14))) AS Summary, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"RCI Trigger Met",1)+28),5)),1)="Y",-1,0) AS Rci, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List RCI Triggers Met",1)+22,InStr(1,[tblObservation1]![Observation],"Global UPE Trigger Met",1)-(InStr(1,[tblObservation1]![Observation],"List RCI Triggers Met",1)+22))) AS [RCI Triggers], IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Global UPE Trigger Met",1)+35),5)),1)="Y",-1,0) AS GUPE, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Facility UPE Trigger Met",1)+37),5)),1)="Y",-1,0) AS FUPE, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Other UPE trigger met",1)+34),5)),1)="Y",-1,0) AS OUPE, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Near Miss",1)+22),5)),1)="Y",-1,0) AS NM, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Crisis Criteria Met",1)+32),5)),1)="Y",-1,0) AS CriC, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Start Up/Shut Down Applicable",1)+44),5)),1)="Y",-1,0) AS SUSD, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"EP Release Report? (Y if yes):",1)+30),5)),1)="Y",-1,0) AS EPRelease, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List CAS No(s)",1)+33,InStr(1,[tblObservation1]![Observation],"Sensory Leak",1)-(InStr(1,[tblObservation1]![Observation],"List CAS No(s)",1)+33))) AS CAS, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Sensory Leak? (Y if yes and fill out below):",1)+44),5)),1)="Y",-1,0) AS SL, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Contractor Event",1)+30),5)),1)="Y",-1,0) AS ce, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Assessment? (Y if yes):",1)+24),5)),1)="Y",-1,0) AS a.ses, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Audit? (Y if yes):",1)+19),5)),1)="Y",-1,0) AS Audit, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Environmental? (Y if yes):",1)+27),5)),1)="Y",-1,0) AS ENV, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Personal Safety? (Y if yes)",1)+29),5)),1)="Y",-1,0) AS safe, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Process Safety? (Y if yes)",1)+29),5)),1)="Y",-1,0) AS [proc], IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Quality? (Y if yes):",1)+21),8)),1)="Y",-1,0) AS qual, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Reliability? (Y if yes)",1)+25),7)),1)="Y",-1,0) AS reli, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Asset Utilization? (Y if yes)",1)+31),7)),1)="Y",-1,0) AS AU, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Security? (Y if yes)",1)+22),7)),1)="Y",-1,0) AS secu, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Success Analysis? (Y if yes)",1)+30),7)),1)="Y",-1,0) AS Success, IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Off The Job? (Y if yes)",1)+25),7)),1)="Y",-1,0) AS OTJ, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event Reported by",1)+36,InStr(1,[tblObservation1]![Observation],"Projected $ Impact",1)-(InStr(1,[tblObservation1]![Observation],"Event Reported by",1)+36))) AS Reported, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Projected $ Impact",1)+19,InStr(1,[tblObservation1]![Observation],"List Possible causes",1)-(InStr(1,[tblObservation1]![Observation],"Projected $ Impact",1)+19))) AS Projected, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List Possible causes",1)+37,InStr(1,[tblObservation1]![Observation],"List action",1)-(InStr(1,[tblObservation1]![Observation],"List Possible causes",1)+37))) AS [Pos Cause], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List action and results",1)+52,InStr(1,[tblObservation1]![Observation],"List verified cause",1)-(InStr(1,[tblObservation1]![Observation],"List action and results",1)+52))) AS [Act Res Ver], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List verified cause",1)+42,InStr(1,[tblObservation1]![Observation],"List actions completed",1)-(InStr(1,[tblObservation1]![Observation],"List verified cause",1)+42))) AS [Ver Cause to Complete], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List actions completed ",1)+59,InStr(1,[tblObservation1]![Observation],"List Additional Actions",1)-(InStr(1,[tblObservation1]![Observation],"List actions completed ",1)+59))) AS [Actions Completed], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List Additional Actions",1)+58,InStr(1,[tblObservation1]![Observation],"-------------",1)-(InStr(1,[tblObservation1]![Observation],"List Additional Actions",1)+58))) AS [Additional Actions], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Discovery Method",1)+54,InStr(1,[tblObservation1]![Observation],"Component Type",1)-(InStr(1,[tblObservation1]![Observation],"Discovery Method",1)+54))) AS DiscMeth, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Component Type",1)+51,InStr(1,[tblObservation1]![Observation],"Fugitive Tag",1)-(InStr(1,[tblObservation1]![Observation],"Component Type",1)+51))) AS CompType, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Fugitive Tag",1)+31,InStr(1,[tblObservation1]![Observation],"Process Stream",1)-(InStr(1,[tblObservation1]![Observation],"Fugitive Tag",1)+31))) AS FugTagNo, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Process Stream",1)+26,InStr(1,[tblObservation1]![Observation],"Governing Regulation",1)-(InStr(1,[tblObservation1]![Observation],"Process Stream",1)+26))) AS ProcStream, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Governing Regulation",1)+32,InStr(1,[tblObservation1]![Observation],"Component Location",1)-(InStr(1,[tblObservation1]![Observation],"Governing Regulation",1)+32))) AS GovReg, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Component Location",1)+31,InStr(1,[tblObservation1]![Observation],"Initial to Verify",1)-(InStr(1,[tblObservation1]![Observation],"Component Location",1)+31))) AS CompLocDesc, Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Initial to Verify",1)+36,InStr(1,[tblObservation1]![Observation],"Repair Attempt made",1)-(InStr(1,[tblObservation1]![Observation],"Initial to Verify",1)+36))) AS [Verify Tag], IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Repair Attempt made (Y if yes)",1)+32),7)),1)="Y",-1,0) AS [Repair Att], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Date Repair Attempt",1)+42,InStr(1,[tblObservation1]![Observation],"Repair Method",1)-(InStr(1,[tblObservation1]![Observation],"Date Repair Attempt",1)+42))) AS [Repair Att Dt], Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Repair Method",1)+36,InStr(1,[tblObservation1]![Observation],"Was leak stopped",1)-(InStr(1,[tblObservation1]![Observation],"Repair Method",1)+36))) AS [Repair Mthd], IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Was leak stopped?",1)+50),7)),1)="Y",-1,0) AS [Leak Stop], IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"If still leaking",1)+71),7)),1)="Y",-1,0) AS [Leak Still], Right([tblObservation1]![Observation],(Len([tblObservation1]![Observation])-InStr(1,[tblObservation1]![Observation],"Comments",1)-8)) AS Comments, tblObservation1.Status, tblObservation1.UniqueID, tblObservation1.ModifyDT, tblObservation1.Equipment, tblObservation1.NoteType FROM tblObservation1 ORDER BY tblObservation1.PlantName;  SignatureNita J. Perez > I need to separate the text in a memo field by the psuedo field headings (not > my design) [quoted text clipped - 10 lines] > mutliple fields. > Please let me know if there is a better way It would appear you are relying on the pseudo field names always being present and in the same order e.g. "Event Time" is always followed by "Equipment Description" (or whatever) with the data element for "Event Time" found between the two. Correct? It may be possible to model the pseudo field names plus their order in a two column table e.g. ('Event Date', 'Event Time') ('Event Time', 'Equipment Description') ('Equipment Description', 'Event Summary') etc You could then use this table to parse out the values. I'd have to make a number of assumptions about delimiters etc so, rather than a solution, here's a quick demo to give you some ideas: CREATE TABLE Test ( key_col INTEGER NOT NULL UNIQUE, data_col MEMO NOT NULL ) ; INSERT INTO Test VALUES ( 1, 'Event Date: 2006-01-01 Event Time: 23:59:59 Equipment Description: Crampons Event Summary: Mountain rescue') ; INSERT INTO Test VALUES ( 2, 'Event Date: 2006-08-02 Event Time: 13:23:25 Equipment Description: Sandwich box Event Summary: Lunch') ; CREATE TABLE Pseudos ( pseudo_name_1 VARCHAR(30) NOT NULL, pseudo_name_2 VARCHAR(30) NOT NULL) ; INSERT INTO Pseudos VALUES ('Event Date', 'Event Time') ; INSERT INTO Pseudos VALUES ('Event Time', 'Equipment Description') ; INSERT INTO Pseudos VALUES ('Equipment Description', 'Event Summary') ; And the query: SELECT DT1.key_col, DT1.data_col, DT2.pseudo_name_1, DT2.pseudo_name_2, MID$(DT1.data_col, INSTR(1, DT1.data_col, DT2.pseudo_name_1) + LEN(DT2.pseudo_name_1), INSTR(INSTR(1, DT1.data_col, DT2.pseudo_name_1) + LEN(DT2.pseudo_name_1), DT1.data_col, DT2.pseudo_name_2) - INSTR(1, DT1.data_col, DT2.pseudo_name_1) - LEN(DT2.pseudo_name_1)) AS data_element FROM ( SELECT key_col, ' ' & data_col & ' ' AS data_col FROM Test ) AS DT1, ( SELECT ' ' & pseudo_name_1 & ': ' AS pseudo_name_1, ' ' & pseudo_name_2 & ': ' AS pseudo_name_2 FROM Pseudos ) AS DT2 The derived tables (DT1 and DT1) are to suffix/prefix the text with delimiters. Here's the VBA to reproduce the above scenario. It creates a new database C:\DropMe.mdb with tables, data and stored query. Run the code, open the db and take a look at the query: Sub pseudos() Dim cat Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\DropMe.mdb" With .ActiveConnection ' Create auxilary table of digits 0-9 .Execute _ "CREATE TABLE Test ( key_col INTEGER NOT" & _ " NULL UNIQUE, data_col MEMO NOT NULL);" .Execute _ "INSERT INTO Test VALUES (1, 'Event Date:" & _ " 2006-01-01 Event Time: 23:59:59 Equipment" & _ " Description: Crampons Event Summary: Mountain" & _ " rescue') " .Execute _ "INSERT INTO Test VALUES (2, 'Event Date:" & _ " 2006-08-02 Event Time: 13:23:25 Equipment" & _ " Description: Sandwich box Event Summary:" & _ " Lunch') " .Execute _ "CREATE TABLE Pseudos ( pseudo_name_1 VARCHAR(30)" & _ " NOT NULL, pseudo_name_2 VARCHAR(30) NOT" & _ " NULL)" .Execute _ "INSERT INTO Pseudos VALUES ('Event Date'," & _ " 'Event Time') " .Execute _ "INSERT INTO Pseudos VALUES ('Event Time'," & _ " 'Equipment Description') " .Execute _ "INSERT INTO Pseudos VALUES ('Equipment Description'," & _ " 'Event Summary') " .Execute _ "CREATE VIEW qryTest AS SELECT DT1.key_col" & _ " AS key_col, DT1.data_col AS data_col, DT2.pseudo_name_1" & _ " AS pseudo_name_1, DT2.pseudo_name_2 AS" & _ " pseudo_name_2, MID$(DT1.data_col, INSTR(1," & _ " DT1.data_col, DT2.pseudo_name_1) + LEN(DT2.pseudo_name_1)," & _ " INSTR(INSTR(1, DT1.data_col, DT2.pseudo_name_1)" & _ " + LEN(DT2.pseudo_name_1), DT1.data_col," & _ " DT2.pseudo_name_2) - INSTR(1, DT1.data_col," & _ " DT2.pseudo_name_1) - LEN(DT2.pseudo_name_1))" & _ " AS data_element FROM ( SELECT key_col," & _ " ' ' & data_col & ' ' AS data_col FROM Test" & _ " ) AS DT1, ( SELECT ' ' & pseudo_name_1" & _ " & ': ' AS pseudo_name_1, ' ' & pseudo_name_2" & _ " & ': ' AS pseudo_name_2 FROM Pseudos )" & _ " AS DT2;" Dim rs Set rs = .Execute( _ "SELECT key_col, pseudo_name_1 AS pseudo_field_name," & _ " data_element FROM qryTest ORDER BY key_col," & _ " pseudo_name_1;") MsgBox rs.GetString rs.Close End With Set .ActiveConnection = Nothing End With End Sub Jamie. -- Jamie, Thanks for the direction, hoever I get and error message when executing the query in the dropme database: Circular reference caused by alias 'data_col' in query definition;s SELECT list. SignatureNita J. Perez > > I need to separate the text in a memo field by the psuedo field headings (not > > my design) [quoted text clipped - 157 lines] > > -- > Thanks for the direction, hoever I get and error message when executing the > query in the dropme database: Circular reference caused by alias 'data_col' > in query definition;s SELECT list. It works for me :) Try this: 1) Open Excel. 2) Navigate the Visual Basic Editor e.g. from the menu, choose: Tools, Macro, Visual Basic Editor. 3) Add a Standard code module e.g. from the menu, choose: Insert, Module. 4) Copy and paste the code (modified slightly, below) into the module. 5) Run the code e.g. from the menu, choose: Tool, Macros and with 'pseudos' selected press the 'Run' button. Here's the code (tweaked): Sub pseudos() On Error Resume Next Kill "C:\DropMe.mdb" On Error GoTo 0 Dim cat Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\DropMe.mdb" With .ActiveConnection ' Create auxilary table of digits 0-9 .Execute _ "CREATE TABLE Test ( key_col INTEGER NOT" & _ " NULL UNIQUE, data_col MEMO NOT NULL);" .Execute _ "INSERT INTO Test VALUES (1, 'Event Date:" & _ " 2006-01-01 Event Time: 23:59:59 Equipment" & _ " Description: Crampons Event Summary: Mountain" & _ " rescue') " .Execute _ "INSERT INTO Test VALUES (2, 'Event Date:" & _ " 2006-08-02 Event Time: 13:23:25 Equipment" & _ " Description: Sandwich box Event Summary:" & _ " Lunch') " .Execute _ "CREATE TABLE Pseudos ( pseudo_name_1 VARCHAR(30)" & _ " NOT NULL, pseudo_name_2 VARCHAR(30) NOT" & _ " NULL)" .Execute _ "INSERT INTO Pseudos VALUES ('Event Date'," & _ " 'Event Time') " .Execute _ "INSERT INTO Pseudos VALUES ('Event Time'," & _ " 'Equipment Description') " .Execute _ "INSERT INTO Pseudos VALUES ('Equipment Description'," & _ " 'Event Summary') " .Execute _ "CREATE VIEW qryTest AS SELECT DT1.key_col" & _ " , DT1.data_col, DT2.pseudo_name_1" & _ " , DT2.pseudo_name_2" & _ " , MID$(DT1.data_col, INSTR(1," & _ " DT1.data_col, DT2.pseudo_name_1) + LEN(DT2.pseudo_name_1)," & _ " INSTR(INSTR(1, DT1.data_col, DT2.pseudo_name_1)" & _ " + LEN(DT2.pseudo_name_1), DT1.data_col," & _ " DT2.pseudo_name_2) - INSTR(1, DT1.data_col," & _ " DT2.pseudo_name_1) - LEN(DT2.pseudo_name_1))" & _ " AS data_element FROM ( SELECT key_col," & _ " ' ' & data_col & ' ' AS data_col FROM Test" & _ " ) AS DT1, ( SELECT ' ' & pseudo_name_1" & _ " & ': ' AS pseudo_name_1, ' ' & pseudo_name_2" & _ " & ': ' AS pseudo_name_2 FROM Pseudos )" & _ " AS DT2;" Dim rs Set rs = .Execute( _ "SELECT key_col, pseudo_name_1 AS pseudo_field_name," & _ " data_element FROM qryTest ORDER BY key_col," & _ " pseudo_name_1;") MsgBox rs.GetString rs.Close End With Set .ActiveConnection = Nothing End With End Sub HTH, Jamie. -- Jamie, Thanks so very much for your assistance. I will be able to apply to assist in data retrieval no matter the fields. I then used a cross-tab to transpose the data and append an existing application that assist in data roll-up and analyses. SignatureNita J. Perez > > Thanks for the direction, hoever I get and error message when executing the > > query in the dropme database: Circular reference caused by alias 'data_col' [quoted text clipped - 90 lines] > > -- I'm having trouble with my crosstab This is an example of my queries result key FieldName Psuedo Results 11-74100 Event Date Event Date (mm/dd/yy): 7/10/06 11-74100 Event TimeEvent Time (e.g., 2315): 0900 11-74100 Summary Event Summary: #1 Hyper tripped on HiHi Rod Runout on #1 throw inboard cylinder 11-74113 Comments Comments: << 7/22/2006 5:01:09 PM **AutoUpdate** All follow-up actions are complete. Status changed to 3 Closed. If the problem still exists, you can add new follow-up actions to reopen the problem. 11-74113 reli Reliability? (Y if yes): Y I need to transpose this data to then update a table I need the key for the row and field for the colum and the text as the data. I get a 1038 error. I know it's data related but don't know what to do about it Here's and example of the crosstab sql TRANSFORM Max(qryParseMemo.results) AS MaxOfresults SELECT qryParseMemo.KEY, Max(qryParseMemo.results) AS [Total Of results] FROM qryParseMemo GROUP BY qryParseMemo.KEY PIVOT qryParseMemo.FieldName; SignatureNita J. Perez > Jamie, > [quoted text clipped - 98 lines] > > > > -- > I'm having trouble with my crosstab > This is an example of my queries result [quoted text clipped - 20 lines] > GROUP BY qryParseMemo.KEY > PIVOT qryParseMemo.FieldName; I'm sorry, I lack the knowledge to help you with your crosstab problem; I suggest you consider re-posting to attract then attention of someone who can. Jamie. -- Jamie, I did not realized by expression was causing some fields to have errors, the error prevented the crosstab to from functioning. I was "assuming" that the psuedo headers were not disturbed. So I coded around the missing headers. I actually had to shorten the field names to not excede the expression's character limits. Thanks again for putting me on the right track. Here's what I ended up with.... SELECT DT1.KEY, DT2.FieldName, DT2.HDR, IIf(InStr(1,[DT1].[DATA],[DT2].[HDR])=0,"Bad Entry",IIf(InStr(1,[DT1].[DATA],[DT2].[HDR_N])=0,"Bad Entry",IIf(InStr(1,[DT1].[DATA],[DT3].[HDR_R])=0,Trim(Mid$([DT1].[DATA],InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),IIf([dt2].[HDR_N]="",Len([DT1].[DATA]),InStr(InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),[DT1].[DATA],[DT2].[HDR_N])-InStr(1,[DT1].[DATA],[DT2].[HDR])-Len([DT2].[HDR])))),Trim(Mid$([DT1].[DATA],InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),IIf([dt2].[HDR_N]="",InStr(InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),[DT1].[DATA],[DT3].[HDR_R])-InStr(1,[DT1].[DATA],[DT2].[HDR])-Len([DT2].[HDR]),InStr(InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),[DT1].[DATA],[DT2].[HDR_N])-InStr(1,[DT1].[DATA],[DT2].[HDR])-Len([DT2].[HDR]))))))) AS results, DT1.DATA FROM [SELECT KEY, DATA FROM Test]. AS DT1, [SELECT HDR, HDR_N, HDR_ID, FieldName FROM Pseudos]. AS DT2, [SELECT HDR_R,HDRN_R FROM pseudo_remove]. AS DT3 ORDER BY DT1.KEY; SignatureNita J. Perez > > I'm having trouble with my crosstab > > This is an example of my queries result [quoted text clipped - 28 lines] > > -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.