Results 1 to 9 of 9
  1. #1
    data808 is offline Noob
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    727

    Expression Help

    Right now I have this in my Default Value property for a text field:

    =[Forms]![Login]![txtUsername]



    My question is, for the username that the user will enter will always have some letters and numbers followed by an underscore "_" then their initials. How do I change this expression so that whatever characters follow the underscore are taken and not the whole username? For example:

    Username: D1UXM_JDO

    I would like the field to only take "JDO".

    Thanks for the help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    String manipulation functions.

    Is this textbox on the Login form?

    Let x represent txtUsername.

    Mid(x, InStrRev(x, "_") + 1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Take a look at Instr and InstrRev function to retrieve the position of underscore in the string and the use the Right function to get the desired number of characters from right.
    See string functions here http://www.techonthenet.com/access/functions/

  4. #4
    data808 is offline Noob
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    727
    The textbox that I want to auto fill is on another form. I have the login form hidden after log in so that the other form can pull the username into this field. The thing is I don't want this to be an entry but rather a default value because I don't want the primary key field to be triggered when this thing auto fills. Is that what this is going to do? I don't really know how to write strings. I would probably write it something like this:

    Dim txtUsername As x

    Mid(x, InStrRev(x,"_") + 1)

    Would that be right?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That's not how Dim statements are used.

    Dim strIni As String

    strIni = Mid(Me.txtUsername, InStrRev(Me.txtUsername, "_") + 1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    data808 is offline Noob
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    727
    Sounds like this is VBA and would auto fill the text box as an entry. I need it to be an expression that is in the default value of the text box so when it auto fills the text box it won't trigger the primary key.

    Right now this expression "=[Forms]![Login]![txtUsername]" is in the default value for the text box "Clerk Initial" that is on the form called "DL Clearance". I just would like to alter this to pull the letters that follow the underscore in the username field on the login form. Is it even possible?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then why did you show that VBA code?

    Instead of x in my suggested expression, reference [Forms]![Login]![txtUsername]

    In the DefaultValue property:

    Mid([Forms]![Login]![txtUsername], InStrRev([Forms]![Login]![txtUsername], "_") + 1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    data808 is offline Noob
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    727
    I wasn't showing the VBA. I was showing what I had in my default value. I thought this was considered an expression:


    =[Forms]![Login]![txtUsername]

    Anyway, the new expression you provided above did work great so thank you for that. You have been most helpful.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A Dim statement is VBA.

    Oh well, glad you have a solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 09-25-2013, 09:35 AM
  2. Help with IIF expression
    By mrwhitehat in forum Reports
    Replies: 9
    Last Post: 08-27-2013, 01:28 AM
  3. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  4. Replies: 4
    Last Post: 10-26-2012, 12:49 AM
  5. Expression Help
    By Hammer in forum Queries
    Replies: 2
    Last Post: 01-14-2011, 10:30 AM

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