Results 1 to 8 of 8
  1. #1
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153

    Question How to enter a value of "1" but make it appear as "10001"

    I use values that begin with 10,000. However when doing data entry its very easy to mistype and enter such errors as 1001 or 100001. How can I make it s that if a user enters a value of "1" it automatiaclly generates as a value of "10001" and if the value is "500" generate as a value of "10500", etc.

    I hope this makes sense. I feel like this is easy, but its something I cannot do right now. Any help would be greatly appreciated.



    Thanks everyone!!

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You could do this if you control all the data entry through Forms. Assuming the field is of numeric datatype, you could use the "AfterUpdate" event on that field to add 10000 to that field upon entry.
    So if the field was named something like "MyValueField", that VBA code would look something like:
    Code:
    Private Sub MyValueField_AfterUpdate()
            If Me.MyValueField < 10000 Then Me.MyValueField = Me.MyValueField + 10000
    End Sub
    To see where to put this VBA code, if you open your Form in Design View, right click on the field and select "Proprties", go to the Event tab and on the "After Update" line, select the "Code Builder" option and enter your code there.

  3. #3
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    In format on the table type 10,000.
    However IF the user inputs 10,500 it will show 110,500.
    Dale

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    In format on the table type 10,000.
    Just bear in mind that if you use that method, while if you type in 1 it will display as 10001 or if you type in 500, it will display 10500, the actual values stored will be 1 and 500 (not 10001 or 10500).
    Just something important to remember if you have to use it in any matching, relationships, or mathematical computations...

  5. #5
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153
    I used the format suggestion and made it 10000. I am not sure which method would be more proficent. The method by user "JoeM" or by the user "rzw0wr". Right now I just used the format of 10000 becasue that was an easy fix without having to do any coding. Any other ideas on which is better long term?

    Thanks for your help!! Both ways work perfectly!

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It really depends on what you need to use it for. If your display purposes only, then the Format method will work fine.
    However, if you have do any mathematical calculations or matching on that field, you may have issues, as I outlined in my previous post.

  7. #7
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153
    OK that makes sense. I wont be doing any math, except for maybe counting values but no summing or subtraction or anything of the sort. Mainly display and count values. Thank you so much for your help! This is solved!!

  8. #8
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    In that case either way will work. It should noted that this is not a recommended practice.

    Dale

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

Similar Threads

  1. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  2. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  3. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  4. Replies: 16
    Last Post: 07-22-2011, 09:23 AM

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