I am looking for help on something that may be very confusing to explain. I am composing a media report for our client. The report consists of publication titles, the date, audience figure, clip synopsis, etc. These are professional publications (magazines, and trade pubs). Some have dates that consist of Month/date (ie September 1, 2003), while others only have a month (ie: September 2003). I am required by my boss to include the date exactly as it is on the publication. The problem is, when I finish entering all of the data and look at the report in its final form, the dates do not go in order. There may be a September 2003, followed by a September 1, 2003, followed again by September 2003, and so on, when really all of the September 2003's should come before September 1, 2003. I have tried EVERYTHING to fix this problem, but nothing seems to work. My report is being delayed because I do not have permission to send it out with these errors on it. HELP!

Welcome to Utter Access
Can you convert the dates to text strings and sort on that? In they are treated as text, not dates, September, 2003 would sort before September 1, 2003.

George

How do I convert the dates to text strings?

How are the dates currently stored in the database? Are the month/year values in the same field in the table as the month/day/year values?

There are two columns for dates. One is titled "dates" and it requires a number. So, any time I enter a September 2003 value in this field, it automatically changes it to September 1, 2003. The other is titled "Report Dates" and this column allows me to enter the dates as they will go into the report. So, when I enter September 2003, it stays this way. The database was set up before I began working here. I do not know why there are two columns, but I do know that the date in the report is taken from the "Report Dates" column (this is how the management wants it). I believe the "Dates" column is more for a data retrieval purpose. I just looked up the format. The "Report Date" (the one we use for the report) is a text format. The "Date" column is a "Date/Time" format.

Looking at the report, the problem seems to be in the format. The titles of the publications are alphabatized in the report, but also in chronological order (Family Practice News, August 28, 2003 would still come before American Family Physician, August 29, 2003). The magazines that are sooner in the alphabet, but labeled September 2003, come before the ones labeled September 1, 2003. But the magazines later in the alphabet labeled September 2003 come after this, making them all jumbled up. Does this make sense? So maybe if I can somehow change the format of my sorting function? I have tried to look for a way to do this, but cannot find any options in the pull down menu.

I know it is very confusing for me to explain. I hope you understand what I am trying to say. Thank you for your help so far...do you have any more tips?

This explanation does make more sense because it accounts for the problem with mixing dates as dates and dates as text.

You do want to use the "Report Date" field, as it is a text field that will sort as you want, or should sort that way, I should say. It sounds like the report sorts first by magazine name, then by Report Date.

Open the sorting and group dialog by clicking on the icon:


Here's an example from one of my reports.



You'll see the sort order for the report, which operates in precedence from top to bottom. In your report it is probably sorting by magazine name, then by report date.

Thank you. I am going to try to go this route. I have been spending about 20 minutes trying to find the sorting and grouping button and it is nowhere to be found. I know it has to be somewhere because when I type in the help box "Sorting and Grouping" it shows me the button and tells me where to find it. It is just not there!? What else can make this more complicated!! I am going to try for awhile longer and come in first thing tomorrow morning and keep trying. Thanks for your help and I'll let you know if I can find the button, and if it solves the problem.

It's on the Report Design Toolbar. Right mouseclick on the menu bar to see a list of available toolbars



Check it to make it visible.

George

Thank you for your help. I found the Sorting and Grouping function and it worked (sort of). Now I have a different problem. It sorted all of the September 1's together and the September 2003's together. BUT, all of the September 1's come before the September 2003's, when they should come after the September 2003's. So it goes September 1, 2003, September 2003, September 15, 2003. I tried to reorder the Sorting and Grouping by putting the Report Date as the first to sort, the Date as the second and the Outlet name as the third. This makes the report come up with August's first, October's second and September's third (because it is alphabetical). So, I cannot do this. Can you think of ANY other thing I can try???

Can you zip up the file and post it up? Maybe I, or someone else, can come up with an idea.

Just to make sure, you want all magazines to sort first on pblication date, then on outlet. Right?

George

The magazines should sort first on report date, then be alphabatized. How may I post the file? I don't think I have 2003, I think I have 2000, if that makes a difference.

Do you have a program like WINZIP, which creates compressed files? If not, you can download a trial version here . Use it to compress the file. Then you can attach it to your post.

The version doesn't matter, although it is useful to know.

BTW: if there is an propriery information in the file that you don't want to release, you should create a copy of the db with some dummy data for testing.

HTH
George

I tried to zip the file up to show you, but I was having trouble doing it with the trial version. Then, I played around for a little longer in my database and I finally found a solution. It is not a permanent fix, because it is something that will always have to be corrected after the report is printed, but it works. What I did was in the Date field (the one that does not show up on the report) I changed the date from September 1, 2003 to September 2, 2003. I kept it September 1, 2003 in the report date field (because that is what shows up on the report). Changing it to September 2, 2003 makes it go behind September 2003 because of the ordering. It is only going to be a problem when there ARE actually publications that appear on September 2,2003. It becomes a domino effect because if there are, they will have to be changed to the 3rd in the Date format, the September 3, 2003's will have to be changed to the 4th, etc. Luckily, this time I do not have those issues. I am still going to try to make a permanent fix though, so if I can attach it for you to look at, I will. Thanks for all of your help.