Combining multiple fields into 1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I found this statment to combine the fields but I need the fields to combine
after all the data is inputed into a form before you go to the next record.

How do I incorperate this SQL statement to automaticly combine each time I
change records in a form?

EX:
SELECT Field1, Field2, Field3 AS Field4
UNION

Thanks!
 
Are you trying to store this combined value into field4 and save the result?

If so, DON'T!

As mentioned VERY OFTEN in here, you don't store calculated values. It is
redundant, inefficient, and leads to problems if one of the values is
changed. Calculated values should be performed in the forms, reports, or
tables when needed, but not saved. The table should contain the items
needed to perform the calculation, but not the actual calculation.

In your forn, you could put code in the last field's beforeupdate or exit
event to concatenate the values and test to make sure all the values are
entered.

You would not do any of this in the query. The query performs the
concatenation only on saved values. If you are changing controls in a form,
then you need to do the function on the form, not in the query.
 
In that second paragraph, I should have said....

Calculated values should be performed in the forms, reports, or QUERIES when
needed, but not saved
 
Well im not trying to calcutale them.
Here is what im trying to do:

I have a bunch of different items and different critera on these items. Now
every item and critera has a unique number code (ex: 0010) and I need to
combine these numbers from the differnet fields to create a unique ID number
for the item.

EX: item1 = 001, item1location = 293, item1repairstatus = 839
so the unique number would be 001293839 and i need this number stored in a
field for referencing.
 
Again, you store them as separate fields, not combined (calculated) fields.

In your FORM or REPORT or QUERY, you'd combine them as an unbound text box.

In a form, you could display the result in an unbound field as...

=[Field1]&[Field2]&[Field3]

same applies to a report.


In a query, youd use...


SomeCombinedName: [Field1]&[Field2]&[Field3]

You could then use the field name "SomeCombinedName" in reports or forms
based on that query.


The exact same principal applies to name entries. You store FirstName and
LastName in separate fields in your table. Then you can combine them in
your reports to display them in various formats...

Smith, John =[LastName] & ", " & [FirstName]

John Smith = [FirstName] & " " & [LastName]

Mr. Smith = [Salutation] & " " & [LastName]


etc.
 
Back
Top