Problem in using A function(in module) and Group By Levels Simultaneos

[varunms]

Hi there,
Can we use a function(written in modules) and Group by levels clause Simultaneoulsy in Building a report..
Will both of them work??

To be more clear i ll explain what i am doing?

I have a function

Public Function FixTitle(strTitle As String) As String

On Error Resume Next
Dim strOut As String
If UCase$(strTitle) Like "A *" Then
strOut = Mid$(strTitle, 3) & ", " & Left$(strTitle, 1)
ElseIf UCase$(strTitle) Like "AN *" Then
strOut = Mid$(strTitle, 4) & ", " & Left$(strTitle, 2)
ElseIf UCase$(strTitle) Like "THE *" Then
strOut = Mid$(strTitle, 5) & ", " & Left$(strTitle, 3)
ElseIf UCase$(strTitle) Like "B.*" Then
strOut = Replace(strTitle, ".", "&quot
Else
strOut = strTitle
End If
FixTitle = strOut
End Function


So, hope you have understood what this function does: it eliminates the Words 'A', 'AN', 'THE' in the Titles field
and returns the Remaining word..

So, now what i am trying to do while building a Report is Print the list of Titles which comes under one Subject So, i am using a Group By Levels clause(like to Group by Subject and then By Title) While Bulding this, i am able to build the Report but the Function is not working.

So, my question group by levels and Funtions will not work simultaneously.??

thanx for ur sujjestions,
Varun.

 

[VogonPoet]

Yes, you can use functions and group by simultaneously. What specifically is not "working" with your function?

Michael

 

[MichaelRed]

Hmmmmmmmmmm,

Can't do it this way, because the group by field is modified during the generation / vcreation. Use layered queries. i.e. base the report on "qry2", which is based on "qry1", where "qry1" does the elimination of the articles (which are the FIRST word of the title).


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[varunms]

Lets Assume I have a Set of titles as below:

A Moving Title.
The Alaram Clock.
Baby's garment.
A Fickling problem.

So, by using the FixTitle() in a query :

SELECT [Rptpageno].[Series-Title] AS Series, [Rptpageno].[Title] AS Title, [Rptpageno].[CatalogNumber] AS CatalogNumber, "(pg " & [Rptpageno].[PageNo] & &quot" AS PageNo
FROM Rptpageno
WHERE ((([Rptpageno].[Series-Title])<>''))
ORDER BY FixTitle([Rptpageno].[Series-Title]), FixTitle([Rptpageno].[Title]);

i got the records sorted in this order.
The Alaram Clock. [eliminating 'The' Since A comes 1st]
Baby's garment.
A Fickling problem.
A Moving Title.

But i wanted to build a Report with this query Group by Series and Title...
So, when i do this ..the records were not sorted as i said above....

The report was built as though FixTitle is not written.
(So, to my knowledge we are trying to use Ordey by in the Query and here in report Building we are using Group By...So, Both are not working Simultaneously.)

So, it wil be of great help if you give any sujjestions to solve this.
varun.

 

[MichaelRed]

The recordsoruce 'order' is ignored if the report has sort and Group. You should remove the sort (Order by) from the query and include it wihtin the report sort and group criteria.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places