SQL syntax (Access 2000)

Topic

New Reply

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) 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?

Reply | Quote

Replies

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.

Reply | Quote

Markus,

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

Reply | Quote