Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Back in Access (Access 97)

    I want to sort on an expression made of two columns of string values.

    Trim(PurchaseOrderType) + trim(PurchaseOrderNumber)

    How do I do this in the Report Design tool?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Back in Access (Access 97)

    >>Trim(PurchaseOrderType) + trim(PurchaseOrderNumber)<<

    FYI, In the above expression, if either of the variables is Null, then the result it Null. If you don't want this, then use & instead of +.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Back in Access (Access 97)

    Kevin,

    You can sort/group on an expression, but as Mark pointed out, a Null will cause you problems the way you have it typed. Try this instead:

    Nz(Trim([PurchaseOrderType]),"") & Nz(Trim([PurchaseOrderNumber],"")

    You don't want a group header/footer, I assume, so make sure those are turned off. The default sort is Ascending, so you don't need to change that unless you want to, and I recommend you not change it since descending is far slower.
    Charlotte

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back in Access (Access 97)

    Thanks to all.

    BTW, there's no Nz() function in 97, is there?

    If I wanted to created a control (a calculated field) that contains these two fields concatenated, how would I do that?

    I tried slapping a text control onto the report and pasting in concatenated column names, but it did work.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Back in Access (Access 97)

    Actually, I think the Nz() function has been around since either Access 2 or Access 95. Try it and see if you get an error in a query grid.
    If you want to concatenate two fields it is simply
    MyString = [myTable].[Field1] & [myTable].[Field2]
    Of course you can get cute and put in a separator character or characters.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back in Access (Access 97)

    Access 97 does indeed have a Nz() function.

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back in Access (Access 97)

    I did not see it in the Expression Builder list of built-in functions, but when I used it in an expression it works fine. Thanks for the education!
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back in Access (Access 97)

    Ok, that's good.

    MyString = [myTable].[Field1] & [myTable].[Field2]

    Now what I need is to be able to put MyString in a header band of a report using the Report Design tool (or whatever it's called) so that I can break (group) on the value of MyString. I want to do this without having to write any code.

    I use Access so infrequently, if I ever knew how to do this, I have forgotten. Appreciate the help.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Back in Access (Access 97)

    Well, there is in *my* copy of Access 97. If I remember correctly, NZ was introduced in 97.
    Charlotte

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

    Re: Back in Access (Access 97)

    You don't put mystring there, you put [Field1] & [field2] in the sorting/grouping dialog. Don't reference the table. You're referring to fields in the report's recordset not to the table.
    Charlotte

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back in Access (Access 97)

    Char,

    Ok, that's very helpful. It works just fine. Thanks.

    Now, without writing any code, I want [Field1] & [Field2] to display in the group header. I am doing this now simply by dropping both fields onto the Group header grid. But I was hoping I could create a single field or variable or calculated field or whatever (in the report, not in the DB table) that concatenates the two fields. Then I would drop that field onto the grid.

    This is probably so simple, but nothing I try works. Thanks for helping.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Back in Access (Access 97)

    All you really need is the same concatention string as a data source for a text box in the header. You can still group on the two fields, and if you wish cause a page break so you get a new page each time you start a new combination.
    Wendell

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back in Access (Access 97)

    That's exactly what I did first thing. And I couldn't get it to work. Let me try again.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  14. #14
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back in Access (Access 97)

    Wendell, thank you. I must have had a syntax error or maybe I forgot the backets. I don't know. But it works just fine. Gee wiz, such a simple thing and I spent way too much time on it.

    Thanks again.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

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