Sorting in Query and Report involving look up combo box

[Esaw]

I have this query and cannot get it to sort by the StyleNumber. I think it's
sorting by the ID and not by the actual StyleNumber and that's why it looks
like it's not sorted ascending. I've also set up a report involving this
query and am not able to sort there either. Could someone give me some
insight on this? Thanks so much. Below is the Query

SELECT Contracts.ArtistName, Contracts.StyleNumber,
Contracts.StyleDescription, Contracts.ClientName, Contracts.ContractStart,
Contracts.ContractEnd, Contracts.RoyaltyRate, Contracts.Product
FROM Art RIGHT JOIN Contracts ON (Art.StyleNumber = Contracts.StyleNumber)
AND (Art.StyleDescription = Contracts.StyleDescription)
WHERE (((Contracts.ContractEnd)>=Now() Or (Contracts.ContractEnd) Is Null));

The report uses this query and then the StyleDescription uses a look up
combo box involving the StyleNumber. Could this be why I can't sort by
StyleNumber?

Thank you for any help with this.
E

 

[Klatuu]

Your query doesn't sort because there is no ORDER BY clause in it.
Reports ignore the sort order of a query. To sort for a report, use the
reports Sorting and Grouping.

 

[Esaw]

Thanks Dave. I've actually tried that and it is not working.

 

[Klatuu]

"it is not working" doesn't give me much to go on.
What have you done, what results are you expecting, and what are you getting?

 

[Esaw]

sorry about that, hopefully this clarifies it more.

I have sorted in the report using the Sorting and Grouping and I have
selected the column in the query results, right-clicked, and selected Sort
Ascending. Both ways are not sorting by Style Number. Like I said in my
original post it seems that it is sorting in another way, maybe by ID number.
The Style Numbers are entered in this format "AA123" and "AA123A" and when
sorted either in the query or the report they are not showing up in order,
AA123, AA124, etc; they seem more random.

 

[Klatuu]

AA123A would show up before AA124. That is the correct sorting for text.
What is it you are expecting?

 

[Esaw]

I'm expecting just that. Like I said it's showing up random. ex. AA123,
AA187, AA102

 

[Klatuu]

Post the SQL of your query, please.
Also, how do you have your sorting and grouping set up in your report.

Believe me, you still have a problem. The ORDER BY clause of a query sort
the output of the query based on the field or fields in the ORDER BY
And, as I said before, Reports ignore the sort order of a query. They use
the Sorting and Grouping to sort the data for the report. I have not seen
Access get it wrong in the 10 years I have been working with it.

 

[Esaw]

SELECT Contracts.ArtistName, Contracts.StyleNumber,
Contracts.StyleDescription, Contracts.ClientName, Contracts.ContractStart,
Contracts.ContractEnd, Contracts.RoyaltyRate, Contracts.Product
FROM Art RIGHT JOIN Contracts ON (Art.StyleDescription =
Contracts.StyleDescription) AND (Art.StyleNumber = Contracts.StyleNumber)
WHERE (((Contracts.ContractEnd)>=Now() Or (Contracts.ContractEnd) Is Null))
ORDER BY Contracts.StyleNumber;

I have the report grouped by Artist Name and then Sorted by Style Number
using Group/Sort.

As I mentioned in the original post, I have a lookup set up between the
StyleNumber and StyleDescription, could this be affecting the results in the
report?

 

[Klatuu]

Forgot about the Lookup.
Yes, that could definitely be the problem. Lookup fields are evil and
should be avoided.

 

[Esaw]

okay, well I need to have the style number and the style description in the
query and the report and the best way to do that is having the look up field.
Unless there is another way. I have it set up this way because I enter Art
into a table by Style number and description, and then it is used in other
tables, Contracts, Royalties, Submissions, which are all subforms in the
database. Much easier to enter things by style number and have the
description just pop up.

I have also tried exporting the query to an Excel sheet but when I do that
the ID number comes up in the StyleNumber field and then I don't have the
Style Description either, that field ends up blank. any ideas on that or am I
just not able to do this with a look up field involved?

 

[Klatuu]

That is the problem with lookup fields. You don't get the value you think
you are getting. You get the lookup value.
Since I do not and never have used lookup fields, I am not the best person
to tell you how to control this situation. But I do know that you don't
really need lookup fields. You can do your searching and displaying of the
description fields in a way that doesn't require lookup fields.

 

[Esaw]

Here's the code for one of the subforms (it's set up the same in the others)
Maybe I am searching and displaying and not using a "lookup"...I don't know.
My lingo may be bad... Thanks for all your help with this

Private Sub Contracts_AfterUpdate()
Me.StyleDescription = Me.ArtID.Column(2)
End Sub

Private Sub ArtID_AfterUpdate()

End Sub

 

[Klatuu]

What is the row source query for that combo?
Are you aware Column(2) actually returns the 3rd column?

 

[Esaw]

SELECT [Art].[ID], [Art].[StyleNumber], [Art].[StyleDescription] FROM Art
ORDER BY [StyleNumber];

Yes I am aware that it returns the 3rd column. I had help setting that up
through this community.

 

[Klatuu]

If you run that query and look at the second column - Column(1), what do you
see?
Is it the actual StyleNumber?
--
Dave Hargis, Microsoft Access MVP

SELECT [Art].[ID], [Art].[StyleNumber], [Art].[StyleDescription] FROM Art
ORDER BY [StyleNumber];

Yes I am aware that it returns the 3rd column. I had help setting that up
through this community.

What is the row source query for that combo?
Are you aware Column(2) actually returns the 3rd column?