Sorting concatenated fields

[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
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
ProviderNameroviderName: 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

 

[Scott McDaniel]

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.

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
ProviderNameroviderName: 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.

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

 

[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?

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

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
ProviderNameroviderName: 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.

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

 

[Lesli]

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?

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

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
ProviderNameroviderName: 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.

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

 

[Lesli]

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

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?

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

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
ProviderNameroviderName: 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.

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