Back in Access (Access 97)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

That’s exactly what I did first thing. And I couldn’t get it to work. Let me try again.

Reply | Quote

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.

Reply | Quote

Access 97 does indeed have a Nz() function.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote