[AccessD] query criteria

Exactly how are you using the criteria? 
It looks to me as though you are getting an implicit "="  somewhere 
ie you must be  making "CPD" into "= CPD". 
Trouble is that doing that with "IS NOT NULL" ends up with "= IS NOT NULL"



On 21 May 2008 at 14:51, Kath Pelletti wrote:

> 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