Query/Report problem

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I have an split database application that, amongst
other facilities, gives the user the option of printing
mailing labels. The application runs in an Access 2K
environment.

I recently received a request to change the sort order
of the labels. No big deal... right? The label report's
RecordSource is based on a Union Select, so a simple
addition of a SQL "Order by" should easily do the trick.
So, I in fact added the "Order by" you see in the query
(I've included below) and ran the query. The ordering
was exactly as desired. However, when I ran the
report, the order was unchanged from the original
version of the query.

I inserted a "Msgbox" in the codepage just before
the OnOpen code terminates to verify that the correct
query appears in the Me.RecordSource. With that,
I'm in total mystery as to how the query shows the
correct recordset when I run the query, the report's
properties clearly shows the correct query name,
yet the order of the report's labels DO NOT match
what is seen when the query is run by itself.

What am I missing here?

SELECT [LastName],[FirstName],[POBox],[Address],[CityState],[Zip],
[Salutations].[Salutation],[Active] AS Suffix
FROM [Registry] INNER JOIN [Salutations]
ON [Registry].[Salutation] = [Salutations].[SalID]
WHERE [NewsLetter] = True
UNION SELECT [FamilyName],[FamilyName],[FamilyPOBox],[FamilyAddress],
[FamilyCityState],[FamilyZip],[Salutations].[Salutation],[FamilySuffix]
FROM [Families] INNER JOIN [Salutations]
ON [Families].[FamilySalutation] = [Salutations].[SalID]
WHERE [FamilyNewsletter] = True
ORDER BY [Zip],[LastName]

Thanks,
Bill
 
The sort order of records in a report is defined via the Sorting And
Grouping dialog.

As you found, it does not reliably follow the sort order of its RecordSource
query.
 
Allen,
Got it!
Thanks,
Bill



Allen Browne said:
The sort order of records in a report is defined via the Sorting And
Grouping dialog.

As you found, it does not reliably follow the sort order of its
RecordSource query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bill said:
I have an split database application that, amongst
other facilities, gives the user the option of printing
mailing labels. The application runs in an Access 2K
environment.

I recently received a request to change the sort order
of the labels. No big deal... right? The label report's
RecordSource is based on a Union Select, so a simple
addition of a SQL "Order by" should easily do the trick.
So, I in fact added the "Order by" you see in the query
(I've included below) and ran the query. The ordering
was exactly as desired. However, when I ran the
report, the order was unchanged from the original
version of the query.

I inserted a "Msgbox" in the codepage just before
the OnOpen code terminates to verify that the correct
query appears in the Me.RecordSource. With that,
I'm in total mystery as to how the query shows the
correct recordset when I run the query, the report's
properties clearly shows the correct query name,
yet the order of the report's labels DO NOT match
what is seen when the query is run by itself.

What am I missing here?

SELECT [LastName],[FirstName],[POBox],[Address],[CityState],[Zip],
[Salutations].[Salutation],[Active] AS Suffix
FROM [Registry] INNER JOIN [Salutations]
ON [Registry].[Salutation] = [Salutations].[SalID]
WHERE [NewsLetter] = True
UNION SELECT [FamilyName],[FamilyName],[FamilyPOBox],[FamilyAddress],
[FamilyCityState],[FamilyZip],[Salutations].[Salutation],[FamilySuffix]
FROM [Families] INNER JOIN [Salutations]
ON [Families].[FamilySalutation] = [Salutations].[SalID]
WHERE [FamilyNewsletter] = True
ORDER BY [Zip],[LastName]

Thanks,
Bill
Click to expand...
Click to expand...
 
Back
Top