Can't get one field to sort by another field

[Sharkun]

Got asked look at a members database where they print statements for their members.

First off this DB is huge, with over 40 tables, and many queries and reports. In the main table there is a field for expiration date. I need to bring that field into this report, but have it the expiration date match for the member's ID number. The member's ID number is listed on the report, but the data being shown in this report is coming from a multitude of different tables. None of those tables have this expiration date in them. Just the main table.

I've done some light programming for in-house stuff, and the things I have tried are not working.

So what syntax could I put in the report to have Expiration Date's value be checked with the member number from that main table.

Thanks.


One note: The report is generated by a command button. It's line code is



DoCmd.OpenReport "YearStatements" , 2, , "TypeOfMember = Forms![Members]!ADType"

Can I add another variable set to this to include the one that is setting TypeofMember?

[Sharkun]

No one can offer me any advice on this?

[alansidman]

Is the member ID and the expiration date in the same table? If so, why can't you just add the expiration date field to the report in its design view?

Alan

[Sharkun]

Yes, they are in the same table. I tried that, but it wasn't sorting correctly.

I would think if we had a table of members, and we did a report, each individual report page would start at 00001 (member number) and go up by that 00002,00003,etc.

This report is sorting alphabetically by member last name, and not by member number.
I appreciate the help on this.

[alansidman]

With your report open in design view, in Access 2003, click on View-->Sorting and Grouping. You should be able to pick a field you wish to sort on. If membership is one of the fields, then sort on it and remove the sort for last name.

Alan

[Sharkun]

Got it to sort numerically like I want it, and here's what I've done after that.

Made a query to grab the member ID and the expiration date. That query matches the order in how the report prints out.

If report shows 00001 record first, the query shows 00001 first. But when I put in a text box in the report to show the value of that query, I'm just getting the first entry in the query.

Thanks again.

[Sharkun]

Any Ideas?

Thanks

[alansidman]

Any Ideas?

Thanks

Please post your SQL statement for your query. Without seeing the full picture, it is a stab in the dark as to what the issue is. Then tell us what field in the query the text box in the report is bound to.

Alan

[Sharkun]

Not sure how to get the SQL statement for the query.

In design view I have

first column

Field: ExpirationDate
Table:members
sort:descending
show:checked
criteria: blank
or: blank


In the report, the listbox's row source is

SELECT [Members Query].[ExpirationDate] FROM [Members Query];

[alansidman]

With your query open in design view, click on the drop down arrow in the upper left hand corner, you will then get several options to select. Choose SQL. Copy and paste to the forum.

Do you only have the one column in the query? Only the expiration date? It is not tied to the members ID?
Alan

[Sharkun]

This DB has turned off the drop down menus "File, Edit, View and whatever else."

I don't know how to turn it back on.

[alansidman]

Open your db holding down the Shift Key while you press the enter key. This will allow you to have full menus available to you. Depending on the version of Access you can now turn on/off menus. In 2007, its through the Office button. In earlier versions, if I recall, it is through the tools menu item. Startup.

But, what you are looking for in my earlier comment is below that line. It is just below the menu items. If you open the query in design, then the tab may look like a mini spreadsheet with an drop down arrow next to it.

Alan

[SteveH2508]

Remember that reports ignore the sort order of the underlying query. You need to specifcally set the Grouping and Sorting in the report itself.