Query criteria based on returned value not working

[mastaofdisastax]

First off, I've been doing a lot of searching for an answer, but haven't fond anything close enough to use, so I appreciate anyone's help in advance.

I've built a DB to keep track of tasks and their progress from day to day. There is a memo field on the form to track a task's progress which records a time stamp for each new entry. I have programmed a button which allows me to extract and view specific dates or ranges from the memo field based on date criteria in text boxes off to the side. I should note that in the memo field, the most current date is displayed first since this will make the code below easier to understand. This button's code saves the extracted data to "strReturnedText".

My next step is to create a report which shows the task information along with only the extracted log data, rather than the entire contents of the log. From my reading, I understand a query can return values from public functions, and I have followed what steps I can to do this, but so far have been unsuccessful.

I have set the criteria for the log field in the query to:

GetLogText()

I have added a module which has the following:

Option Compare Database
Option Explicit
Public strReturnedText As String

Public Function GetLogText() As String
GetLogText = strReturnedText
End Function

In my form's class object, the button runs the following code:

Private Sub ViewReport_Click()
On Error GoTo Err_Report_Click

Dim strModStartDate As String
Dim strModEndDate As String
Dim strText As String
Dim StartPos As Long
Dim EndPos As Long
Dim Length As Long
Dim strRptName As String

strModStartDate = Format(Forms!WorkLog.ModStartDate, "mm/dd/yyyy")
strModEndDate = Format(Forms!WorkLog.ModEndDate, "mm/dd/yyyy")

If IsNull(Forms!WorkLog.LongDesc.Value) Then
strText = ""
Else
strText = Forms!WorkLog.LongDesc.Value
End If

'Searches for the end date from the beginning of the text to set the start position
' (given that the field is in reverse chronological order)

If InStr(strText, strModEndDate) = 0 Then
StartPos = 5
' MsgBox ("End date not found, StartPos = " & StartPos)
Else
StartPos = (InStr(strText, strModEndDate) - 2)
' MsgBox ("StartPos = " & StartPos)
End If

'Searches for the start date from the end to set the end position

If InStrRev(strText, strModStartDate) = 0 Then
EndPos = Len(strText) + 1
' MsgBox ("Start date not found, EndPos = " & EndPos)
Else
EndPos = (InStrRev(strText, strModStartDate) - 2)

'Also includes the text after the end position up to the next oldest date entry if found

If InStr(EndPos + 14, strText, "_:") = 0 Then
EndPos = Len(strText) + 1
' MsgBox ("Start date found. No older entries found, end position set to " & EndPos)
Else
EndPos = (InStr(EndPos + 14, strText, "_:") - 16)
' MsgBox ("Start date found. Older entries also found, end position set to " & EndPos)
End If
End If

Length = EndPos - StartPos

strReturnedText = Mid(strText, StartPos, Length)
MsgBox (strReturnedText)

Select Case CurrentRS
Case 0
strRptName = "WorkLog Report (Open, Default)"
DoCmd.OpenReport strRptName, acPreview
Case 1
strRptName = "WorkLog Report (All)"
DoCmd.OpenReport strRptName, acPreview
Case 2
strRptName = "WorkLog Report (Closed)"
DoCmd.OpenReport strRptName, acPreview
End Select

Exit_Report_Click:
Exit Sub

Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click
End Sub