Results 1 to 6 of 6
  1. #1
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    214

    Prefix

    Hi Everyone



    I have a Control which requires the following 3 Characters everytime a new Record is added. "CO#"

    Is it possible to modify the format of the underlying field in the table so that these 3 characters are entered by Default?

    Any help appreciated

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,792
    in the table design, format property for the field put

    "CO#"@

    note the underlying value will remain as a number so user search will never find it if using say CO#123, they just need to search for 123

    Also, it is a very bad idea to have any sort of formatting in a table - it disguises the true value. Just apply it in your control

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,315
    As Ajax said I would probably do this in the control on the form. Few ways to do this, maybe use a data input format string or on the LostFocus event of that field can do
    If Not Isnull(Me.CoValue) then Me.COValue = "CO# " & Me.CoValue

    Or could do something on the Before Update on the record to add the CO#.

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,792
    better to keep the number part (I'm assuming it's a number) rather than adding a text prefix. a) because numbers are faster than text for searching/sorting etc and b) because if you do add it as a prefix and a user does not want to constantly have to type 'CO#' you will have to use like for searches which will preclude the use of indexing and require additional coding to manage

  5. #5
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    214
    Hi Ajax

    I need the prefix as the data input will always be a string ie CO#1 Added new valves then next record would be CO#2 Updated plumbing

  6. #6
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,792
    my point is 'CO#' is a constant and as such can simply be applied when required. I've explained the benefits of doing this, however it's your app so it is up to you

    Your requirement has become slightly confusing - in your original post you wanted to modify the format, now it seems you want to modify the value.

    so if you want to add this prefix when a user enters the numeric part in the control afterupdate event use the code suggested by Bulzie in post #3 - you'll need additional code to determine if this has already been added so you don't add it again

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

Similar Threads

  1. Add prefix to a formula
    By Pickboy in forum Programming
    Replies: 2
    Last Post: 08-03-2018, 07:12 AM
  2. Prefix transfer to Union Query
    By Perfac in forum Queries
    Replies: 4
    Last Post: 06-19-2018, 01:21 PM
  3. Prefix character in ID field
    By Zbeibu in forum Access
    Replies: 2
    Last Post: 05-14-2012, 07:21 AM
  4. Table naming - use prefix or not?
    By revnice in forum Access
    Replies: 4
    Last Post: 08-08-2010, 11:55 AM
  5. Add prefix to AutoNumber
    By sirmilt in forum Database Design
    Replies: 3
    Last Post: 07-09-2010, 01:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums