[AccessD] query criteria

Darryl - have used variant so that nulls can be handled.

But yeah - bit of a moot point as choices for users are:
CPD
CNE
Non CPD
Any

(Where they select 'any' I would like any of the other 3 choices to become 
the criteria).

After mucking around for hours with trying to get the function to return 
"CPD" or "CNE" or "Non CPD" as the criteria, I thought - well - if the user 
selects "any" then I can just check for not null and not blank, as that 
field will always have one of those 3 values in it.

Kath


----- Original Message ----- 
From: "Darryl Collins" <Darryl.Collins at coles.com.au>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Wednesday, May 21, 2008 3:06 PM
Subject: Re: [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.
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com