[AccessD] query criteria

Kath Pelletti kp at sdsonline.net
Tue May 20 23:51:30 CDT 2008


aaaarrggh.... need some help on something which should be simple.

I have a function which returns a string.

I am using that function name as the criteria in a query, ie. where criteria = GetEventCriteriaString()

One of the possible values I would like the function to return is a criteria of not null and not blank. But so far I can't even get the 'not null' to work. When I run it using the code below I get no records returned even though if I type the words Is Not Null into the query criteria manually it works. I have a feeling that this is to do with the quotation marks and strings............... hope someone knows......

-----------------------------------------
Here's the function:
Public Function GetEventCriteriaString() As String
Dim strerrormsg As String
Dim VarActivityType As Variant
On Error GoTo Err_Handler

If IsFormOpen("FrmDialogRptNoEventsAttended") = True Then
    VarActivityType = [Forms]![FrmDialogRptNoEventsAttended]![CtlActivityTypes]
    
    If IsNull(VarActivityType) Or VarActivityType = "" Then
        GetEventCriteriaString = ""
        GoTo Normal_exit
    Else
        Select Case VarActivityType
        Case "Any"
            GetEventCriteriaString = "Is Not Null"        'THIS LINE NOT WORKING 
        Case "CPD"
            GetEventCriteriaString = "CPD"
        Case "CNE"
            GetEventCriteriaString = "CNE"
       Case "Non CPD"
            GetEventCriteriaString = "Non CPD"
        End Select
    End If
End If

Normal_exit:
    DoCmd.SetWarnings True
    Exit Function
Err_Handler:
    MsgBox "Error: [" & Err.Number & "]  " & IIf(Len(strerrormsg) > 0, strerrormsg, Err.Description), vbCritical, "Error Message"
    Resume Normal_exit
End Function
    


______________________________________
Kath Pelletti
Software Design and Solutions Pty Ltd
Ph: 9505-6714
Fax: 9505-6430
kp at sdsonline.net


More information about the AccessD mailing list