i am trying to process raw, csv data i imported into a table. The data contains medical info, a list of 1 to 12 diagnosis codes, and a list of 1 to 12 procedure codes. There always must be a primary diagnosis and procedure, so those fields are named PriDiag and PriProc. the other field lists respectively have the same base field name, with a numeric suffix. So additional diagnoses are named ihdgc2 thru ihdgc12 and additional procedures are named ihpdi2 thru ihpdi12. I wand to loop thru all of those fields and count fields with values so that i can update the record with counters of the total diagnoses and total procedures on a claim. I need to test each field name to see if it is null/empty. If not empty, append the code values together, so they can be displayed as a single field as a list on a form, and in excel. i had trouble getting the code to work correctly using variable strings, so i went with the code below. Horrible i know, but it allowed me to get the data in quick. now i'm trying to optimize the code.
Also, because this file is passed from an outside source to me, sometimes, the data "skips" some fields, which doesn't always make sense to me, but it's what i get. For example, i will get the primary diagnosis code. then there will be values in the fields for diag2, diag3, diag4 and diag5 will be empty, then a value in diag6 and diag7.
thanks for any help in advance.Code:Private Sub UpdtQLCilDataCombined() Dim myDB As Database Dim myRS As Recordset Dim strSQL As String Dim strDiagFldNm As String Dim strFld As String Dim intDiagCnt As Integer Dim intDiagProcCnt As Integer Dim strDiags As String Dim lngRecCnt As Long strDiagFldNm = "ihdgc" 'base field name is ihdgcd; for primary diag; rest are labeled 2 thru 12 intDiagCnt = 1 strSQL = "SELECT * FROM QL_CIL_Data_Combined" Set myRS = CurrentDb.OpenRecordset(strSQL) Me.txtTeamLog = "" Me.txtRecCnt = 0 lngRecCnt = 0 With myRS If Not (.EOF And .BOF) Then .MoveFirst Do While Not .EOF intDiagCnt = 1 intDiagProcCnt = 0 strDiags = Trim(![ihdgcd]) .Edit lngRecCnt = lngRecCnt + 1 Me.txtTeamLog = ![SystemID] If (Nz(![ihdgc2]) <> "") And IsNumeric(![ihdgc2]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc2]) End If If (Nz(![ihdgc3]) <> "") And IsNumeric(![ihdgc3]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc3]) End If If (Nz(![ihdgc4]) <> "") And IsNumeric(![ihdgc4]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc4]) End If If (Nz(![ihdgc5]) <> "") And IsNumeric(![ihdgc5]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc5]) End If If (Nz(![ihdgc6]) <> "") And IsNumeric(![ihdgc6]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc6]) End If If (Nz(![ihdgc7]) <> "") And IsNumeric(![ihdgc7]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc7]) End If If (Nz(![ihdgc8]) <> "") And IsNumeric(![ihdgc8]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc8]) End If If (Nz(![ihdgc9]) <> "") And IsNumeric(![ihdgc9]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc9]) End If If (Nz(![ihdgc10]) <> "") And IsNumeric(![ihdgc10]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc10]) End If If (Nz(![ihdgc11]) <> "") And IsNumeric(![ihdgc11]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc11]) End If If (Nz(![ihdgc12]) <> "") And IsNumeric(![ihdgc12]) Then intDiagCnt = intDiagCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihdgc12]) End If ![Diags_Sub] = strDiags If (Nz(![ihpdi2]) <> "") And IsNumeric(![ihpdi2]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi2]) End If If (Nz(![ihpdi3]) <> "") And IsNumeric(![ihpdi3]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi3]) End If If (Nz(![ihpdi4]) <> "") And IsNumeric(![ihpdi4]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi4]) End If If (Nz(![ihpdi5]) <> "") And IsNumeric(![ihpdi5]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi5]) End If If (Nz(![ihpdi6]) <> "") And IsNumeric(![ihpdi6]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi6]) End If If (Nz(![ihpdi7]) <> "") And IsNumeric(![ihpdi7]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi7]) End If If (Nz(![ihpdi8]) <> "") And IsNumeric(![ihpdi8]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi8]) End If If (Nz(![ihpdi9]) <> "") And IsNumeric(![ihpdi9]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi9]) End If If (Nz(![ihpdi10]) <> "") And IsNumeric(![ihpdi10]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi10]) End If If (Nz(![ihpdi11]) <> "") And IsNumeric(![ihpdi11]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi11]) End If If (Nz(![ihpdi12]) <> "") And IsNumeric(![ihpdi12]) Then intDiagProcCnt = intDiagProcCnt + 1 strDiags = strDiags & STRDELIM & Trim(![ihpdi12]) End If ![Diags_Proc] = strDiags ![ClmCnt_Diags_Sub] = intDiagCnt ![ClmCnt_Diags_Proc] = intDiagProcCnt .Update 'For ix = 2 To 12 ' strFld = strDiagFldNm & ix ' intDiagCnt = intDiagCnt + 1 ' If Nz(myRS!Fields!strFld, "") <> "" Then ' Me.txtTeamLog = 8 ' Else ' Me.txtTeamLog = 999 ' End If ' 'Me.txtTeamLog = Me.txtTeamLog & vbCrLf & strFld 'Next ix Me.txtRecCnt = lngRecCnt DoEvents .MoveNext Loop End If End With myRS.close End Sub