[AccessD] query criteria

Well, someone may have a better idea but in your query try using 'LIKE' in
the criteria.

Like GetEventCriteriaString () 

Then use an "*" in your select case:

Case "Any"
            GetEventCriteriaString = "*"        'THIS LINE NOT WORKING




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kath Pelletti
Sent: Wednesday, May 21, 2008 12:52 AM
To: Access D Normal List
Subject: [AccessD] query criteria

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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com