Results 1 to 12 of 12
  1. #1
    DecaturLady is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26

    Question Percent in a table; entering it via a form

    The Situation--


    Previously executed retail installment sales agreements (contract) are modified, extending the term & reducing the payment amount. Previously, this data was maintained in an Excel .csv spreadsheet, but 3500 lines later, it is absurdly unwieldy when creating the modified contract via Word merge.

    In Access 2003, I'm trying to building a flat file db to enter modified sales agreement contract information, prepare contracts for signature in Word (using an Access Query for merge data), and maintain a record of the customer's modfied contract parameters.

    Table elements include payment amount (currency) and loan interest (percent), in addition to things like name, loan term, payment dates, etc. [Loan parameters are not part of this db function as they are calculated elsewhere in financial systems software.]


    My Problem:
    I need users to enter the interest amount on the form as it is conventionaly viewed: 4.78% -- 10.39% -- 13.83%.

    The only way I've been able to capture percent to the table is to enter: .0478 -- .1039 -- 0.1383.


    I've struggled with this, experimenting with the Table field properties:
    Data Type: Number
    Field Size: Single
    Format: Percent
    Decimal Places: 2


    Do I need to adjust something in the Percent control on the form? Or do I need to fix something in the table?


    help appreciated

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    do you want your data entry field to record
    5.45% or 5.45

    I'm assuming you want to store it as 5.45

    if that's the case just make your field number/double and just enter it as a regular decimal.

  3. #3
    DecaturLady is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    I'd prefer 5.45% as that provides better options when using merge switches in Word.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you don't want to store it as a decimal just make the field a text field then you can type in whatever you want, particularly if you want to keep the % mark.

  5. #5
    DecaturLady is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    What should the input mask be? I've been trying something like "90!.00\%;0;_".

    Percent will be <20.00% (or <0.0200). I must have the "%" write to the field for merge purposes.

    And, input needs to be elegantly simple. [Can't be complex. just point & shoot!]

    Sorry for such elementary questions.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Try this

    ##.##!\%

  7. #7
    DecaturLady is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    We're halfway there -- percent stores correctly and optimal merge data is achieved!

    An "elegantly simple" Form Control is still missing; the place holders in form control can be confusing to endusers.

    The control is blank prior to and upon entry. When entering 4.89%, the "4" causes the mask to appear [4_.__%] with a black blinking in position 2 (positions #'s =12.34%). When entering the decimal, the cursor blinking box moves to position 3. The enduser enters 8 & 9. The control now reads: [4_.89%]. This is not elegent to me and I fear some users will stop dead in the water with thoughts of, "This isn't right." "I don't want to make a mistake." "How can I close [or remove] that space?"

    Why is the "!" not allowing right to left entry? I'd rather have the numbers march to the left than have users than an empty space in the middle of the entry.

    Can the "right to left" problem be resolved? I remove the place holders?

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if they enter 489 you'll get 48.9 I think, but if they type in 4.89 they will get 4.89. What you want is entry from the right not from the left, so that the four will appear in the far right hand column, then when you enter 8 it pushes the four to the left and puts the 8 in the new slot. and so on so that after entering three digits it appears as 4.89.

    If you use this

    ##.##!\%

    if you enter 489 (no decimal) it will appear in your field as 4.89 but before you navigate away from the field it will look like 48.9_%

  9. #9
    DecaturLady is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    If this is the best we can do, it will have to do.

    My users will see [4_.89%] while in the control. Upon exit, the control will adjust to [4.89%] which is also the way it will write to the table.

    Thanks for your assistance.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There are a few ways to take care of this, the easiest is that you store your field as a number (double) and instead of doing your merge on the table, do it on a query where the number field is converted to a text string. I don't know how important it is to you that this be exactly right on your data entry though.

    You could also have some code on your form in the ON EXIT property of the field that would make some assumptions for instance if someone enters 489 it would always assume the decimal is two places from the right. but you would really have to enforce certain rules during data entry for instance what happens if someone enters 5 (for 5%) etc.

  11. #11
    DecaturLady is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26
    Thanks, rpeare, for your comments. Actually, I am planning to use a query for the Word merge data (see my original question).

    I'm not a programmer and am dependent upon the end-user options Access offers. Do numbers "automatically" become text strings in a query? Or, does it require coding in the query?

    ON EXIT is where it corrects now. The company uses two decimal places for percent; the largest field would be xx.xx%.

    I tried all sorts of right to left entry as part of the mask, hoping to find a way that when the decimal (.) is entered, whether one digit or two preceded that entry, that the field display would adjust appropriately (on the fly) rather than delaying until EXIT is achieved.

    I'm just trying to make the data-entry aspect as simple and "idiot-proof" as I can. Any thoughts are always welcome…

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Could I ask one question before we revisit this question

    How many data entry formats are you going to allow or try to accomodate?

    using your example 4.89% is the desired end result. Theoretically this could be achieved by any of the following

    489
    4.89
    0489
    04.89
    489%
    4.89%
    0489
    04.89%

    So I guess my question is this. Will you have training for entering data the 'correct' way or are you trying to allow any possible format the user enters to be valid? If you are trying to accomodate any possible combination you are not going to do this with an input mask I'm afraid and you'd have to rely on some sort function to produce the results you want based on the data entry.

    I mentioned storing your percent as a text field as a possible solution but if you are going to allow multiple types of data entry then you wouldn't be saved by this either. (by the way cdbl(textfield) will change a text field in numeric format to a decimal number, there are other functions innate to access that perform these types of functions, cdate, cint, etc)

    So before I give you an answer I'd like to know what your data entry intentions are.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-17-2011, 05:19 AM
  2. Entering Text in Combo Form
    By Bobt in forum Forms
    Replies: 2
    Last Post: 10-22-2010, 03:53 AM
  3. Replies: 0
    Last Post: 03-15-2010, 02:38 AM
  4. Question about entering data through form
    By vixtran in forum Database Design
    Replies: 6
    Last Post: 06-16-2009, 07:23 AM
  5. Replies: 2
    Last Post: 03-16-2009, 12:19 PM

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
  •  
Other Forums: Microsoft Office Forums