In the query I use some number fields that are summed and averaged except those fields which have a text field in there row that differs. Text fields with the exact content will cause other number fields in the same row to combine the row with another. Number fields don't cause the row to NOT combine. Logic because you can add substract divide etc these number fields. What I want is to append the text fields to one another. Example: when a text field contains the letter "a" and another text field contains the letter "b" I want the row in the query to contain a text field with string "ab" and other number fields combined together. But how?

=([field1]&[field2]&[field3])

It's called:

concatenation

Now, say it three times fast!

http://www.access-programmers.co.uk/ubb/smile.gif

~Dreamboat
TheWordExpert (http://www.thewordexpert.com)

concatenationconcatenationconcatenation

BUT...

There is no field2 and field3

It's just field1&field1&

Your original question is really confusing and your reply didn't help. Let me see if this is what you really wanted to ask about...

You have a field that is technically a text field, but COULD contain numbers. Sometimes it does not. You want to do math on the numeric fields and concatenation on the non-numeric fields.

If my guess is correct, then your solution is one query and one procedure run by, say, a macro.

The Query in Query Design grid has two columns. Be sure to click the sigma in the query design toolbar so you can select the Sum function.

Col 1 = Field: Test: IsNumeric(Field1), Table: MyTable, Sort: not important, Show: not important, Criteria: True
Col 2 = Field: Sum1: Sum(Field1), Table: MyTable, Sort: not important, Show: checked, Criteria: blank

The concatenation part is not going to be so easy, I'm afraid. You can only do a cross-row concatenation by writing a For loop in VBA to step through the records of the recordset, test "IsNumeric" function for the defining field, and concatenate if the answer is false.

This is because "Concatenate Column" is NOT one of the standard functions of Access. There is no aggregate function or domain aggregate function to do that. There IS a Sum function (aggregate) and a DSum function (domain aggregate).

Thanks Doc_Man for your reply even though my question was confusing and perhaps still is. But you guessed right and came up with a clear answer.