Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort order problem ('97)

    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. [I hope that makes sense.]
    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

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: sort order problem ('97)

    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

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: sort order problem ('97)

    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.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: sort order problem ('97)

    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.
    Wendell

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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?

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: sort order problem ('97)

    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
    <font color=448800>SortDate: DateSerial(YYYY,MM,1)</font color=448800>
    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
    <font color=448800>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</font color=448800>

    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.
    Wendell

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sort order problem ('97)

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

  11. #11
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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.

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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

  13. #13
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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.

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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

  15. #15
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort order problem ('97)

    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

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •