Search Form vba: searching by date

[sear100]

Dear All,

It’s been many months since I was last on the forum. I was wondering if I can get some help and education with regards to coding a search form.

The database is a patient database used for tracking Inpatient data and Handover (or Handoffs for those in the US). It has been merrily ticking over since December and has accrued several thousand records. To get access to this data in new and interesting ways I’m trying evolve my rudimentary search function which currently only works for diagnosis, name and consultant (in otherword text fields).

Thus I have an unbound form that has various input boxes on it.
I have borrowed some code to power the form and have replaced with own fields:

Dim criteria As String

‘ Looks for input text and adds it to the final criteria to power the search list at the end based on field in this case txtDiagnosis
If Not IsNull(Me.txtDiagnosis) Then
	If criteria <> "" Then criteria – criteria & " and "
	criteria = criteria & "txtDiagnosis like " & Chr(34) & "*" & Me.txtDiagnosis & "*" & Chr(34)
End If

'Searches for records that fall between specified date values (dtmStart and dtmStop) for when a patient was admitted (dtmAdmittedDate) [[[CURRENTLY NON FUNCTIONAL]]]
If Not IsNull(Me.dtmStart) Then
	If IsNull(Me.dtmStop) Then 
	Me.dtmStop = Me.dtmStart
End If
If criteria <> "" Then criteria – criteria & " and "
criteria = criteria & "(dtmAdmittedDate >=#" & Me.dtmStart & “# and dtmAdmittedDate <=#” & Me.dtmStop & “#)””
End If

'Closes current form and then opens a List form (frm_InpatientList) based on the criteria in the search form.
DoCmd.Close acDefault
DoCmd.OpenForm “frm_InpatientList”, , , criteria, acDialog

ISSUE 1:
I am struggling with the date search function. It doesn't work. I would be grateful if someone could point out what is wrong with the code? I would also like for users to be able to search for a discharged date - though I'd be grateful here if you could comment on whether it is prudent to search for either a admitted date or a discharged date?

ISSUE 2:
I have a check box for "complications" that I'd like to be included on in the search. Could someone suggest the code that I would use?

Thanks in advance for your kind help! Happy to clarify any issues.

 

[BlueIshDan]

click on the QueryBuilder link below might help you get started.

 

[sear100]

Hi Dan - that's certainly a very cool piece of code. If I'm reading right it helps create a new form based on a table no? I had a look in the module and the class modules but couldn't see any code which helped specifically with my issue here ie. search forms. I'd be grateful if you could point me to it if the answer is there though.
S

 

[BlueIshDan]

How much of this code have you modified?
I don't recognize this as valid code:

If criteria <> "" Then criteria – criteria & " and "

I was under the impression that you didn't have a form to start with seeing as you said you were editing someone else's code to your situation. I thought you could use a form generated by that program to start with. It allows you to filter your results through the selections of distinct values. After the form is generated you can modify it to your design.

 

[sear100]

There may be a transliteration error as I was wrote copy typing from a laptop. I've just found a way to get access to my original actual code that's in the database.

The original code was as follows:

Private Sub cmdContinue_Click()
Dim crit As String

If Not IsNull(Me.txtPatientID) Then
    If crit <> "" Then crit = crit & " and "
    crit = crit & "PatientID = " & Me.txtPatientID
End If

If Not IsNull(Me.txtStartDateStart) Then
    If IsNull(Me.txtStartDateStop) Then
        Me.txtStartDateStop = Me.txtStartDateStart
    End If
    If crit <> "" Then crit = crit & " and "
    crit = crit & "(StartDate >= #" & Me.txtStartDateStart & "# and startdate <= #" & Me.txtStartDateStop & "#)"
End If
If Not IsNull(Me.txtStartTimeStart) Then
    If crit <> "" Then crit = crit & " and "
    
    If IsNull(Me.txtStartTimeStop) Then
        crit = crit & "Starttime >= #" & Me.txtStartTimeStart & "#"
    Else
        crit = crit & "(starttime >= #" & Me.txtStartTimeStart & "# and starttime <= #" & Me.txtStartTimeStop & "#)"
    End If
End If
If Not IsNull(Me.txtTakeTime) Then
    If crit <> "" Then crit = crit & " and "
    crit = crit & "taketime = " & Me.txtTakeTime
End If

DoCmd.OpenForm "frmEpisode_List", , , crit, , acDialog

End Sub

I believe that this is similarly NON functional with respect to the date search function.

The code that Is in My current database is therefore as follows:

Private Sub btnPatientSearch_Click()

'Searches Patient Database and draws records depending on Criteria Entered on the form
Dim crit As String

'Searches for the first name
If Not IsNull(Me.txtFirstName) Then
    If crit <> "" Then crit = crit & " and "
    crit = crit & "txtFirstName like " & Chr(34) & "*" & Me.txtFirstName & "*" & Chr(34)
End If

'Searches for the last name
If Not IsNull(Me.txtLastName) Then
    If crit <> "" Then crit = crit & " and "
    crit = crit & "txtLastName like " & Chr(34) & "*" & Me.txtLastName & "*" & Chr(34)
End If

'NON FUNCTIONAL
'Searches between the two start and stop dates for when was admitted
'If Not IsNull(Me.dtmStart) Then
 '    If IsNull(Me.dtmStop) Then
  '      Me.dtmStop = Me.dtmStart
   ' End If
    'If crit <> "" Then crit = crit & " and "
    'crit = crit & "(dtmAdmittedDate >= #" & Me.dtmStart & "# and dtmAdmittedDate <= #" & Me.dtmStop & "#)'"
'End If

'Searches for the Consultant
If Not IsNull(Me.txtConsultant) Then
    If crit <> "" Then crit = crit & " and "
    crit = crit & "intConultantDisplay = " & Me.txtConsultant
End If

'Searches for DOB dtmDOB
'If Not IsNull(Me.dtmDOB) Then
 '   If crit <> "" Then crit = crit & " and "
  '  crit = crit & "dtmDOB = " & Me.dtmDOB
'End If

'Searchs for Diagnosis
If Not IsNull(Me.txtDiagnosis) Then
    If crit <> "" Then crit = crit & " and "
    crit = crit & "txtDiagnosis like " & Chr(34) & "*" & Me.txtDiagnosis & "*" & Chr(34)
End If

'Opens the search form with everything that was entered as criteria
DoCmd.Close acDefault
DoCmd.OpenForm "frm_InpatientList", , , crit, , acDialog

End Sub

 

[BlueIshDan]

Ok, I see what you're asking.
Tell me, did this work before you addition of the date code?

 

[sear100]

No it didn't work actually.
Do I need to add it as another string? If so how would that then plug into the query at the end?

 

[BlueIshDan]

crit = crit & "(dtmAdmittedDate >= CDate(" & Me.dtmStart.Vaue & ") and dtmAdmittedDate <= CDate(" & Me.dtmStop.Value & ")) "

 

[sear100]

Hi BlueishDan,

Sorry for the delay - I had an extreemly hectic last week!

The full working code was:

Private Sub btnPatientSearch_Click()

'Searches Patient Database and draws records depending on Criteria Entered on the form
Dim crit As String

'Searches between two dates Admitted
If Not IsNull(Me.dtmStart) Then
    If IsNull(Me.dtmStop) Then
        Me.dtmStop = Me.dtmStart
    End If
    If crit <> "" Then crit = crit & " and "
    crit = crit & "(dtmAdmittedDate >= #" & Me.dtmStart & "# and dtmAdmittedDate <= #" & Me.dtmStop & "#)"
End If

If Not IsNull(Me.dtmStart) Then
    If crit <> "" Then crit = crit & " and "
        If IsNull(Me.dtmStop) Then
        crit = crit & "dtmStart >= #" & Me.dtmStart & "#"
    Else
        crit = crit & "(dtmAdmittedDate >= #" & Me.dtmStart & "# and dtmAdmittedDate <= #" & Me.dtmStop & "#)"
    End If
End If

'Searches between two dates Discharged
If Not IsNull(Me.dtmStartDischarge) Then
    If IsNull(Me.dtmStopDischarge) Then
        Me.dtmStopDischarge = Me.dtmStartDischarge
    End If
    If crit <> "" Then crit = crit & " and "
    crit = crit & "(dtmDischargedDateTime >= #" & Me.dtmStartDischarge & "# and dtmDischargedDateTime <= #" & Me.dtmStopDischarge & "#)"
End If

If Not IsNull(Me.dtmStartDischarge) Then
    If crit <> "" Then crit = crit & " and "
        If IsNull(Me.dtmStopDischarge) Then
        crit = crit & "dtmStartDischarge >= #" & Me.dtmStartDischarge & "#"
    Else
        crit = crit & "(dtmDischargedDateTime >= #" & Me.dtmStartDischarge & "# and dtmDischargedDateTime <= #" & Me.dtmStopDischarge & "#)"
    End If
End If

'Searches between two dates Date of Birth
If Not IsNull(Me.dtmStartDOB) Then
   If IsNull(Me.dtmStopDOB) Then
        Me.dtmStopDOB = Me.dtmStartDOB
    End If
    If crit <> "" Then crit = crit & " and "
    crit = crit & "(dtmDOB >= #" & Me.dtmStartDOB & "# and dtmDOB <= #" & Me.dtmStopDOB & "#)"
End If

If Not IsNull(Me.dtmStartDOB) Then
    If crit <> "" Then crit = crit & " and "
        If IsNull(Me.dtmStopDOB) Then
        crit = crit & "dtmStartDOB >= #" & Me.dtmStartDOB & "#"
    Else
        crit = crit & "(dtmDOB >= #" & Me.dtmStartDOB & "# and dtmDOB <= #" & Me.dtmStopDOB & "#)"
    End If
End If


'Opens the search form with everything that was entered as criteria
DoCmd.Close acDefault
DoCmd.OpenForm "frm_InpatientList", , , crit, , acDialog

However I have one issue

I'm from the UK and we have our dates as Day month Year
At present the above code works perfectly for dates if the input in the american format Month Day Year. For now I have put a notice on the form warning users of this fact. I was wondering if there is a work around?
Is it a code issue or is it form issue - should I somehow be using a date serial to get around this?

My second issue is another VBA problem.
In search form I want users to have a tickbox/radio button to select if a patient is "NOT YET DISCHARGED" e.g. Adding to the above criteria where a field "dtmDischargedDateTime" is a Null or 0.

This code bellow is wrong but lays out what I'm trying to achieve...

If Me.chkInpatient = True Then
'pulls records where dtmDischargedDateTime is Null or 0     
crit = crit & "dtmDischargedDateTime = 0"
End If

Thanks again for your help!

 

[BlueIshDan]

crit = crit & "dtmDischargedDateTime Is Null"

OR

crit = crit & "dtmDischargedDateTime Is Not Null"

Also you're repeating yourself by saying dtm****DateTime lol. Would probably be reasonable to remove the DateTime at the end.

As for the date/time format, I'm fairly sure that you can format its display in your table design. I will look this up for you

 

[BlueIshDan]

Here is how you format your date
If you only want there to be the last two numbers of the year, replace the yyyy with yy.
If you want there to be / instead of -, simply swap them!

Hope this helps.

 

[sear100]

Dan - you've saved the day

For the record also for "positive" searches the following works and feeds into the search as well.

'If Tick box is checked only searches for those on Antibiotics
If Me.chkAntibiotics = True Then
     If crit <> "" Then crit = crit & " and "
     crit = crit & "blnAntibiotic = True"
End If

EDITED

As regards to date I'm aware of Date formating dd mm yy hh:nn terminology but it doesnt solve the problem if I change it at the table level. I may have to come back to this one. For my users at least there is the warning and they'll know something up when the results are whack!

I guess we can call this post solved - Thanks so much for your help!