Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    SQL syntax (Access 2000)

    For the Record Source in a report i have built an SQL clause consisting of 2 parts:
    StrUniversity and StrRating:
    Me.RecordSource = StrUniversity & strRating

    I get the values of the StrRating by the folowing code:

    Dim StrRating As String

    Select Case Forms![HighSchool]![Classroom]
    Case 1
    StrRating= "And ((Section.Number) < 20)"
    Case 2
    StrRating = "And ((Section.Number) = 50)"
    Case 3
    StrRating = "And ((Section.Number) >2)"
    End Select

    The code works fine provided that the conditions for the StrRating are written in the OnOpen event
    of the report.If i try however to write a global function called Public function FncRatings(0
    and then refer to it like that:
    Me.RecordSource = StrUniversity & FncRating
    then i receive no error but the function simply doesnt work, and the opened report does not differentiate between the ratings.
    Obviously i cannot refer properly to the function FncRatin in the RecordSource of the Report.
    Can somebody help me?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Sint Niklaas, Belgium
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (Access 2000)

    In the on open event put this code:
    Dim strSQL as String
    strSQL = StrUniversity & FncRating
    Me.RecordSource = strSQL

    Set a breakpoint on the Me.RecordSource = strSQL
    Look in the immediate window what strSQL contains. Look for missing spaces and if the parentheses are in balance.

  3. #3
    Join Date
    Apr 2001
    Stuttgart, Germany
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (Access 2000)


    when you're using the return value of a function, then you have to write the function with parantheses, even if there are no arguments to pass:

    Me.RecordSource = StrUniversity & FncRating()

    If there is no error message, I wonder if VBA thinks, FncRating is a new variable (which would be Null, thus leaving University as the only criteria). So be sure you have the 'Option Explicit' statement in the module header.

    And Francois is right: StrRating seems to begin with 'And' - it should be ' And', with a space as first character.

    Hope this helps <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

Posting Permissions

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