Results 1 to 6 of 6

Thread: DateField + 7

  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: DateField + 7

    Hey guys, i am using access database 2007, and i am developing a dvd loan database. I am struggling with the the return date. In the LoanDate the default value is Date(), and what i am trying to do is have the return date default value the LoanDate + Customer RentLength field. I thought the expression i would put in the default value would be
    Code:
    =[LoanDate] + [Customer]![RentLength]
    But every time i do this it says "Database engine does not recognise either the field 'LoanDate' in a validated expression or the default value in the table 'Loan'

    And even if do it the other way
    Code:
    =Date + [Customer]![RentLength]
    It says "Could not find field 'Customer]![RentLength'"

    Any help would be appreciated thanks

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Are you trying to do this in a Form or directly in the Table?

    A Default Value infers that you are storing the Return Date field, which you shouldn't be doing, since it is a calculated field.

    This really should be done in the Control Source for the Return Date control, on the Form, and would simply be

    =[LoanDate] + [RentLength]

    Welcome to the forum!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    Quote Originally Posted by Missinglinq View Post
    Are you trying to do this in a Form or directly in the Table?

    A Default Value infers that you are storing the Return Date field, which you shouldn't be doing, since it is a calculated field.

    This really should be done in the Control Source for the Return Date control, on the Form, and would simply be

    =[LoanDate] + [RentLength]

    Welcome to the forum!

    Linq ;0)>
    I am ideally trying to impliment it into the table, am I barking up the wrong tree with that? And should maybe let the form handle it with the way you suggested? And what I meant by ReturnDate is the expected return date sorry for the confusion because I have another field called ReturnedDate which is when the customer actually returned it.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You cannot set a Default Value in a Table that is dependent on another field in the Table, which is what your error message is telling you.

    The Default Value for field is assigned when a New Record is first created, so if you stop and think about it, this really makes sense. How can you assign a value to a field, based on other fields, when the record is first created? The other fields haven't been populated yet!

    This had to be done in the Form using the method I gave you, with the Textbox for the expected return date not being bound to a field in the underlying table. If you need the field calculated again, such as in a Report, you simply use the same expression as the Control Source for another unbound Textbox.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Dec 2010
    Posts
    3
    Quote Originally Posted by Missinglinq View Post
    You cannot set a Default Value in a Table that is dependent on another field in the Table, which is what your error message is telling you.

    The Default Value for field is assigned when a New Record is first created, so if you stop and think about it, this really makes sense. How can you assign a value to a field, based on other fields, when the record is first created? The other fields haven't been populated yet!

    This had to be done in the Form using the method I gave you, with the Textbox for the expected return date not being bound to a field in the underlying table. If you need the field calculated again, such as in a Report, you simply use the same expression as the Control Source for another unbound Textbox.

    Linq ;0)>
    When you put it like that it makes total sense, I didn't think that the default value is when a new record is created, I just thought it would somehow put a value there so and when the record is created it is just copied but you've showed me that this isn't the case! Thanks for clearing this up!

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That's why we're here!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •