to my post :)
i am an extreme newcommer to the world of vb scripting. my problem here is i am unable to pull results i need based on a certain field resulting in any value other than null.
my script is as follows.
------
'The following constants are copied from C:\Program Files\Common Files\System\ado\adovbs.inc
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3
'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H8
Const adCmdText = &H1
Const adCmdTable = &H2
Const adCmdStoredProc = &H4
Const adCmdFile = &H100
Const adCmdTableDirect = &H200
'Function converts string to date. If fails, returns false
Function ConvertDate(DateString)
If IsDate(DateString) Then
ConvertDate = CDate(DateString)
Exit Function
Else
If Len(DateString) >= 6 Then
DateString = Mid(DateString, 5, 2) & "/" & Mid(DateString, 7, 2) & "/" & Mid(DateString, 1, 4) 'formatting date that was yyyymmdd
End If
If IsDate(DateString) Then 'if it's a date string after conversion, return the date, else return false
ConvertDate = CDate(DateString)
Else
ConvertDate = False
End If
End If
End Function
Dim cn, ConnectionString
Dim rs, TableName
Dim DateFieldName, TypeFieldName
Dim AdmitStateFieldName, DischargeFieldName 'Added for NULL Value Deletion
Dim RoomFieldName
Dim PurgeDays, DischargeDate, PatientTypeCLI
'----------- Data configuration - Modify as appropriate ------------------
PurgeDays = 1 'How many days to wait until purging record
TableName = "PatientVisits" 'Database Table
CLIDateFieldName = "AdmitDateTime" 'Discharge Date column for CLI patients
DateFieldName = "DischargeDateTime" 'Discharge Date for other type of patients
TypeFieldName = "PatientType" 'PatientType column in database
AdmitStateFieldName = "AdmitState" 'Admit State for NULL Return
DischargeFieldName = "DischargeDateTime" 'Discharge Date for NULL Return
RoomFieldName = "Room" 'To Remove Discharged Patients if they are in a room
ConnectionString = "Provider=SQLOLEDB;Data Source=OMC-INT01; Initial Catalog=FormFast-SQL;User ID=FormFast;Password= f0rmf@$t2008;" 'DSN name or connection string
'----------- Data configuration END -----------------------------------
Set cn = CreateObject("ADODB.Connection")
cn.Open ConnectionString
Set rs = CreateObject("ADODB.Recordset")
rs.Open TableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
rs.MoveFirst
Do While Not rs.EOF
'---Modify this region to create the criteria for record deletion-----------------------------
'Get the Discharge date (different for PatientType of CLI)
If (rs.Fields(TypeFieldName).Value = "CLI")_
OR (rs.Fields(TypeFieldName).Value = "REF")_
OR (rs.Fields(TypeFieldName).Value = "SDC")_
OR (rs.Fields(TypeFieldName).Value = "IN")_
OR (rs.Fields(TypeFieldName).Value = "INO")_
OR (rs.Fields(TypeFieldName).Value = "RCR")_
Then
DateString = rs.Fields(CLIDateFieldName).Value
Else
DateString = rs.Fields(DateFieldName).Value
End If
'convert the datestring
DischargeDate = ConvertDate(Trim(DateString))
If IsDate(DischargeDate) Then 'make sure date converted
'if meets the criteria, delete the record
If (Now - DischargeDate > PurgeDays) Then
rs.Delete 1 'adAffectCurrent
End If
End If
'---Remove Null Discharges -------------------------------------------------------------------
If (rs.Fields(DischargeFieldName).Value = "") And (rs.Fields(AdmitStateFieldName).Value = "Discharged")_
Then
rs.Delete 1
End If
If IsNull(DischargeFieldName) And (rs.Fields(AdmitStateFieldName).Value = "Discharged")_
Then
rs.Delete 1
End If
'---This is where my error occurs------ '---Remove Discharged Patients if Discharged and in Room -------------------------------------
'If (rs.Fields(AdmitStateFieldName).Value = "Discharged") And (rs.Fields(RoomFieldName) Is Not Null)_
'Then
'rs.Delete 1
'End If '---Modified Region END ----------------------------------------------------------------------
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
See i need my outpatients to stay in the database until the evening, but patients discharged from the hospital with a room number can be removed at 10 minute intervals because i dont want two patients in the same room with one with the state of discharged. any help would be much appreciated :)
thanks,
chad