Results 1 to 3 of 3
  1. #1
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Justify numeric text field


    I imported an Excel file into a table where the first field is a Text field (JobNo) that is left justified and can be either numeric or alphanumeric. In order to query against a legacy database I need to make sure the field is properly justified. If numeric then it needs to be 9 position space left filled such as " 12345". If alphanumeric then it can remain left justified such as "12345W". I know I can use Right(Space(9) & [JobNo],9) for the numeric field. I tried writing an Update Query using If IsNumeric but can't figure out the syntax...any combination I try returns a syntax error.
    thanks for any help.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's a function you can use:
    Code:
    Public Function MyNum(InNum As String) As String
       Dim LeadingSpaces As Integer
       If IsNumeric(Trim(InNum)) Then
          LeadingSpaces = 9 - Len(Trim(InNum))
          MyNum = Space(LeadingSpaces) & Trim(InNum)
       Else
          MyNum = Trim(InNum)
       End If
    End Function

  3. #3
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Justify numeric text field

    ok...thanks...I will try that.

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

Similar Threads

  1. Split Numeric Data out of Irregular Text Field
    By nathanielban in forum Queries
    Replies: 6
    Last Post: 12-21-2010, 10:30 AM
  2. Bring in numeric text field to access from excel
    By Sck in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2010, 04:07 PM
  3. Sephen Leban's Justify memo text
    By azal in forum Reports
    Replies: 3
    Last Post: 08-09-2010, 01:17 AM
  4. Replies: 2
    Last Post: 05-05-2010, 02:52 PM
  5. centre justify column text
    By marky in forum Access
    Replies: 0
    Last Post: 09-01-2008, 12:02 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
  •  
Other Forums: Microsoft Office Forums