Sort a Concatenated Field

[Guest]

I don't know what I am doing wronge, I have this in the RECORD SOURCE area
for a form:

Subjects: Concatenate2("SELECT Format(Last,'>')&', '& [first]&' '&[middle]&'
[DOB/'&format([dob],'mm-dd-yyyy')&']' FROM tblPerson INNER JOIN (tblOperation
INNER JOIN tblOperation2Person ON tblOperation.OperationID =
tblOperation2Person.OperationID) ON tblPerson.PersonID =
tblOperation2Person.PersonID WHERE tblOperation.OperationID=" & [OperationID])

It creates a field called Subjects that I list all the people's names and
DOB's... I want this sorted by, Last Name, First Name, Middle Name, and then
DOB. I tried putting ORDER BY at the end and it is not working.

Where does this go or how to I get it to sort in this way?

Thanks!

 

[Guest]

Hi Jeremy

There is a simply method you could use for this. Sort the query not the
form control.

Create a query and bring in the fields you want. Sort them as you want.
Create the concencated field in the query (don't sort this). Use this
concencated field

 

[Guest]

That makes sense. I have been using code to program all of my fields and not
using queries at all.... i will give that a shot!

Thnx!

jeremy

Hi Jeremy

There is a simply method you could use for this. Sort the query not the
form control.

Create a query and bring in the fields you want. Sort them as you want.
Create the concencated field in the query (don't sort this). Use this
concencated field


--
Wayne
Manchester, England.

I don't know what I am doing wronge, I have this in the RECORD SOURCE area
for a form:

Subjects: Concatenate2("SELECT Format(Last,'>')&', '& [first]&' '&[middle]&'
[DOB/'&format([dob],'mm-dd-yyyy')&']' FROM tblPerson INNER JOIN (tblOperation
INNER JOIN tblOperation2Person ON tblOperation.OperationID =
tblOperation2Person.OperationID) ON tblPerson.PersonID =
tblOperation2Person.PersonID WHERE tblOperation.OperationID=" & [OperationID])

It creates a field called Subjects that I list all the people's names and
DOB's... I want this sorted by, Last Name, First Name, Middle Name, and then
DOB. I tried putting ORDER BY at the end and it is not working.

Where does this go or how to I get it to sort in this way?

Thanks!

 

[Guest]

I got it to work. I wrote the query and had it gather the names in
alphabetical order. I then went into my form and used the concatenate
function in the forms record source property to create the list of names with
the source of the names being the query.... worked great!

Thnx!

jeremy

Hi Jeremy

There is a simply method you could use for this. Sort the query not the
form control.

Create a query and bring in the fields you want. Sort them as you want.
Create the concencated field in the query (don't sort this). Use this
concencated field


--
Wayne
Manchester, England.

I don't know what I am doing wronge, I have this in the RECORD SOURCE area
for a form:

Subjects: Concatenate2("SELECT Format(Last,'>')&', '& [first]&' '&[middle]&'
[DOB/'&format([dob],'mm-dd-yyyy')&']' FROM tblPerson INNER JOIN (tblOperation
INNER JOIN tblOperation2Person ON tblOperation.OperationID =
tblOperation2Person.OperationID) ON tblPerson.PersonID =
tblOperation2Person.PersonID WHERE tblOperation.OperationID=" & [OperationID])

It creates a field called Subjects that I list all the people's names and
DOB's... I want this sorted by, Last Name, First Name, Middle Name, and then
DOB. I tried putting ORDER BY at the end and it is not working.

Where does this go or how to I get it to sort in this way?

Thanks!