OnClick Event Calls Function to Evaluate

[ewong1]

I am attempting to evaluate what my user has inputed in the fields availabe on my form [frmQuery]. The two available fields are [dtmBeg] and [dtmEnd].

Here is the code:

Option Compare Database
Option Explicit

Public stDocName As String
stDocName = ""

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click
    
    stDocName = Evaluate()
    
    Dim stPreview As String
    
    stPreview = stDocName
    DoCmd.OpenReport stPreview, acPreview

Exit_btnPreview_Click:
    Exit Sub

Err_btnPreview_Click:
    MsgBox Err.Description
    Resume Exit_btnPreview_Click
    
End Sub

Public Sub Evaluate()

    If IsNull(Me.dtmBeg) And IsNull(Me.dtmEnd) Then
    stDocName = "rptPQ_CPSAll"
    Else
        If IsNull(Me.dtmBeg) And Not IsNull(Me.dtmEnd) Then
        stDocName = "rptPQ_CPSUpTo"
        Else
            If Not IsNull(Me.dtmBeg) And IsNull(Me.dtmEnd) Then
            stDocName = "rptPQ_CPSAfter"
            Else
            stDocName = "rptPQ_CPSBetween"
            End If
        End If
    End If
    
End Sub

I was hoping someone could help me decifer what it is that I am doing incorrectly here. Basically what I am looking for are the following conditions:
Null Beginning Date queries records before the end date
Null End Date queries records after beginning date
no null fields queries records between beginning and end date

If someone can help with this task I would appreciate it. Thanks!

 

[pbaldy]

What error do you get?

 

[ewong1]

Error Message

The expression On Click you entered as the even property setting produced the following error: Invalid Outside Procedure.

 

[pbaldy]

Figured you'd get that at least. This line has to be within a procedure:

stDocName = ""

 

[ewong1]

OK, So I've moved the [stDocName = ""] portion into a method, and now I'm getting the following error message: "Compile Error: Expected Function or Variable" and it yellow highlights my Private Sub btnPreview_Click line and grey highlights my Evaluate() text after the stDocName = Evaluate().

 

[Mile-O]

You have Evaluate listed as a Sub. Subs don't return values; they simply perform operations. You need to list it as a function so that it returns a value.

So, just change this line:

Public Sub Evaluate()

to

Public Function Evaluate() As String

Change End Sub to End Function for Evaluate too.

 

[Mile-O]

Also, stDocname = "" is unnecessary as the default for a string is "" - or, to use its compiled constant: vbNullString

 

[pbaldy]

SJ is spot on, and I'll raise a point you may want to consider. It would be more efficient to have 1 report that returned all records, instead of 4 reports. Then in your function, build a wherecondition that is used by the OpenReport method (more info in Help and on this site). That will restrict the records as you desire, without having to have a separate report/query for each possibility.

 

[ewong1]

I am sorry, I'm a total newb to writing vb. How do I create a wherecondition in my open report function that will query based on my dates?

 

[pbaldy]

A search of this site turns up numerous discussions, including this one that I took part in:

http://www.access-programmers.co.uk/forums/showthread.php?t=82477