Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Enter a Default Value in a Field in a Table

Discussion in 'Information Technology' started by access user, Jul 28, 2006.

  1. access user

    access user
    Expand Collapse
    Guest

    I have a table - tblProperty, with fields:

    TradeAccommodation
    PrivateAccommodation

    I want PrivateAccommodation = 100 - TradeAccommodation

    So in the default value for PrivateAccommodation I put:

    100-[TradeAccommodation]

    but I get the error:

    "The database engine does not recognise the field 'TradeAccommodation' in a
    validation expression, or the default value in the table 'tblProperty' "

    Any ideas?

    TIA
    James
     
  2. Loading...


  3. Rick B

    Rick B
    Expand Collapse
    Guest

    You don't store calculated values in a table. If PrivateAccomodation will
    ALWAYS be 100-TradeAccomodation, then you don't need to store both pieces of
    data in the table - that is redundant.

    Instead, simply store the TradeAccomodation in your table. When you need to
    print or display PrivateAccomodation, simply calculate it in your forms,
    queries, or reports.

    --
    Rick B



    "access user" <accessuser@discussions.microsoft.com> wrote in message
    news:A2AE8480-9865-451B-B428-2BC7A150971F@microsoft.com...
    >I have a table - tblProperty, with fields:
    >
    > TradeAccommodation
    > PrivateAccommodation
    >
    > I want PrivateAccommodation = 100 - TradeAccommodation
    >
    > So in the default value for PrivateAccommodation I put:
    >
    > 100-[TradeAccommodation]
    >
    > but I get the error:
    >
    > "The database engine does not recognise the field 'TradeAccommodation' in
    > a
    > validation expression, or the default value in the table 'tblProperty' "
    >
    > Any ideas?
    >
    > TIA
    > James
     
  4. access user

    access user
    Expand Collapse
    Guest

    Thanks for the reminder Rick - I had come across that before (redundant data
    etc...)

    BUT, just as a point of interest, can a field in a table be populated in the
    way I was trying to or, if not, is the reason for that because it is a forced
    control against redundancy?

    TFTH

    "access user" wrote:

    > I have a table - tblProperty, with fields:
    >
    > TradeAccommodation
    > PrivateAccommodation
    >
    > I want PrivateAccommodation = 100 - TradeAccommodation
    >
    > So in the default value for PrivateAccommodation I put:
    >
    > 100-[TradeAccommodation]
    >
    > but I get the error:
    >
    > "The database engine does not recognise the field 'TradeAccommodation' in a
    > validation expression, or the default value in the table 'tblProperty' "
    >
    > Any ideas?
    >
    > TIA
    > James
     
  5. Rick B

    Rick B
    Expand Collapse
    Guest

    No, you can't include a calculation in a table. I believe that it can be
    done in an SQL database and is called a "trigger", but not in Access.

    --
    Rick B



    "access user" <accessuser@discussions.microsoft.com> wrote in message
    news:78002876-5FFE-4238-9DAF-684EBF3326E8@microsoft.com...
    > Thanks for the reminder Rick - I had come across that before (redundant
    > data
    > etc...)
    >
    > BUT, just as a point of interest, can a field in a table be populated in
    > the
    > way I was trying to or, if not, is the reason for that because it is a
    > forced
    > control against redundancy?
    >
    > TFTH
    >
    > "access user" wrote:
    >
    >> I have a table - tblProperty, with fields:
    >>
    >> TradeAccommodation
    >> PrivateAccommodation
    >>
    >> I want PrivateAccommodation = 100 - TradeAccommodation
    >>
    >> So in the default value for PrivateAccommodation I put:
    >>
    >> 100-[TradeAccommodation]
    >>
    >> but I get the error:
    >>
    >> "The database engine does not recognise the field 'TradeAccommodation' in
    >> a
    >> validation expression, or the default value in the table 'tblProperty' "
    >>
    >> Any ideas?
    >>
    >> TIA
    >> James
     
  6. access user

    access user
    Expand Collapse
    Guest

    Thanks - I've read about triggers before - seem very useful...
    TFTH

    "Rick B" wrote:

    > No, you can't include a calculation in a table. I believe that it can be
    > done in an SQL database and is called a "trigger", but not in Access.
    >
    > --
    > Rick B
    >
    >
    >
    > "access user" <accessuser@discussions.microsoft.com> wrote in message
    > news:78002876-5FFE-4238-9DAF-684EBF3326E8@microsoft.com...
    > > Thanks for the reminder Rick - I had come across that before (redundant
    > > data
    > > etc...)
    > >
    > > BUT, just as a point of interest, can a field in a table be populated in
    > > the
    > > way I was trying to or, if not, is the reason for that because it is a
    > > forced
    > > control against redundancy?
    > >
    > > TFTH
    > >
    > > "access user" wrote:
    > >
    > >> I have a table - tblProperty, with fields:
    > >>
    > >> TradeAccommodation
    > >> PrivateAccommodation
    > >>
    > >> I want PrivateAccommodation = 100 - TradeAccommodation
    > >>
    > >> So in the default value for PrivateAccommodation I put:
    > >>
    > >> 100-[TradeAccommodation]
    > >>
    > >> but I get the error:
    > >>
    > >> "The database engine does not recognise the field 'TradeAccommodation' in
    > >> a
    > >> validation expression, or the default value in the table 'tblProperty' "
    > >>
    > >> Any ideas?
    > >>
    > >> TIA
    > >> James

    >
    >
    >
     
  7. access user

    access user
    Expand Collapse
    Guest

    See John Vinson's response as well - same question:

    http://www.microsoft.com/office/com...7617c5-382e-4b35-b5e5-c11d7651058b&sloc=en-us

    James

    "access user" wrote:

    > Thanks - I've read about triggers before - seem very useful...
    > TFTH
    >
    > "Rick B" wrote:
    >
    > > No, you can't include a calculation in a table. I believe that it can be
    > > done in an SQL database and is called a "trigger", but not in Access.
    > >
    > > --
    > > Rick B
    > >
    > >
    > >
    > > "access user" <accessuser@discussions.microsoft.com> wrote in message
    > > news:78002876-5FFE-4238-9DAF-684EBF3326E8@microsoft.com...
    > > > Thanks for the reminder Rick - I had come across that before (redundant
    > > > data
    > > > etc...)
    > > >
    > > > BUT, just as a point of interest, can a field in a table be populated in
    > > > the
    > > > way I was trying to or, if not, is the reason for that because it is a
    > > > forced
    > > > control against redundancy?
    > > >
    > > > TFTH
    > > >
    > > > "access user" wrote:
    > > >
    > > >> I have a table - tblProperty, with fields:
    > > >>
    > > >> TradeAccommodation
    > > >> PrivateAccommodation
    > > >>
    > > >> I want PrivateAccommodation = 100 - TradeAccommodation
    > > >>
    > > >> So in the default value for PrivateAccommodation I put:
    > > >>
    > > >> 100-[TradeAccommodation]
    > > >>
    > > >> but I get the error:
    > > >>
    > > >> "The database engine does not recognise the field 'TradeAccommodation' in
    > > >> a
    > > >> validation expression, or the default value in the table 'tblProperty' "
    > > >>
    > > >> Any ideas?
    > > >>
    > > >> TIA
    > > >> James

    > >
    > >
    > >
     

Share This Page