Adding a calculated field to a table...

[John]

I need to add a field to my table that will default to a
value based on another field in that same table. For
clarity I'll give an example...

I have a table that has order information in it. One of
the fields is 'ScheduledProductionDate' the product that
has a scheduled production date needs to be picked from
inventory. So I have another field in my table
called 'PickDate', that value should default to one day
earlier than 'ScheduledProductionDate' after you put that
value in.

Is this possible? I can't believe it would be possible in
Excel and not in Access?

Thanks for your help!

 

[Nikos Yannacopoulos]

John,

You can't put calculated fields in tables. You can do so in the form you use
to enter new dat ain the table, or the query you use to append data to the
table, if they come form somewhere else, but not in the table itself.
Before embarking on doing that, though, you should ask yourself wheteher you
really need to store this information in the table; if it can be calculated
based on another field, it can be calculated real time when you need it,
without being stored at all!

HTH,
Nikos

 

[Allen Browne]

You can do this in a form, but not in the Default Value property of a field
in a table.

The Default Value is assigned before the record is added. At that time,
there is not a ScheduledProductionDate yet, so you cannot assign one day
earlier than the non-existent date.

You can use the AfterUpdate event of ScheduledProductionDate on your form to
automatically assign a value to PickDate. The Event Procedure would look
like this:

Private Sub ScheduledProductionDate_AfterUpdate()
Me.PickDate = Me.ScheduledProductionDate - 1
End Sub

 

[John]

I'm sorry I should have explained myself a little better.
I agree that in my example, I would probably not need the
field, in my real life situation the default pick date
should be 1 day earlier, but there are often times when
that day changes, say to 2 or 3 days earlier.

I would have to have this information stored in the table.

 

[James Goodman]

This is fairly simple. You will need to use an unbound control & set its
control-source to:

=DateAdd("d", -1, [ProductionDate])

N.B. This is a calculated control, & is thus not stored anywhere in the DB.

 

[James Goodman]

Doh, thought I was in the forms group!

 

[Victor Delgadillo]

John:
If it is an arbitrary date, then indeed it is not a 'calculated date'...
but! If it can be calculated automatically (skipping weekends, for instance,
or other possible ways), then it becomes a calculated field and should not
be on the table. For arbitrary entries, just request the arbitrary date and
make it default to one day earlier than the delivery date: (on entry screen,
you may update the field by initially calling the default: Me.txtPickDate =
DateAdd("d",-1,Me.txtDeliveryDate) and then move the cursos:
Me.txtPickDate.SetFocus to allow the operator to modify the date.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

 

[John Vinson]

I need to add a field to my table that will default to a
value based on another field in that same table. For
clarity I'll give an example...

I have a table that has order information in it. One of
the fields is 'ScheduledProductionDate' the product that
has a scheduled production date needs to be picked from
inventory. So I have another field in my table
called 'PickDate', that value should default to one day
earlier than 'ScheduledProductionDate' after you put that
value in.

Is this possible? I can't believe it would be possible in
Excel and not in Access?

Excel is a spreadsheet; Access is a relational database. Not the same
thing!

You cannot do this in table datasheet view, but you can in a Form. Use
the AfterUpdate event of [ScheduledProductionDate] and put in code
like

Private Sub ScheduledProductionDate_AfterUpdate()
If IsNull Me![PickDate] Then
Me![PickDate] = Me!ScheduledProductionDate
End If
End Sub