Calculated Field in Db Table

[Guest]

I am using Access 2003.
Is there a way to add a calculated field to a Db Table. I know I can do
create a calculated field in a form or a report - but can this be done in a
table as one of the fields?

Aurora

 

[Douglas J. Steele]

No, it can't be done. But then it shouldn't be done either: it would violate
database normalization rules.

Create a query that includes the calculation, and use the query wherever you
would otherwise have used the table.

 

[David F Cox]

It is probably something that you should not be doing, but, just in case,
what calculation do you want to do?

 

[Guest]

I want a date field to be added to a frequency field (=# of days) to come up
with a new date. Ex: [NextPMDate] = [pmdate] + [freq]
#3/25/06# = #1/25/06# + 60 days

Can this be done?
Aurora

 

[Rick Brandt]

I want a date field to be added to a frequency field (=# of days) to
come up with a new date. Ex: [NextPMDate] = [pmdate] + [freq]
#3/25/06# = #1/25/06# + 60 days

Just do the calculation in a SELECT query and then use the query instead of your
table.

 

[David F Cox]

If you are creating a new record and you could set a default :

Dateadd("d",Now(),60)

It may be possible to write a function that will run a query to set a
default value in a new record from other tables.
Just because something is possible does not mean it is a good idea, and I am
very dubious that this is the best solution to your problem. Calculated
fields are something to be avoided unless the application really needs one.

I want a date field to be added to a frequency field (=# of days) to come
up
with a new date. Ex: [NextPMDate] = [pmdate] + [freq]
#3/25/06# = #1/25/06# + 60 days

Can this be done?
Aurora

It is probably something that you should not be doing, but, just in case,
what calculation do you want to do?

 

[David W. Fenton]

If you are creating a new record and you could set a default :

Dateadd("d",Now(),60)

Why use Now() instead of Date()?

 

[David F Cox]

To save me some work. The existing default in my test db was Now() and I
just added the dateadd to make sure I, and Access 2007, were correct.

That plan worked well, didn't it? :-<

 

[Armen Stein]

To save me some work. The existing default in my test db was Now() and I
just added the dateadd to make sure I, and Access 2007, were correct.

That plan worked well, didn't it? :-<

Yes, but for anyone else following along, Now() returns the current date
and time. Date() returns only the date component. If you don't want
time included in your value, it's best to use Date().

In most cases it won't matter, but when using criteria to specify a date
range, having the time in there too can product unexpected results if
you don't form the Where clause correctly.

 

[David W. Fenton]

To save me some work. The existing default in my test db was Now()
and I just added the dateadd to make sure I, and Access 2007, were
correct.

That plan worked well, didn't it? :-<

Well, based on Aurora's example:

with a new date. Ex: [NextPMDate] = [pmdate] + [freq]
#3/25/06# = #1/25/06# + 60 days

I assumed tha tthe fields did *not* include time values. Fields that
are called "date" fields seem to me to be dates, and not date/times.

But I could be overinterpreting that.

My point in asking the question is simply that when you really want
dates, don't use Now(), but use Date() -- use Now() only when you
specifically want to have both date and time (and I usually split
them into two separate fields if I need both date and time -- this
makes querying for dates much easier).

 

[Jamie Collins]

If you are creating a new record and you could set a default :

Dateadd("d",Now(),60)

It may be possible to write a function that will run a query to set a
default value in a new record from other tables.

Not possible in Jet SQL. Only niladic system functions such as DATE()
and NOW() are permissable.

Jamie.

--

 

[Jamie Collins]

My point in asking the question is simply that when you really want
dates, don't use Now(), but use Date() -- use Now() only when you
specifically want to have both date and time (and I usually split
them into two separate fields if I need both date and time -- this
makes querying for dates much easier).

I would strongly recommend against splitting a single atomic
fact in this way. Jet has only one temporal data type, being DATETIME.
Splitting a DATETIME into two columns is an obvious flaw because you
would be forever having to put them back together (think scalar data
types).

What data type is your 'time' column? INTEGER plus another column to
model the granularity of the interval? Using DATETIME to model a time
element (no date) is an almost universally-acknowledged design flaw.

Jamie.

--

 

[David F Cox]

permissable. ? Don't bother me with permissable ... (;->)

I did wonder ... so I did try it before I first posted it. Apparently Access
2007 Beta is not bothered about "permissable" either. Todays added record
has [mydate] defaulted to 31/12/2006.

and the next record added came up with the same date, but by now I had
wrapped Cdate() around Format() around the dateadd function in the default.

Go figure ...

 

[Jamie Collins]

permissable. ? Don't bother me with permissable ... (;->)

CREATE TABLE Test (
key_col INTEGER NOT NULL UNIQUE,
data_col DATETIME DEFAULT DATEADD('d', NOW(), 60) NOT NULL
)
;

fails with, 'Syntax error in field definition.' Nothing to do with
permissions ;-)

Jamie.

--

 

[David F Cox]

Apologies, I was posting in a hurry again I did not mean to take issue with
the "possible in JET SQL line", but with the permissable one. For whatever
reason Access table design window in 2007 Beta will accept and act upon some
at least of the built in functions. I would suggest people avoid going
there. Because it is possible does not mean it is a good idea. It would be
nice if we could have a reliable custom default autoincrement function
though, wouldn't it?