[AccessD] query criteria

Kath,

A moot point I am sure but I like to use ".value" to make sure things are behaving as I want them to.
VarActivityType = [Forms]![FrmDialogRptNoEventsAttended]![CtlActivityTypes].Value

You have dimmed "ActivityType" as a variant - As a guess a STRING would be better here than a variant - which VBA can guess something unexpected.

' -----------------------------------
Select Case VarActivityType
        Case "Any"
' -----------------------------------

>From what I understand of this you want the text "Is Not Null" returned to 'GetEventCriteriaString' if the value of 'VarActivityType' = "Any"

Is this what your criteria is?  Are you providing the input of "Any"? Maybe try

<Air Code>
' --------------------------------------------------------------------
If IsNull(VarActivityType) = True Or VarActivityType = "" Then
        GetEventCriteriaString = ""
        GoTo Normal_exit
ElseIf IsNull(VarActivityType) = False Then
	GetEventCriteriaString = "Is Not Null"
Else
      Select Case VarActivityType
       Case "CPD"
           GetEventCriteriaString = "CPD"
       Case "CNE"
           GetEventCriteriaString = "CNE"
       Case "Non CPD"
            GetEventCriteriaString = "Non CPD"
        End Select
    End If
End If
End If

'-----------------------------------------------------------

 Or have i fouled this up completely?

Cheers
Darryl.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Kath Pelletti
Sent: Wednesday, 21 May 2008 2:52 PM
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

This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses.  No warranty is made that this material is free from computer virus or any other defect or error.  Any loss/damage incurred by using this material is not the sender's responsibility.  The sender's entire liability will be limited to resupplying the material.