concatenating fields

[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

 

[krash_control]

In the ControlSource of a textfield type


Code
-------------------

=Date() & "_" & [SectionNumber] & "_" & [dvID]

-------------------


The = sign is important and there is no space between Date and ()

Another thing I have noticed is that if you have text boxes on the sam
form whose control sources are SectionNumber and dvID, make sure th
name of the text box is different, in this case I'd name the text boxe
txtSectionNumber and txtdvID respectively. So you can change the cod
above to refer to the textboxes by just putting the names in the [

 

[ccw]

so are you saying I need to do this in a FORM - not right in the Table

 

[krash_control]

Well it depends where you want to display the information. If you want
to display it on a form or report then do it on either. I just re-read
your post and realise you want to store the value in a field. I don't
recommend it as the information is already in the table and repeating
it would go against the rules of database normalization.

You could also concatenate strings using a query or in vba code (I
recommend using vba code when the form/report is not bound to any
datasource). I'd normally only use a query if I need the information
for a second query or something (there are other valid reasons for
doing in the query, just can't think of any).

Just out of curiosity how do you intend to use the concatenated
string??

 

[ccw]

Yeah, I'd welcome your thoughts on a better way to achieve what I'm
trying to do....

I need to generate a unique number (or number/text combo). And I'd like
at least part of the string to mean something to me - like DATE or
Section#
And potentially there could be several entries on the same date. And
possibly even the same section# - even though it would be a separate
thing.

The project is basically a DB for video clips. The clips are taken from
meetings during a day and then divided up into topics. So an all-day
meeting could have several sections (topics). So I would want to store
all these and have unique names for the files.

These are the fields I've thought of so far:
dvID (autoNumber)
DateOfEvent
NameOfEvent
SectionNumber
DateModified
FileName

can you think of a better way?

If I make the unique string in a Form or Report, is that value actually
stored in the DB? I'd like it to actually be a value in the Table if
possible.
Maybe I should just go to Excel?

 

[krash_control]

Ok let me say that what you want to do can be done, but is a bit
pointless (I learnt this the same way). Basically you already have a
unique reference for each record (dvID) and in order to make it mean
something to you as you want, you propose combining it with the
DateofEvent and SectionNumber for example, but because this data
already exists and I assume will not change for that record, storing it
as a string in another field would be repetition. Basically look at it
this way, if you can generate a concatenated string to store it, you
can generate it when ever you need it use it again e.g. when looking up
tapes or wanting to print a report of the tapes and locations. I
recommend you read up about Database Normalization to understand what I
mean. Here is a good article
http://databases.about.com/library/weekly/aa080501a.htm

But if you insist on having the concatenated string, you can do it in
two ways, in the form when entering the data or by using an update
query after data has been entered. Here is what the query might look
like (Copy and paste it in sql view and then go into design view to
work with it more).


Code:

 

[ccw]

You're right. I can concatenate the fields at any time. I just wanted a
field where the user could select all the text and copy/paste to
something else.
But instead of them using the Table view, I'll have to make a Form for
them to use.
Thanks for all your help!