Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following query and it returns address records in the proper ascending order:

    1 Allen St
    8 Allen St
    15 Allen St

    <pre>SELECT qrytbl_Dlicense.strAddrNo, qrytbl_Dlicense.strAddrName
    FROM qrytbl_Dlicense
    ORDER BY qrytbl_Dlicense.strAddrName,
    IIf(IsNull([strAddrNo]),True,IsNumeric([strAddrNo])),
    IIf(IsNull([strAddrNo]),0,Val([strAddrNo]));
    </pre>


    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Do you have a sort order set in the Sorting and Grouping window of the report? That will override the code. If you want to set the sort order in code, you should NOT set the sort order in the Sorting and Grouping window (grouping levels are OK)

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    There is nothing in Sorting and Grouping window

    Any other thoughts?

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I will work on that

    Is there a way to set Sorting and Grouping window in open event with my criteria previously posted?

    Thanks, John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Look up CreateGroupLevel in the VBA help. But I would think twice before trying it. It is much easier to set sorting and grouping in design view than in code.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    Here you go

    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Create a query:

    SELECT tblqryResultsDL.*, IIf(IsNull([strAddrNo]),True,IsNumeric([strAddrNo])) AS Sort1, Val(Nz([strAddrNo],0)) AS Sort2
    FROM tblqryResultsDL

    Set the RecordSource property of the report to the name of this query or to the above SQL (in design view or in code)
    Use the calculated fields in the OrderBy property:

    Me.OrderBy = "strAddrName, Sort1, Sort2"

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hello Hans

    You recommendation worked great on my report

    I still have a problem isolating records by Start and End range of address no and address name

    See attached db, frm_Address_Criteria, Find Matches - Name & Number

    For example, I want to be able to query the range 1Allen St through 17 Allen St, ther should be 10 records

    There could be alpha characters in the address number field

    Thanks, John

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    This could get very complicated. Your query selects on street and number separately, so if you select

    Between "5 Allen" and "7 Beach"

    you won't get 9 Allen or 1 Arthur or 3 Beach. Is that what you want? (My recommendation would be to forget about this unless you REALLY need it)

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    For example,

    I want to be able to query the range 1Allen St through 17 Allen St, there should be 10 records returned

    I want to be able to query the range 8 Allen St through 15 Allen St, there should be 6 records returned

    I want to be able to query the range 16 Allen St through 8133 Any Jenn, there should be 4 records returned

    Is there a better way to set up select and query to accomplish the above?

    Thanks, John

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Your examples skillfully evade the question I asked. Please try again.

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Between "5 Allen" and "7 Beach"

    <you won't get 9 Allen or 1 Arthur or 3 Beach. Is that what you want?>

    In the above example I would want 9 Allen 1 Arthur and 3 Beach

    Thanks, John

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Does the attached do what you want? Please test thoroughly on real data.

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Report Sequence? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Hans

    It

Posting Permissions

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