So I finally got this function working with the help of another member, then I transferred it to another database to do the same exact thing and I get error 3061 too few parameters expected 1. I have checked and double checked the table and field names to no avail, but still no luck. If anybody has an idea as to how I could fix this, I would really appreciate the help?
Here is my code:
(Both functions return the same error)
Here is my code:
(Both functions return the same error)
Code:
Public Function patientAdmit(patientName As String, startDate As Date) As Date
Dim dbs As dao.Database
Dim rst As dao.recordset
Dim StrSQl As String
Dim Startdates(10) As Date 'create a matrix of 10 positions
Dim enddates(10) As Date
Dim i As Integer
On Error GoTo ErrorAndExit
StrSQl = "SELECT patientName, startDate, endDate " & _
"FROM MyRecords " & _
"WHERE (((patientName)= '" & patientName & "') " & _
"AND ((startDate)<= #" & Format(startDate, "mm-dd-yyyy") & "#) ) " & _
"ORDER BY MyRecords.startDate DESC;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQl)
'check recordset
If Not rst.EOF And Not rst.BOF Then
rst.MoveLast
rst.MoveFirst
End If
i = 0 'initialise counter
While Not rst.EOF And Not rst.BOF
If rst.RecordCount = 1 Then 'if theres only one entry no furter testing
patientAdmit = rst!startDate
Else
Startdates(i) = rst!startDate 'dump dates in an array
enddates(i) = Nz(rst!endDate, Date) 'if no enddate entered us today
If i > 0 Then 'if more the one date check for
If enddates(i) + 1 = Startdates(i - 1) Then
patientAdmit = rst!startDate
Else
patientAdmit = Startdates(i - 1)
GoTo Exitfunction 'no need for further looping
End If
End If
End If
rst.MoveNext
i = i + 1
Wend
GoTo Exitfunction
ErrorAndExit:
MsgBox "Error: " & Err.Description & vbNewLine & "Errornumber: " & Err.Number, vbOKOnly, "Error"
Exitfunction:
Set dbs = Nothing
Set rst = Nothing
End Function
Public Function patientDischarge(patientName As String, endDate As Date) As Date
Dim dbs As dao.Database
Dim rst As dao.recordset
Dim StrSQl As String
Dim Startdates(10) As Date 'create a matrix of 10 positions
Dim enddates(10) As Date
Dim i As Integer
On Error GoTo ErrorAndExit
StrSQl = "SELECT patientName, startDate, endDate " & _
"FROM MyRecords " & _
"WHERE (((patientName)= '" & patientName & "') " & _
"AND ((endDate)>= #" & Format(endDate, "mm-dd-yyyy") & "#) ) " & _
"ORDER BY MyRecords.endDate ASC;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQl)
'check recordset
If Not rst.EOF And Not rst.BOF Then
rst.MoveLast
rst.MoveFirst
End If
i = 0 'initialise counter
Do While Not rst.EOF And Not rst.BOF
If rst.RecordCount = 1 Then 'if theres only one entry no furter testing
patientDischarge = rst!endDate
Else
Startdates(i) = rst!startDate 'dump dates in an array
enddates(i) = Nz(rst!endDate, Date) 'if no enddate entered us today
If i > 0 Then 'if more the one date check for
If enddates(i - 1) + 1 = Startdates(i) Then
patientDischarge = enddates(i)
Else
patientDischarge = enddates(i - 1)
GoTo Exitfunction 'no need for further looping
End If
End If
End If
rst.MoveNext
i = i + 1
Loop
GoTo Exitfunction
ErrorAndExit:
MsgBox "Error: " & Err.Description & vbNewLine & "Errornumber: " & Err.Number, vbOKOnly, "Error"
Exitfunction:
Set dbs = Nothing
Set rst = Nothing
End Function