Sorting concatenated fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lesli - 02 May 2007 18:34 GMT

I actually have two scenarios that I need help with. I have a form (created
and coded by someone else) that I am revising. The form is set up with
columns of data and a corresponding button for each column that the user can
click to sort the data by whichever column is selected.

First scenario: In the form's data set (a query) there are fields called
cLname and cFname. On the form that field's control source si coded
=[cLname]&", "&[cFname]. On the form I need to be able to code the button to
sort by cLname then cFname and cLname descending then cFname descending.

Second Scenarios: In the form's data set (same query as first scenario)
there is a created field coded as
ProviderName:ProviderName: IIf(IsNull([pLName]),[pAgency],([pLName] & ", " &
[pFName])) because the provider name can come from one of two different
fields. On the form I need to be able to code the button to sort by either
pLname then pFname or pAgency and pLname descending then pFname descending or
pAgency descending.

My VB skills just aren't developed enough to know where to begin!

Thanks in advance,
Lesli

Reply to this Message

Scott McDaniel - 02 May 2007 19:05 GMT

>I actually have two scenarios that I need help with. I have a form (created
>and coded by someone else) that I am revising. The form is set up with
[quoted text clipped - 5 lines]
>=[cLname]&", "&[cFname]. On the form I need to be able to code the button to
>sort by cLname then cFname and cLname descending then cFname descending.

You'd need to include the "root" fields in the form's recordsource, then sort on those. So make sure your form's
recordsource includes the cLname and cFname field, make sure they're included.

To sort, set the form's OrderBy property, and turn it on:

Me.OrderBy="cLName"
Me.OrderByOn = True

or

Me.OrderBy="cFName"
Me.OrderByOn = True

You could do this with a button click:

Sub YourButton_Click()
<sort code here>
End Sub

to remove the sort, just set Me.OrderbyOn=False

>Second Scenarios: In the form's data set (same query as first scenario)
>there is a created field coded as
[quoted text clipped - 3 lines]
>pLname then pFname or pAgency and pLname descending then pFname descending or
>pAgency descending.

The same concepts apply; the form's recordsource would have to include the pLName, pAgency and pFname fields, and you'd
sort the same way.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Reply to this Message

Lesli - 02 May 2007 19:18 GMT

Thank you! This is very helpful. Two more questions though. What is the
syntax to sort by last name then first name? Is this correct?

Me.OrderBy = "pLName" & "pFName"
Me.OrderByOn = True

and what is the syntax if I wanted to sort by the same to fields only
descending ?

Thanks,
Lesli

> >I actually have two scenarios that I need help with. I have a form (created
> >and coded by someone else) that I am revising. The form is set up with
[quoted text clipped - 41 lines]
> scott@takemeout_infotrakker.com
> www.infotrakker.com

Reply to this Message

Lesli - 02 May 2007 19:27 GMT

I figured out the syntax to sort descending

Me.Orderby = "cLname DESC"

but I still can't figure out the syntax to sort by multiple fields.

Lesli

> Thank you! This is very helpful. Two more questions though. What is the
> syntax to sort by last name then first name? Is this correct?
[quoted text clipped - 53 lines]
> > scott@takemeout_infotrakker.com
> > www.infotrakker.com

Reply to this Message

Lesli - 02 May 2007 19:45 GMT

I got it!

Me.Orderby = "cLname DESC, cFname DESC"

Thanks for starting me in the right direction.

Lesli

> I figured out the syntax to sort descending
>
[quoted text clipped - 61 lines]
> > > scott@takemeout_infotrakker.com
> > > www.infotrakker.com

Reply to this Message