default table values

[Guest]

In a table how do I get the values of one feild to show in another feild?
example:
feild 1 named: Request No
feild 2 named: Serial No

I need the value of Request No to be =Year(Now()) & "-" & the value of
Serial No

 

[Guest]

Don't do it that way. You would be introducing redundancy which leaves the
door open to update anomalies. Store the date/time value when each row is
inserted into the table by having a column, DateTimeEntered say, with its
Default Value property set to Now() in table design. Don't have a Request No
column in the table but compute it in a query or in a computed control in a
form whenever required with:

[Serial No] & "-" & Year([DateTimeEntered]

Even though you don't need the full date time value in the DateTimeEntered
for this I'd nevertheless use the Now() function as the default. You might
well find it will come in extremely useful in the future to have a set of
unique values which determines not only when but the order in which rows
where inserted, something for which an autonumber column cannot be fully
relied on. Users never need see the DateTimeEntered column, it will just
quietly get on with its job in the background.

Ken Sheridan
Stafford, England

 

[Guest]

Create it in a query

Select [Serial No] , Year(Now()) & "-" & [Serial No] As [Request No] From
TableName

Ot in a field in the query it will look like
Request No:Year(Now()) & "-" & [Serial No]

 

[Guest]

ok, let me try this again, maybe I did not clarify what I needed.

1. no date is entered in either feild. It is added in the default value of
Request No feild. (ie. Year(Now()) & "-" & the value of Serial No

2. The value of Serial No is supposed to be incremented by one with each new
entry (which desn't work either) i.e start incrementing number at 300 and add
one for each new entry.

can get it to work in a form, but does not save value in table?

Don't do it that way. You would be introducing redundancy which leaves the
door open to update anomalies. Store the date/time value when each row is
inserted into the table by having a column, DateTimeEntered say, with its
Default Value property set to Now() in table design. Don't have a Request No
column in the table but compute it in a query or in a computed control in a
form whenever required with:

[Serial No] & "-" & Year([DateTimeEntered]

Even though you don't need the full date time value in the DateTimeEntered
for this I'd nevertheless use the Now() function as the default. You might
well find it will come in extremely useful in the future to have a set of
unique values which determines not only when but the order in which rows
where inserted, something for which an autonumber column cannot be fully
relied on. Users never need see the DateTimeEntered column, it will just
quietly get on with its job in the background.

Ken Sheridan
Stafford, England

In a table how do I get the values of one feild to show in another feild?
example:
feild 1 named: Request No
feild 2 named: Serial No

I need the value of Request No to be =Year(Now()) & "-" & the value of
Serial No

 

[Guest]

You misunderstand the main point at issue here. By having both the Serial No
ands Request No columns in a table you are introducing redundancy because
part of the latter is determined by the former. The elimination of
redundancy is achieved by normalizing tables. The correct way to achieve
what you want is as I described in my last post and have two columns, one
with the date/time when the row was inserted into the table and one with the
sequential serial numbers. There is now no redundancy and therefore no
possibility of update anomalies. The Request No should not be stored but
computed by concatenating the values of the other two columns, inserting the
dash between them. This can be done in queries , on forms or in reports.

How you insert the sequential values for the Serial Number column depends on
whether the data base is in a single user or multi user environment. Both
are done in the data entry form, however. In a single user environment there
can be no conflicts between users inserting a row simultaneously so its very
easy to insert the next number in the form’s BeforeInsert event procedure by
using the DMax function to get the highest existing number , to which 1 is
then added e.g.


Me.[Serial No] = DMax("[Serial No]", "[Your table]") + 1

In a multi user environment in a network this could result in a conflict
with two users getting the same number. One way to avoid this is to store
the latest number in an external database on the server, which is opened
exclusively in code to get the next number. You’ll find a demo of this at
the following link:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps

Ken Sheridan
Stafford, England