combining date and text fields into text field

  • Thread starter Thread starter Kennedy
  • Start date Start date
K

Kennedy

date field is called date. text field is called text. I
wish to run update [date] + ["-"] + [text] for field
[date-text]. It won't take. Please suggest appropriate
syntax for query update. Using Access 2000. Thanks for
any help.
 
Hi Kennedy,
The SQL you need is below.

Change YourTableName to the name of your table

UPDATE YourTableName SET YourTableName.[date-text] = [YourTableName]![date] & "-" & [YourTableName]![text];
 
First of all, you should rename your date field. Date is a reserved word,
and using it as a field name can lead to problems. Similarly with using
Text. However, I'll assume may have picked those for illustration purposes
only.

When dealing with text values, you should use & as the concatenation
character, not +. Also, you don't want square brackets around the literal
string: square brackets are used to signify fields, not values. Finally, to
ensure consistency, I'd recommend explicitly formatting your date field.
Otherwise, how the value of [date] is displayed will depend on how the user
set their ShortDate format in Regional Settings. Conceivably, for today's
date (02 Aug, 2004), you could get 8/2/2004, 8/2/04, 08/02/2004, 08/02/04 or
even 2/8/04.

Having said all that, though, I just noticed you're saying you want to store
this concatenated value. Why? A field in a relational database is supposed
to contain one value only. Store them as two separate values, and
concatenate them as a calculated field in a table. Use the query wherever
you would otherwise have used the table.

Format([MyDate], "yyyy-mm-dd") & "-" & [MyText]
 
Back
Top