Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting Criteria by Function (Access 97 SP2)

    How do I set LIKE and/or IN criteria in the saved Query ? I tried to
    use a function but I must be doing something wrong because it does not
    work. This is the statement I have in the Criteria box " In
    (SetReportDataSource())". I also tried " Like (SetReportDataSource())".

    These are the functions I tried to use with the query:

    I tried using IN statement:

    Function SetReportDataSource() As String
    On Error GoTo Err_SetReportDatasource

    'Check Area
    If Forms!frmSwitchboardReports!chkArea Then
    strOrganisations = "area"
    End If

    'Check CHERP
    If Forms!frmSwitchboardReports!chkCherp Then
    If strOrganisations = "" Then
    strOrganisations = "'cherp'"
    Else
    strOrganisations = strOrganisations & ", cherp"
    End If
    End If

    'Check Uni
    If Forms!frmSwitchboardReports!chkUni Then
    If strOrganisations = "" Then
    strOrganisations = "'Uni'"
    Else
    strOrganisations = strOrganisations & ", uni"
    End If
    End If

    SetReportDataSource = strOrganisations

    Exit_SetReportDatasource:
    Exit Function

    Err_SetReportDatasource:
    MsgBox "Err " & Err & " -- " & Error$, vbCritical, "Error Setting
    Report Data Source!"
    Resume Exit_SetReportDatasource
    End Function

    Then I tried using LIKE statement:

    Function SetReportDataSource() As String
    On Error GoTo Err_SetReportDatasource

    'Check Area
    If Forms!frmSwitchboardReports!chkArea Then
    strOrganisations = "*area*"
    End If

    'Check CHERP
    If Forms!frmSwitchboardReports!chkCherp Then
    If strOrganisations = "" Then
    strOrganisations = "'*cherp*'"
    Else
    strOrganisations = strOrganisations & " Or
    (tblStaffDetails.Employer) Like ('*cherp*')"
    End If
    End If

    'Check Uni
    If Forms!frmSwitchboardReports!chkUni Then
    If strOrganisations = "" Then
    strOrganisations = "'*Uni*'"
    Else
    strOrganisations = strOrganisations & " Or Like ('*uni*'"
    End If
    End If

    SetReportDataSource = strOrganisations

    Exit_SetReportDatasource:
    Exit Function

    Err_SetReportDatasource:
    MsgBox "Err " & Err & " -- " & Error$, vbCritical, "Error Setting
    Report Data Source!"
    Resume Exit_SetReportDatasource
    End Function

    This is the saved query SQL I used it in:

    SELECT tblStaffDetails.Employer, tblEvents.EventID, tblEvents.Event,
    tblEventTypes.EventTypeID, tblEventTypes.EventType,
    tblEventApp.StartDate, [FName] & [Surname] AS AuthorName,
    DatePart("yyyy",[tblEventApp]![StartDate]) AS SortYear,
    DatePart("m",[tblEventApp]![StartDate]) AS SortMonth,
    Format([tblEventApp]![StartDate],"mmmm") & " " &
    Format([tblEventApp]![StartDate],"yyyy") AS DisplayDate, tblRoles.Title,
    tblRoles.Speaker, "Invited Speaker" AS RolePlayed, tblAuthors.Author
    FROM ((tblEventTypes INNER JOIN (tblEvents INNER JOIN tblEventApp ON
    tblEvents.EventID = tblEventApp.EventID) ON tblEventTypes.EventTypeID =
    tblEventApp.EventTypeID) INNER JOIN tblRoles ON tblEventApp.EventAppID =
    tblRoles.EventAppID) INNER JOIN (tblStaffDetails INNER JOIN tblAuthors
    ON tblStaffDetails.StaffID = tblAuthors.Author) ON tblRoles.EventRoleID
    = tblAuthors.EventRoleID
    WHERE (((tblStaffDetails.Employer) In (SetReportDataSource())) AND
    ((tblEventApp.StartDate) Is Null Or (tblEventApp.StartDate) Between
    [Forms]![frmSwitchboardReports]![dtmBeginningDate] And
    [Forms]![frmSwitchboardReports]![dtmEndingDate]) AND
    ((tblRoles.Speaker)=True) AND
    ((tblAuthors.Author)=IIf([Forms]![frmSwitchboardReports]![cmbSelectedEntity]>0,[Forms]![frmSwitchboardReports]![cmbSelectedEntity],[tblAuthors]![Author])))
    ORDER BY DatePart("yyyy",[tblEventApp]![StartDate]),
    DatePart("m",[tblEventApp]![StartDate]), "Invited Speaker";

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting Criteria by Function (Access 97 SP2)

    AFAIK you cannot use a function like this in a query. A function can only return one value, what you could do is:
    1. to pass the field into the query and test in the function if it is one of the desired organisations, or,
    2. build the SQL of the query and update the .SQL of the QueryDef, then execute that.

    Pat

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting Criteria by Function (Access 97 SP2)

    Patt has already indicated two possibilities. Microsoft has an MSKB article about this subject: ACC: How to Create a Parameter In() Statement with an example of a function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •