sort order problem (’97)

Topic

New Reply

I have a form which allows users to enter a month/year, or enter a quarter and year, for each entry. The report which displays the data uses a query which combines those fields into one field [XXX] which holds either mmm-yyyy, or if that is null, then yyyyQq.
When the query runs it drops the rows in the correct descending sort order for the field [XXX]. But the report detail section, where the [XXX] field and the others displayed are shown forces a choice of sorting ascending or descending, but it’s alpha or numeric, not in date order. Thus Mar-2002 is followed by Jan-2002, followed by Feb-2002 etc.
Is there a way to make the detail section sort in date order? Or to rewrite the query /add a numeric field it can use?
Thanks in advance. You all are so good at answering questions.
Judy

Reply | Quote

Replies

If you have a date as part of the underlying source of the report, then yes you can sort on this date.

Are you asking how to sort in a report, if so, click on View in report design view and choose Sorting and Tab order.
Next select from the left column the field you wish to sort on and from the right hand column choose Ascending or Descending.

I hope I have understood your question.
Cheers,
Pat

Reply | Quote

Pat is on the right track, but from your description of the field you want to sort on, it isn’t a date. In order to make it a date, you want to use the DateSerial function. You specify Year, Month, and Day of Month as inputs to the function – if you don’t know the day of the month, simply make it 1. Then you can format it on the report as MMM-yyyy and it will display correctly and sort correctly.

Reply | Quote

Hi Pat and Wendell,
I do know how to make a field sort in a report, but it only allows an alpha or numeric sort, not a date sort for what I created. And making a combined field be a date when I have both yyyyq and mmm-yyyy – well, it wants to look at the results as a text field not a date field. I assume that’s because the data is mixed. Some values are 2000Q1 where =”yyyy”& “Q”& “q” and others are Jan-2000 where =”mmm-yyyy”.
I’ve considered forcing all Q1 values to equal Mar etc, but it then is hard to distinguish between actual quarter values and month values. The data being entered can be done monthly, quarterly, semi-annually, once only, etc. and I wanted to make it as flexible as possible.
Judy

Reply | Quote

I suspect that you would be best off using a hidden field to sort your data, either on the order of data entry or by forcing your Q1 to March etc.

Reply | Quote

That’s fine, but since folks may enter information in any order, choosing to use entry date, or ID#, won’t work. It would have to be something that actually did sort against the real date value and assign a different, sortable value. And I havenm’t figured out the best approach for that.
If I wanted to set up a field, or query value, of Q1=March, Q2=June, etc., what would be the best way to write that so the value carried forward to the combined field?

Reply | Quote

This does get kind of sticky – the trick with date sorts is that they are really numeric. Date fields are actually stored as a certain number of days from long ago (I forget what the actual zero date is, but something like 1/1/1900) as the integer portion, and the hours, minutes and seconds as the decimal portion. So when you sort a date field you are actually doing numeric sort.

So the trick is to create a pseudo date from the information the user enters so you can sort on it. Bat17’s suggestion of a hidden field is a good one, but you can also display the pseudo date so it looks like the entry made by the user if you use the formatting capabilities. If you want to display the data just as the user entered it, then the hidden field pseudo date is probably the best approach. Hope this helps.

Reply | Quote

If date field sort as numeric data, why does the field in the report sort on alpha? The field I created in the query is read as text. Is there a way to force it to be numeric, when it contains both year-month and year-quarter information?

Reply | Quote

The only way I know of to make it numeric AND make it sort in date order is to force in into a date with the DateSerial function. In that case it definitely will be. To do that you will have to parse the input data to figure out which kind it is, and then plug in a psuedo date as I suggested. In other words, if it turns out to be MM/YYYY then you would use
SortDate: DateSerial(YYYY,MM,1)
but it it is quarters then you would need to do a bunch of IFs, or a Select statement – it would look something like this
SELECT CASE Quarter
CASE Q1
SortDate = DateSerial(YYYY,4,0)
CASE Q2
SortDate = DateSerial(YYYY,7,0)
CASE Q3
SortDate = DateSerial(YYYY,10,0)
CASE Q4
SortDate = DateSerial(YYYY+1,1,0)
END Select

Of course you can’t use Select statements in queries, but it occurs to me that if the user isn’t putting in true dates then you will need to do a fair bit of validation as they enter the date. In that case you could use the code to calculate the date and actually save it in the record. Otherwise you would have to resort to doing record sets with DAO or ADO – a fair bit more comples than just using a form bound to a query. Actually, it seems to me you will need code anyhow – I assume the date is being put into a single field and is stored either as MM/YYYYor Qx/YYYY, so you will need to do some validation to be sure that they put in either a valid month or valid quarter and a valid year – presumably either the current year or last year. So you will already have a fair bit of VBA going on already – might as well do it then and there.

I should add that I used a trick in the SELECT statements – when you do a date serial with the day set to zero (0) it returns the last day of the month. That way the dates would sort with Jan, Feb, Mar, Q1, Apr, May, Jun, Q2 and so on. Hope this isn’t too muddled an explanation.

Reply | Quote

Actually, CDate() will convert a date string in the format mm/yyyy to a date using the first of the month in the string.

Reply | Quote

Actually, there are 3 fields -field 1 for entering month/year; field 2 for quarter and field 3 for year. I’m having to make a query field to combine the quarter and year, another to put the quarter and year plus the month-year in one filed, and now I’ll have to work against that.

I’m sure there is an easier way to set it up, but….. I didn’t hink one could have two types of dates in one input field.
Thanks, I’ll let you know what I figure out. I’ll play with this when I get back to work tomorrow.

Reply | Quote

Reading your post again, I see that the query returns the results in the right order for you. If you are not doing further sorting/grouping within the report, then make sure that the OrderByOn property is set to yes in the property sheet and it should sort in the same order asthe query.

HTH

Peter

Reply | Quote

I just tried this, but no luck. I even made a group header and put the ‘when’ field in it, but it still sorts as text. The query is taking two date fields and putting them in one If/Then field. Is there a way to make the new If/Then field think it is dates rather than text? How can that be done when there are two types of dates?

I suspect Wendell was correct- I’m going to have to force quarters into months, somehow. I’ll keep trying.

Thanks for the suggestion – it sounded great.

Reply | Quote

You could use a custom function in the query to add a “serial” number to your data and sort by that.

Function funAddSer(var As Variant)
Static lngCounter As Long
lngCounter = lngCounter + 1
funAddSer = lngCounter
End Function

and call it with lngCounter:funAddSer([some field name here])

It seems to need a field name passed to it for it to work!

HTH

Peter

Reply | Quote

My apologies for the delay, but when I went to the query to add this function, I couldn’t figure out how to do that. I don’t see how to build code in the query other than SQLview.

I have
ORDER BY [Ongoing Data].MONTHYEAR DESC , IIf([MONTHYEAR] Is Not Null,Format([MONTHYEAR],”mmm yyyy”),[YEAR] & “Q” & [QUARTER]) DESC;

Can you help me further so I can add your function?
Thanks – Judy

Reply | Quote

Why not have 2 different fields, the concatenated field you have at the moment and a date field that Wendell suggested. Sort on the date field but only show your concatenated field in the report.
How does this sound?
Pat

Reply | Quote

That would work just fine, except I haven’t figured out how to make a single format date field from what I have. Wendell sent a set of select statements but I’m not sure how those get built into the query itself. I could possibly write an If..then code for a Expression1 type field, or try a multiple IIf() or IIf () or type statement. I just don’t know the best way to write what is needed.
Judy

Reply | Quote

If you can guarantee that the [QUARTER] value is indeed 1,2,3 or 4 then try the following in a query:

iif(isnull([MONTHYEAR]), DateSerial([YEAR], [QUARTER]*3+1,0), CDate([MONTHYEAR])) as SpecialDate

This could then be used in the ORDER BY clause.
I have used clues by both Charlotte(CDate) and Wendell (0 day in DateSerial returning the last day of the previous month).

I hope this helps,
Pat

Reply | Quote

I’m stymied! I’ve tried various different things and still can’t get it to work. I’ll attach a pared down version here in the hopes that someone else will be able to see whatever it is that I am missing. It contains three different ID#s – 123, 311 and 378. All of them have entries in both styles – month year and quarter year. No matter what I try it sorts Mar Jan Feb. And now that I changed it to include the Special Date statement, it doesn’t even give back all the rows it should.
In advance, I thank you all…….Judy

Reply | Quote

Hi Judy,
Try the following at the front of the query:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;

What this effectively does is to signal WHEN to be a date field. I can cerrtainly get a lot of records (20 for 1/1/00 thru 16/4/02 for 123,311,378) returned.
I have posted your changed sample db back to you.

Hope this solves the problem!!
Pat

Reply | Quote

Thank you! I had put the ‘start date’ and ‘end date’ in the query parameters earlier, but forgot to enclose that in brackets. I made one other modification and it is just great.
Thanks again for your patience, Pat.
Judy

Reply | Quote

Those 2 tables you reference in your query, where are they? Are they hidden?
Pat

Reply | Quote

No, both are shown on the Tables tab. Why?

Reply | Quote

I could not see them there.
When I tried to create a new query I still could not see them in the available tables list.
Strange.
Cheers,
Pat

Reply | Quote

I had them hidden, and I guess only I can see them. I hadn’t considered that when I sent it to you. Sorry.
Judy

Reply | Quote

How do you hide tables?

Reply | Quote

You can hide them by setting their dbHiddenObject property to true, but doing that in Access 97 is an extremely bad idea since they will be deleted when the database is compacted. I’ve never tried it with Access 2000. The other way is to use the UI property dialog of the table and check the Attributes: Hidden checkbox.

Reply | Quote

Thanks Charlotte.
I don’t know how to set the dbHiddenObject property to true. How do you do that?

The other way (set the Attributes: Hidden checkbox after right clicking on the table and into properties) is the way I would go as this nicely hides the table from being changed by prying eyes.
Pat

Reply | Quote

You can hide tables, forms, queries – whatever you don’t want other users to see. What they can’t see they can’t change.
Highlight the item you want to hide , for instance under the table tab highlight one of the tables. [You have to do this one by one] Right mouse on the highlighted item, scroll down to Properties.That will bring up another screen and in the lower left corner is a box you can click to hide the item. Now when you open the db, you will still be able to see it. But when someone else opens it as a shared application, that item will not be visible.
Let me know if that works for you.

Reply | Quote

What I have found when you hide the table and exit from the database then come back in, that I do not see the tables. I don’t know how you manage to?
Pat

Reply | Quote

in Tools/options/ there is a view tab wich lets you choose to see hidden objects among other things.

HTH

Peter

Reply | Quote

Thanks Peter

Reply | Quote

You really should still see the names, but they will be faint grey. If you can’t see them at all, check under Tools, Options and see if SHOW Hidden Objects is checked. The other thought is, who is the administrator of your db?
Let me know.
Judy

Reply | Quote

No I don’t see them. I know to goto show if SHOW Hidden Objects is checked. The administrator is admin, no-one special.
I wonder how it is that you can see them.
What time is it at your place at the moment, it’s 13:10 here on Sat.
Pat

Reply | Quote

Most peculiar! Could you ask the Lounge if they can explain it? I have no more ideas on it.
You said it was 13:10 your time when you wrote that, but the Lounge recorded it as 22:08. Right now for me, in Central Time Zone, it is 6:26PM.
Judy

Reply | Quote

Unfortunately, you can’t use a SELECT statement in a query, as it is VBA. I was assuming that you would run a small VBA routine on the BeforeUpdate event of the data entry form that would probably update a hidden control on your form containing the field with the real date format bound presumably to a new date field in your table. If you want to try it at the query level, look at the SWITCH funtion – I believe it’s available for use in queries. As long as you aren’t dealing with lots of possibilities, it might do what you need in converting the Q entries into date fields. Also, Charlotte’s suggestion of using CDate on the field as you enter it would obviate the need for complicated statements in the query. In fact that might let you simplify things so you don’t need as many entry fields. I presume that if someone goes back and looks at the form after data has been entered, you would want them to see the data exactly as they entered it. If that isn’t a requirement you may be able to get by with one date field.

Reply | Quote

I didn’t think about using a BeforeUpdate statement. I’ll try that Monday. (The db is too big to bring home)
The date field already creates a mm/dd/yyyy date. It’s getting the joint year field and quarter field responses to turn into mm/dd/yyyy that seems problematic.

Reply | Quote

i having problem trying to sort in order of alphabetical in unbound textbox. What I’m trying to show is after the user have selected an items from combobox then it shall display some data inthe detail form. But user have some difficult to see whos comes first. So in order to have a data in order the user want to specific by account name in order. How do make it happen?pls help..

Reply | Quote

You wrote “sort in order of alphabetical in unbound textbox” – do you want to sort text within a text box? Seems strange…

Or did you mean that the data in the combo box (not text box) should be sorted? I assume that the Row Source of the combo box is a table now. In that case, create a query based on the table that selects the account names in alphabetical order, and use this query as Row Source of the combo box. If the Row Source is a query already, edit it and make it sort on account name.

Reply | Quote