Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    10

    Unanswered: How do make a field automatically become a value based on another field?

    I have a field called DeliveryCharges, but a delivery charge only occurs when the quantity of a product is less than 100, how do I make the DeliveryCharges field do this? Thanks .

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Use a formula for it that involves a IIf function would be my first thought.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd have some logic in your form which decides if a delivery charge is payable. it may be a smart call to push that into a module so it an be called by other forms/reports if required. you could do it as SQL, but as I read the problem if it was an online or telesales or face to face (remember those days) transaction Id want to tell the customer that if they buy n items more they get free delivery.....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2008
    Posts
    10
    If I use the IIF function, I won't be able to store the value will I? Because I'd have to put it in ControlSource.

  5. #5
    Join Date
    Feb 2004
    Posts
    214

    ...

    On the "Quantity" field you can build an On_Change procedure in VB. In the On_Change call, you then write an If statement that evaluates the value of Quantity and updates Delivery Charges

    Private Sub Quantity_Change()
    If Quantity <100 Then
    Delivery_Charges = 25 (or whatever value you want)
    End If
    End Sub

    See if that works for what you are looking for.

  6. #6
    Join Date
    Oct 2008
    Posts
    10
    Cool I think that's what I'm looking for, except it doesn't go to 0 when the quantity is above 100 for some reason

  7. #7
    Join Date
    Feb 2004
    Posts
    214

    Change from an If to an If Then Else

    Private Sub Quantity_Change()

    If Quantity <100 Then
    Delivery_Charges = 25 (or whatever value you want)

    Else
    Delivery_Charges=0

    End If

    End Sub

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by iiiiiiiii
    If I use the IIF function, I won't be able to store the value will I? Because I'd have to put it in ControlSource.
    So??

    If it is a formula, then it SHOULDN'T be stored.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the delivery charges form part of an invoice, a quote or estimate, then personally I'd violate the rules and store it.

    why you have the actual data that formed the transaction, so if someone wants to query the invoice, or you need to demonstrate the accuracy of your system you have the actaul data, rather than having to design a mechanisms which calculates which delivery charge was applicable to which order(s) at such and such a time.

    It helps as the normal peopl who do these sort of checks are either HMRC/Customs & Excise or Accountants.. if you keep life simple then you can easily satisfy the simple people (like Accountants and HMRC/Customs and Excise)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Whether or not this is looked up at run time or written to the column, I would have this in a DeliveryCharges table, with an effective date and effective order value column. Delivery charges will change over time and also might apply to different total order values at a later date too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, if the delivery charges are likely to change, then they should be stored. Forgot about that for a sec.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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