concatenation

[ccw]

Please excuse the basic question....I'm not entirely new to Access, jus
fairly new to doing calulations or default values within Access. In th
past, I've done all my coding work in a web page that accesses Acces
DB's. So I'm green when it comes to Forms & Reports. I've mostly jus
dealt with Tables and Queries.

I'm trying to make a Field (MyFileName) in a Table have a value whic
is concatenated from a date plus 2 other Fields in the same Table an
Record. I don't want to Sum the values, just put them in a string.
And in order to make things a little more readable, I'd like to have a
underscore character between the 3 values.
so a sample might look like: 20040129_1_38

I'm assuming I should put this in the Default Value of the Field I hav
named "MyFileName".
Please let me know if this is not possible for a default value, or i
it should go somewhere else.

I've tried a variety of things similar to this:
Date () & "_" & Forms![VideoFiles]![SectionNumber] & "_"
Forms![VideoFiles]![dvID]
Date () & "_" & [SectionNumber] & "_" & [dvID]

And I'm getting different error messages, but mostly that it can't fin
a value or something like that.

My fields are:
dvID = AutoNumber
SectionNumber = number (default value is 1)

Can I do this in a Table? Or do I have to use a Form, Query? Or what

 

[Nikos Yannacopoulos]

Default values do not work in tables when required to read
other fields; the reason is that the default value is
calculated at the time the new record is created, and at
that point the referenced fields are not populated yet.
The same goes for forms more or less.
So, if my guess is correct, the problem is not the formula
(that seems OK, especially the long - safe - version).
Assuming you stick with the form, you could forget the
default value and use a macro or code to set the value of
MyFileName. This could be triggered by, say, the on
doubleclick event of the corresponding control, or the on
change field of SectionNumber (if you remove the default
from that and make it a mandatory field in the table it
will never be forgotten).

HTH,
Nikos

 

[Tim Ferguson]

I'm trying to make a Field (MyFileName) in a Table have a value which
is concatenated from a date plus 2 other Fields in the same Table and
Record.

No you don't. You may very well want to display the long string -- and this
is quite easy to do -- but there is no good reason to store it in the
table, and about forty years of R Theory research to explain why it's a bad
idea.

Base your form or recordset on a query that includes the calculated string;
or else use the events on the form to put it together and poke it into a
text box; or use a db-aware control to watch the values and update it
automatically. I have not seen any web pages where one text box updates
itself on changes to another, but I should imagine it can be done. It's
easy on an Access form, of course.

Hope that helps


Tim F