B
Beccy Howard
I have got this module to copy table 1 to table 2. In the
copy over I have certain adjustments and calculations
being performed and fields are added as a result. The
problem is that when I run the code I have a situation
where the the U_DNA field in table 2 is being filled with
data even when there is no data in the LastDNAdate field
in table 1.
What am I doing wrong for it to automatically insert
figures in the U_DNA field? the full code is included
below.
Function copy_op_rkb00()
Dim wait As Database, rkb As Recordset, opwl As Recordset
Set wait = DBEngine.Workspaces(0).Databases(0)
Set rkb = wait.OpenRecordset("op wl cmds (rkb00)")
Set opwl = wait.OpenRecordset("op wl test")
rkb.MoveFirst
While Not rkb.EOF
cendate = #8/31/2003#
bookdate = Right$(rkb![REFREQDATE], 2) & "/" & Mid$(rkb!
[REFREQDATE], 5, 2) & "/" & Mid$(rkb![REFREQDATE], 3, 2)
If Len(rkb![LastDNAdate] & "") = 0 Then
LastDNAdate = ""
Else
dnadate = Right$(rkb![LastDNAdate], 2) & "/" &
Mid$(rkb![LastDNAdate], 5, 2) & "/" & Mid$(rkb!
[LastDNAdate], 3, 2)
dnadate2 = DateValue(dnadate)
End If
cendate2 = DateValue(cendate)
bookdate2 = DateValue(bookdate)
If dnadate = "" Then
waittime = (cendate2 - bookdate2)
Else
waittime = (cendate2 - dnadate2)
End If
Select Case (waittime)
Case 0 To 27: period = 1
Case 28 To 55: period = 2
Case 56 To 90: period = 3
Case 91 To 118: period = 4
Case 119 To 146: period = 5
Case 147 To 182: period = 6
Case Else: period = 7
End Select
opwl.AddNew
opwl![procode] = rkb![procode]
opwl![purcode] = rkb![purcode]
opwl![serno] = rkb![serno]
opwl![contline] = rkb![contline]
opwl![purchref] = rkb![purchref]
opwl![nhsno] = rkb![nhsno]
opwl![patname] = rkb![patname]
opwl![patadd] = rkb![patadd]
opwl![sex] = rkb![sex]
opwl![marstat] = rkb![marstat]
opwl![postcode] = rkb![postcode]
opwl![dha] = rkb![dha]
opwl![DOB] = rkb![DOB]
opwl![reggp] = rkb![reggp]
opwl![gppraccode] = rkb![gppraccode]
opwl![refgp] = rkb![refgp]
opwl![reforg] = rkb![reforg]
opwl![sertype] = rkb![sertype]
opwl![localpatid] = rkb![localpatid]
opwl![REFREQDATE] = rkb![REFREQDATE]
opwl![refreqrec] = rkb![refreqrec]
opwl![priority] = rkb![priority]
opwl![sourceref] = rkb![sourceref]
opwl![attendid] = rkb![attendid]
opwl![firstatten] = rkb![firstatten]
opwl![ATTENDATE] = rkb![ATTENDATE]
opwl![dna] = rkb![dna]
opwl![vactype] = rkb![vactype]
opwl![timeaheadcount] = rkb![timeaheadcount]
opwl![noofchanges] = rkb![noofchanges]
opwl![conscode] = rkb![conscode]
opwl![mainspef] = rkb![mainspef]
opwl![consspec] = rkb![consspec]
opwl![localspec] = rkb![localspec]
opwl![clinpur] = rkb![clinpur]
opwl![admcat] = rkb![admcat]
opwl![loctype] = rkb![loctype]
opwl![sitecode] = rkb![sitecode]
opwl![medstafftype] = rkb![medstafftype]
opwl![CensusDate] = rkb![CensusDate]
opwl![outcome] = rkb![outcome]
opwl![LastDNAdate] = rkb![LastDNAdate]
opwl![LastDNAdatestat] = rkb![LastDNAdatestat]
opwl![diag1] = rkb![diag1]
opwl![diag2] = rkb![diag2]
opwl![diag3] = rkb![diag3]
opwl![op1] = rkb![op1]
opwl![op2] = rkb![op2]
opwl![op3] = rkb![op3]
opwl![wait] = rkb![wait]
opwl![listno] = rkb![listno]
opwl![U_Census_Date] = cendate
opwl![U_REF_DATE] = bookdate
opwl![U_DNA] = dnadate
opwl![WAIT_DUR] = waittime
opwl![u_low] = period
opwl![u_pattype] = Null
opwl.Update
rkb.MoveNext
Wend
End Function
copy over I have certain adjustments and calculations
being performed and fields are added as a result. The
problem is that when I run the code I have a situation
where the the U_DNA field in table 2 is being filled with
data even when there is no data in the LastDNAdate field
in table 1.
What am I doing wrong for it to automatically insert
figures in the U_DNA field? the full code is included
below.
Function copy_op_rkb00()
Dim wait As Database, rkb As Recordset, opwl As Recordset
Set wait = DBEngine.Workspaces(0).Databases(0)
Set rkb = wait.OpenRecordset("op wl cmds (rkb00)")
Set opwl = wait.OpenRecordset("op wl test")
rkb.MoveFirst
While Not rkb.EOF
cendate = #8/31/2003#
bookdate = Right$(rkb![REFREQDATE], 2) & "/" & Mid$(rkb!
[REFREQDATE], 5, 2) & "/" & Mid$(rkb![REFREQDATE], 3, 2)
If Len(rkb![LastDNAdate] & "") = 0 Then
LastDNAdate = ""
Else
dnadate = Right$(rkb![LastDNAdate], 2) & "/" &
Mid$(rkb![LastDNAdate], 5, 2) & "/" & Mid$(rkb!
[LastDNAdate], 3, 2)
dnadate2 = DateValue(dnadate)
End If
cendate2 = DateValue(cendate)
bookdate2 = DateValue(bookdate)
If dnadate = "" Then
waittime = (cendate2 - bookdate2)
Else
waittime = (cendate2 - dnadate2)
End If
Select Case (waittime)
Case 0 To 27: period = 1
Case 28 To 55: period = 2
Case 56 To 90: period = 3
Case 91 To 118: period = 4
Case 119 To 146: period = 5
Case 147 To 182: period = 6
Case Else: period = 7
End Select
opwl.AddNew
opwl![procode] = rkb![procode]
opwl![purcode] = rkb![purcode]
opwl![serno] = rkb![serno]
opwl![contline] = rkb![contline]
opwl![purchref] = rkb![purchref]
opwl![nhsno] = rkb![nhsno]
opwl![patname] = rkb![patname]
opwl![patadd] = rkb![patadd]
opwl![sex] = rkb![sex]
opwl![marstat] = rkb![marstat]
opwl![postcode] = rkb![postcode]
opwl![dha] = rkb![dha]
opwl![DOB] = rkb![DOB]
opwl![reggp] = rkb![reggp]
opwl![gppraccode] = rkb![gppraccode]
opwl![refgp] = rkb![refgp]
opwl![reforg] = rkb![reforg]
opwl![sertype] = rkb![sertype]
opwl![localpatid] = rkb![localpatid]
opwl![REFREQDATE] = rkb![REFREQDATE]
opwl![refreqrec] = rkb![refreqrec]
opwl![priority] = rkb![priority]
opwl![sourceref] = rkb![sourceref]
opwl![attendid] = rkb![attendid]
opwl![firstatten] = rkb![firstatten]
opwl![ATTENDATE] = rkb![ATTENDATE]
opwl![dna] = rkb![dna]
opwl![vactype] = rkb![vactype]
opwl![timeaheadcount] = rkb![timeaheadcount]
opwl![noofchanges] = rkb![noofchanges]
opwl![conscode] = rkb![conscode]
opwl![mainspef] = rkb![mainspef]
opwl![consspec] = rkb![consspec]
opwl![localspec] = rkb![localspec]
opwl![clinpur] = rkb![clinpur]
opwl![admcat] = rkb![admcat]
opwl![loctype] = rkb![loctype]
opwl![sitecode] = rkb![sitecode]
opwl![medstafftype] = rkb![medstafftype]
opwl![CensusDate] = rkb![CensusDate]
opwl![outcome] = rkb![outcome]
opwl![LastDNAdate] = rkb![LastDNAdate]
opwl![LastDNAdatestat] = rkb![LastDNAdatestat]
opwl![diag1] = rkb![diag1]
opwl![diag2] = rkb![diag2]
opwl![diag3] = rkb![diag3]
opwl![op1] = rkb![op1]
opwl![op2] = rkb![op2]
opwl![op3] = rkb![op3]
opwl![wait] = rkb![wait]
opwl![listno] = rkb![listno]
opwl![U_Census_Date] = cendate
opwl![U_REF_DATE] = bookdate
opwl![U_DNA] = dnadate
opwl![WAIT_DUR] = waittime
opwl![u_low] = period
opwl![u_pattype] = Null
opwl.Update
rkb.MoveNext
Wend
End Function