combining date and text fields into text field

[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.

 

[Guest]

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];

 

[Douglas J. Steele]

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]