Help:Is there a better way to do this?

[Insyderznf]

So I thought I might ask you guys if there is a better way to do the project I'm working on.

I'm creating a fake lab file for a group of fakes patients selected from a table. The only part I have a question on is the actual lab results.

The results are output in Hl7 format and look something like this.

MSHSISFTPORUR01EVN20090329093628LaboratoryPID
PTIDNUMBERPMSMR
SSNTESTTEST12/25/1980FPIDNTE
PV1B74ORCRETEST20090329092908090329
20090329092908PAML41414ORCNTE
OBRI194710T74312083999.Z45Obstetric Panel with CBC
200903291700200903291701PAML41414
Z_Delete20090329092908LABFRoutine

OBRNTEOBX0ST86901RH, non-crossmatch1.1Rh Pos
F20090329092908PAMLOBX-NTEDSPDG1PTIDNUMBEREND

Anyways there is about 20 of those segments per patient. What I am planning to do is assign the entire first half of the segment to a variable and then simply add the results segment in using a + string. Since the results will be hard coded I need to create 20 different lines of adding the results in so it would look like

X = "First part of Lab Segment"
Z = "End of Lab segment"
A = X + "Results Segment for culture" + Z
B = X + "Results Segment for antibody screen" + Z
C = X + "Results Segment for ABO non-crossmatch" + Z

Is there any easier way of doing this? I was thinking making a function but couldn't come up with anything I liked. :think:

Thanks!

[Demosthine]

Good morning.

Do you have any code and workbooks that you could provide. This will help give us a better idea of your overall process in composing the strings rather than just a snippet.

Greatly appreciated.
Scott

[Insyderznf]

Here is what I ended up doing:
Note the Y's that seperate are because we use a ascii character, i didn't include the function to convert in here.

Private Sub cmdGenerateLabs_Click()
On Error GoTo Err_cmdGenerateLabs_Click
If cboProv = "" Or IsNull(cboProv) Or TestOrderDate = "" Or IsNull(TestOrderDate) Or _
TestResultDate = "" Or IsNull(TestResultDate) Then GoTo Err_Null_Values
Dim dbThisDB As DAO.Database
Dim rstPts As DAO.Recordset
Dim rstProv As DAO.Recordset
Dim fs As FileSystemObject
Dim HL7File As TextStream
Dim ptRow As Variant
Dim provID As String
Dim provLName As String
Dim provFName As String
Dim provTitle As String
Dim wlName As String
Dim PtID As String
Dim ptLName As String
Dim ptFName As String
Dim ptDOB As Date
Dim ptSex As String
Dim collectionDate As Date
Dim resultDate As Date
Dim Y As String
Dim Beginlab As String
Dim EndLab As String
Dim Counter As Integer
Dim Labs(25) As String

Set dbThisDB = CurrentDb
Set rstPts = dbThisDB.OpenRecordset("Patients", dbOpenDynaset)
Set rstProv = dbThisDB.OpenRecordset("Providers", dbOpenDynaset)
Set fs = CreateObject("Scripting.FileSystemObject")
Set HL7File = fs.CreateTextFile(Application.CurrentProject.Path & "\PrenatalLabs.hl7", True)
Set ApptFile = fs.CreateTextFile(Application.CurrentProject.Path & "\TestLab.txt", True)

Y = hex2ascii(2)

Labs(0) = "0" & Y & "ST" & Y & "86900" & Y & "ABO, non-crossmatch" & Y & "1.1" & Y & "B" & Y & Y & _
Y & Y
Labs(1) = "0" & Y & "ST" & Y & "86901" & Y & "RH, non-crossmatch" & Y & "1.1" & Y & "Rh Pos" & Y & _
Y & Y & Y
Labs(2) = "0" & Y & "ST" & Y & "86885.Z1" & Y & "Antibody Screen,non-crossmatch" & Y & "1.1" & Y & _
"Negative" & Y & Y & "NEG~" & Y & Y
Labs(3) = "0" & Y & "ST" & Y & "86592" & Y & "RPR" & Y & "1.1" & Y & "Non Reactive" & Y & Y & _
"NR~" & Y & Y
Labs(4) = "0" & Y & "ST" & Y & "86762" & Y & "Rubella Antibody, IgG" & Y & "1.1" & Y & ">500" & Y & _
"IU/mL" & Y & ">9~" & Y & Y
Labs(5) = "0" & Y & "ST" & Y & "86287" & Y & "HBs Ag Screen" & Y & "1.1" & Y & "Non Reactive" & _
Y & Y & "NR~" & Y & Y
Labs(6) = "0" & Y & "NM" & Y & "85025.Z1" & Y & "White Blood Cells" & Y & "1.1" & Y & "8.2" & Y & _
"K/uL" & Y & "4.0-11.0~" & Y & Y
Labs(7) = "0" & Y & "NM" & Y & "85025.Z2" & Y & "Red Blood Cells" & Y & "1.1" & Y & "4.42" & Y & _
"M/uL" & Y & "3.80-5.20~" & Y & Y
Labs(8) = "0" & Y & "NM" & Y & "85025.Z3" & Y & "Hemoglobin" & Y & "1.1" & Y & "12.3" & Y & "g/dL" & _
Y & "11.6-15.5~" & Y & Y
Labs(9) = "0" & Y & "NM" & Y & "85025.Z4" & Y & "Hematocrit" & Y & "1.1" & Y & "36.8" & Y & "%" & Y & _
"35.0-46.0~" & Y & Y
Labs(10) = "0" & Y & "NM" & Y & "85025.Z5" & Y & "MCV" & Y & "1.1" & Y & "83.3" & Y & "fL" & Y & _
"80.0-100.0~" & Y & Y
Labs(11) = "0" & Y & "NM" & Y & "85025.Z6" & Y & "MCH" & Y & "1.1" & Y & "27.9" & Y & "pg" & Y & _
"27.0-34.0~" & Y & Y
Labs(12) = "0" & Y & "NM" & Y & "85025.Z7" & Y & "MCHC" & Y & "1.1" & Y & "33.5" & Y & "g/dL" & Y & _
"32.0-35.5~" & Y & Y
Labs(13) = "0" & Y & "NM" & Y & "85025.Z8" & Y & "RDW" & Y & "1.1" & Y & "13.6" & Y & "%" & Y & _
"11.0-15.0~" & Y & Y
Labs(14) = "0" & Y & "NM" & Y & "85025.Z9" & Y & "Platelets" & Y & "1.1" & Y & "223" & Y & "K/uL" & _
Y & "150-400~" & Y & Y
Labs(15) = "0" & Y & "ST" & Y & "85025.Z25" & Y & "Differential Type" & Y & "1.1" & Y & "Automated" & _
Y & Y & Y & Y
Labs(16) = "0" & Y & "NM" & Y & "85025.Z15" & Y & "Neutrophils" & Y & "1.1" & Y & "73.7" & Y & "%" & _
Y & "40.0-80.0~" & Y & Y
Labs(17) = "0" & Y & "NM" & Y & "85007.Z18" & Y & "Lymphocytes" & Y & "1.1" & Y & "19.9" & Y & _
"%" & Y & "15.0-45.0~" & Y & Y
Labs(18) = "0" & Y & "NM" & Y & "85007.Z22" & Y & "Monocytes" & Y & "1.1" & Y & "5.4" & Y & "%" & _
Y & "0.0-12.0~" & Y & Y
Labs(19) = "0" & Y & "NM" & Y & "85007.Z24" & Y & "Eosinophils" & Y & "1.1" & Y & "0.8" & Y & "%" & _
Y & "0.0-7.0~" & Y & Y
Labs(20) = "0" & Y & "NM" & Y & "85007.Z26" & Y & "Basophils" & Y & "1.1" & Y & "0.2" & Y & "%" & _
Y & "0.0-2.0~" & Y & Y
Labs(21) = "0" & Y & "NM" & Y & "85025.Z16" & Y & "Neutrophils, Absolute" & Y & "1.1" & Y & "6.05" & _
Y & "K/uL" & Y & "2.00-7.30~" & Y & Y
Labs(22) = "0" & Y & "NM" & Y & "85007.Z19" & Y & "Lymphocytes, Absolute" & Y & "1.1" & Y & "1.63" & _
Y & "K/uL" & Y & "1.00-3.40~" & Y & Y
Labs(23) = "0" & Y & "NM" & Y & "85007.Z23" & Y & "Monocytes, Absolute" & Y & "1.1" & Y & "0.44" & Y & _
"K/uL" & Y & "0.00-0.80~" & Y & Y
Labs(24) = "0" & Y & "NM" & Y & "85007.Z25" & Y & "Eosinophils, Absolute" & Y & "1.1" & Y & "0.07" & _
Y & "K/uL" & Y & "0.00-0.50~" & Y & Y
Labs(25) = "0" & Y & "NM" & Y & "85007.Z27" & Y & "Basophils, Absolute" & Y & "1.1" & Y & "0.02" & _
Y & "K/uL" & Y & "0.00-0.10~" & Y & Y

provID = ""
provLName = ""
provFName = ""
provTitle = ""
wlName = ""
PtID = ""
ptLName = ""
ptFName = ""
ptDOB = "01/01/1800"
ptSex = ""

rstProv.FindFirst ("ProviderIDInEnvironment=""" & cboProv & """")
If rstProv.NoMatch Then GoTo Err_No_Provider
If Not IsNull(rstProv("ProvLabIDInEnvironment").Value) Then provID = rstProv("ProvLabIDInEnvironment").Value
If Not IsNull(rstProv("LName").Value) Then provLName = rstProv("LName").Value
If Not IsNull(rstProv("FName").Value) Then provFName = rstProv("FName").Value
If Not IsNull(rstProv("Title").Value) Then provTitle = rstProv("Title").Value
If Not IsNull(rstProv("WorklistName").Value) Then wlName = rstProv("WorklistName").Value

collectionDate = TestOrderDate
resultDate = TestResultDate

For Each ptRow In lstPts.ItemsSelected
rstPts.FindFirst ("PtID=" & lstPts.ItemData(ptRow))
If Not rstPts.NoMatch Then
If Not IsNull(rstPts("PtIDInEnvironment").Value) Then PtID = rstPts("PtIDInEnvironment").Value
If Not IsNull(rstPts("LName").Value) Then ptLName = rstPts("LName").Value
If Not IsNull(rstPts("FName").Value) Then ptFName = rstPts("FName").Value
If Not IsNull(rstPts("DOB").Value) Then ptDOB = rstPts("DOB").Value
If Not IsNull(rstPts("Gender").Value) Then ptSex = rstPts("Gender").Value

If chkHL7 = True Then

'Write MSH header
HL7File.WriteLine ("MSH|^~\&|SISFTP|PAML|YVFWC|PAML|" & _
FormatHL7Date(Date, True) & "|7605aeb5:11f66327ca4:-7c89|ORU^R01|90420011568|P|2.1")

'Write PID segment
HL7File.WriteLine ("PID|||05121976HIP^^^PA||" & ptLName & "^" & ptFName & "^||" & ptDOB & _
"|" & ptSex & "|||^^^^||555-555-5555|||||" & PtID & "||||||||||||")

'Write PV1 segment
HL7File.WriteLine ("PV1||O|B74^^^PA|||||||||||||||||^|||||||||||||||||^||11098|||||||||||||")

'Write ORC segment
HL7File.WriteLine ("ORC|RE|||" & ptLName & FormatHL7Date(Date, True) & "||||||||||B74||^|")

'Write OBR segment
HL7File.WriteLine ("OBR|1|I194710|T743120|83999.Z45^Obstetric Panel with CBC^PPOPS|||" & _
FormatHL7Date(collectionDate, False) & "1700|||||||" & FormatHL7Date(resultDate, False) & _
"1701|^|" & provID & "^" & wlName & "^^^^^^^|||||||||F||^^^^^R|~~|")

'Write OBX's and NTE's
HL7File.WriteLine ("OBX|1|ST|86900^ABO, non-crossmatch|1.1|B||||||F||||01^")
HL7File.WriteLine ("OBX|2|ST|86901^RH, non-crossmatch|1.1|Rh Pos||||||F||||01^")
HL7File.WriteLine ("OBX|3|ST|86885.Z1^Antibody Screen,non-crossmatch|1.1|Negative||NEG||||F||||01^")
HL7File.WriteLine ("OBX|4|ST|86592^RPR|1.1|Non Reactive||NR||||F||||01^")
HL7File.WriteLine ("OBX|5|ST|86762^Rubella Antibody, IgG|1.1|>500|IU/mL|>9||||F||||01^")
HL7File.WriteLine ("NTE|1|| 10 or greater: Presumed immune")
HL7File.WriteLine ("OBX|6|ST|86287^HBs Ag Screen|1.1|Non Reactive||NR||||F||||01^")
HL7File.WriteLine ("OBX|7|NM|85025.Z1^White Blood Cells|1.1|8.2|K/uL|4.0-11.0||||F||||17^")
HL7File.WriteLine ("OBX|8|NM|85025.Z2^Red Blood Cells|1.1|4.42|M/uL|3.80-5.20||||F||||17^")
HL7File.WriteLine ("OBX|9|NM|85025.Z3^Hemoglobin|1.1|12.3|g/dL|11.6-15.5||||F||||17^")
HL7File.WriteLine ("OBX|10|NM|85025.Z4^Hematocrit|1.1|36.8|%|35.0-46.0||||F||||17^")
HL7File.WriteLine ("OBX|11|NM|85025.Z5^MCV|1.1|83.3|fL|80.0-100.0||||F||||17^")
HL7File.WriteLine ("OBX|12|NM|85025.Z6^MCH|1.1|27.9|pg|27.0-34.0||||F||||17^")
HL7File.WriteLine ("OBX|13|NM|85025.Z7^MCHC|1.1|33.5|g/dL|32.0-35.5||||F||||17^")
HL7File.WriteLine ("OBX|14|NM|85025.Z8^RDW|1.1|13.6|%|11.0-15.0||||F||||17^")
HL7File.WriteLine ("OBX|15|NM|85025.Z9^Platelets|1.1|223|K/uL|150-400||||F||||17^")
HL7File.WriteLine ("OBX|16|ST|85025.Z25^Differential Type|1.1|Automated||||||F||||17^")
HL7File.WriteLine ("OBX|17|NM|85025.Z15^Neutrophils|1.1|73.7|%|40.0-80.0||||F||||17^")
HL7File.WriteLine ("OBX|18|NM|85007.Z18^Lymphocytes|1.1|19.9|%|15.0-45.0||||F||||17^")
HL7File.WriteLine ("OBX|19|NM|85007.Z22^Monocytes|1.1|5.4|%|0.0-12.0||||F||||17^")
HL7File.WriteLine ("OBX|20|NM|85007.Z24^Eosinophils|1.1|0.8|%|0.0-7.0||||F||||17^")
HL7File.WriteLine ("OBX|21|NM|85007.Z26^Basophils|1.1|0.2|%|0.0-2.0||||F||||17^")
HL7File.WriteLine ("OBX|22|NM|85025.Z16^Neutrophils, Absolute|1.1|6.05|K/uL|2.00-7.30||||F||||17^")
HL7File.WriteLine ("OBX|23|NM|85007.Z19^Lymphocytes, Absolute|1.1|1.63|K/uL|1.00-3.40||||F||||17^")
HL7File.WriteLine ("OBX|24|NM|85007.Z23^Monocytes, Absolute|1.1|0.44|K/uL|0.00-0.80||||F||||17^")
HL7File.WriteLine ("OBX|25|NM|85007.Z25^Eosinophils, Absolute|1.1|0.07|K/uL|0.00-0.50||||F||||17^")
HL7File.WriteLine ("OBX|26|NM|85007.Z27^Basophils, Absolute|1.1|0.02|K/uL|0.00-0.10||||F||||17^")

HL7File.WriteBlankLines (2)

End If

If chkDev = True Or chkTest = True Or chkTraining = True Then

Beginlab = "MSH" & Y & "SISFTP" & Y & "ORU" & Y & "R01" & Y & "EVN" & Y & _
FormatHL7Date(Date, True) & Y & "Laboratory" & Y & "PID" & Y & Y & Y & Y & Y & PtID & _
Y & "PMS" & Y & "MR" & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & _
Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & "SSN" & Y & _
Y & ptLName & Y & ptFName & Y & Y & Format(ptDOB, "yyyymmdd") & Y & ptSex & Y & "PID-NTE" & _
Y & Y & "PV1" & Y & "B74" & Y & Y & Y & Y & Y & Y & Y & "ORC" & Y & "RE" & Y & Y & Y & _
"Prenatal" + FormatHL7Date(collectionDate, True) & Y & Y & Y & _
FormatHL7Date(collectionDate, True) & Y & Y & Y & Y & Y & Y & Y & Y & Y & provID & _
Y & Y & Y & Y & Y & "ORC-NTE" & Y & Y & "OBR" & Y & "I194710" & Y & "T743120" & Y & _
"83999.Z45" & Y & "Obstetric Panel with CBC" & Y & Y & FormatHL7Date(collectionDate, True) & _
Y & Y & Y & Y & Y & Y & Y & FormatHL7Date(resultDate, True) & Y & Y & Y & provID & Y & _
wlName & Y & Y & Y & FormatHL7Date(resultDate, True) & Y & "LAB" & Y & ptSex & Y & Y & Y & _
Y & "Routine" & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & _
Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & Y & _
Y & Y & Y & Y & Y & Y & Y & "OBR-NTE" & Y & Y & "OBX" & Y

EndLab = ptSex & Y & FormatHL7Date(collectionDate, True) & Y & "PAML" & Y & Y & Y & Y & Y & _
"OBX-NTE" & Y & Y & "DSP" & Y & Y & "DG1" & Y & Y & Y & Y & Y & Y & PtID & Y & "END" & Y
For Counter = LBound(Labs) To UBound(Labs)
ApptFile.WriteLine (Beginlab + Labs(Counter) + EndLab)
ApptFile.WriteBlankLines (2)
Next

End If
End If

Next ptRow
HL7File.Close
rstPts.Close
rstProv.Close
DoCmd.Close acForm, "PtLabOptions", acSaveNo

[CreganTur]

Thanks for sharing your solution!

In the future when you post code, please wrap it in VBA tags- click on the green VBA button. This will format the code according to VBIDE, which makes it much easier to read.

Thanks:thumb