Sort Expression

[myxmaster]

I have the following expression in a query. I would like to sort the
account # ascending however when I choose ascending in the grid it
groups all the 1's , then the 2,' and so on. I would like it to show
in numerical order. Any ideas.

Expr1: [Account#] & ", " & [LastName] & " , " & [FirstName] & ", "
& [City]

Also at the moment the customer is found by their account#. Is there a
way to utilize this to search by partial name or would I need to set
up a seperate query.

TIA

 

[Guest]

Is your account # field text or number? If your field is a text field, then
make a new expression in your query to format it to a number.
Account: FormatNumber([Account#],0)
and choose ascending for that field (if there is a possibility of text in
that field, then you would have to account for that in your expression,
different issue)

If the field is a number, then bring it by itself into the grid, then choose
ascending on that field.

Regarding the search issue, if you have a data entry field on a form (it can
be bound or unbound), you can refer to that in your query criteria with an
expression similar to:
Like "*" & [forms]![frmDataInput]![FieldOnForm] & "*"

Hope this helps.

 

[John W. Vinson]

I have the following expression in a query. I would like to sort the
account # ascending however when I choose ascending in the grid it
groups all the 1's , then the 2,' and so on. I would like it to show
in numerical order. Any ideas.

Expr1: [Account#] & ", " & [LastName] & " , " & [FirstName] & ", "
& [City]

Also at the moment the customer is found by their account#. Is there a
way to utilize this to search by partial name or would I need to set
up a seperate query.

TIA

Don't mix the three quite different operations of data *display*, data
*sorting*, and data *searching*.

If Account# (which, by the way, would best be renamed since # is a meaningful
date delimiter) is a Number tpe field, sort your query by Account#, LastName
and FirstName and just display the concatenation.

Similarly, if you want to search by last name - search the LastName field for
the last name.

If account numbers are not easy for users to find, and it would be better to
have them select by name, you can use a Combo Box on the form displaying the
name and searching for the account number; or you could have both if you wish.

John W. Vinson [MVP]