Default Value in Table

  • Thread starter Thread starter Jonathan Smith
  • Start date Start date
J

Jonathan Smith

I am using Access 2002, with Access 2000 File Format.
I am having difficulty setting a Default Value in a particular field.
Following is the Database Schema, as it relates to this problem:

tbl2001
SvcStartDate Date/Time Date Service Started
SvcEndDate Date/Time Date Service Ended

I need to set the Default Value of SvcEndDate to equal SvcStartDate.

Aside from the lectures on Data Normalization, the doctrines of which I
am fully aware -- and the ramifications of Non-Normalized Data, of which
I am also fully aware -- I need help here.

I appreciate any/all input in this endeavor.

Jonathan Smith
 
You can't set the default value like this in a table. You can provide
something workable in a form. Add code to the after update event of the
SvcStartDate text box to set the value of the SvcEndDate text box.
 
As Duane points out, forms give you the control to do this. Access has no
"triggers" (unlike SQL-Server), so this isn't doable working only in
(Access) tables.

So, I promise not to lecture on normalization ... but why do you need ANY
date pre-loaded in SvcEndDate? I can imagine a "service" that is typically
completed within one day, but then what useful information would #1/1/2003#
and #1/1/2003# provide? Any chance you are recording date & time?

Good luck

Jeff Boyce
<Access MVP>
 
I need to set the Default Value of SvcEndDate to equal SvcStartDate.
Click to expand...

At the time the DefaultValues get filled in, it's a new empty record so
there is no ScvStartDate value for it to be equal to.

It sounds like you need to catch the Event for SvcStartDate being updated
(i.e. filled in), which is a job for a form.

B Wishes


Tim F
 
As Duane points out, forms give you the control to do this. Access
has no "triggers" (unlike SQL-Server), so this isn't doable working
only in (Access) tables.

So, I promise not to lecture on normalization ... but why do you need
ANY date pre-loaded in SvcEndDate? I can imagine a "service" that is
typically completed within one day, but then what useful information
would #1/1/2003# and #1/1/2003# provide? Any chance you are recording
date & time?

Good luck

Jeff Boyce
<Access MVP>
Click to expand...

The necessity for the Pre-Loaded Date is to simplify Data Input, at the
Form Level. That Table Structure must meet Government Requirements for
Electronic Filing of Medical Claims.

Thanks for skipping the lecture.

Jon
 
You can't set the default value like this in a table. You can provide
something workable in a form. Add code to the after update event of the
SvcStartDate text box to set the value of the SvcEndDate text box.
Click to expand...

Duane,

I tried that approach, but apparently I am not as well-versed in Coding
as I has thought. Any suggestions on the code?

I have tried

txtSVCSTARTDATE_AfterUpdate
Me("txtSVCENDDATE") = "txtSVCSTARTDATE"

But it is not working.

Thanks for your help.

Jon
 
Jonathan Smith said:
Duane,

I tried that approach, but apparently I am not as well-versed in Coding
as I has thought. Any suggestions on the code?

I have tried

txtSVCSTARTDATE_AfterUpdate
Me("txtSVCENDDATE") = "txtSVCSTARTDATE"

But it is not working.
Click to expand...

Me!txtSVCENDDATE = Me!txtSVCSTARTDATE
 
Jon

Oh now you have me worried... are you saying that Government Healthcare
regulations prohibit good normalization?! <g>

Jeff Boyce
<Access MVP>
 
Jon

Oh now you have me worried... are you saying that Government Healthcare
regulations prohibit good normalization?! <g>

Jeff Boyce
<Access MVP>
Click to expand...

Only in Arizona.
 
That Table Structure must meet Government Requirements for
Electronic Filing of Medical Claims.
Click to expand...

This does not make sense, I'm afraid. It is like saying that the metal used
for the engine mountings is dictated by what colour the outside is going to
be.

The business needs of the system may well include some form of export or
data transmission and specific presentation of same, but that is an ocean
away from the nuts and bolts of logical table design. There is no excuse
(oh, alright, very rarely then) to scupper a good logical design to satisfy
an external body -- it's the job of the query grid to bridge the gap.

Just my tuppence worth... :-)


Tim F
 
Back
Top