Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I have imported some data externally for a firned to help them with their invoicing needs.

    I have a field called 'Invoice'

    The values for this field at the moment have a '/' before the invoice number and then another '/' at the end of the number. Some invoice numbers are 4 numbers long, some are 5 long.

    I need to be able to remove the '/' from the field and then sort the row into invoice number order.

    Please help....
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Can there be a slash at another position than beginning and end? For example an invoice number "/12/345/"?

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786200' date='25-Jul-2009 16:28']Can there be a slash at another position than beginning and end? For example an invoice number "/12/345/"?[/quote]


    No all the field have the format of '/12345/'
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create a query based on the table.
    Select Query | Update Query to change the query to an update query.
    Add the invoice number field. Let's say it is named InvoiceNo.
    In the Update to line, enter

    Replace([InvoiceNo],"/","")

    Select Query | Run or click the run button on the toolbar (the one with the exclamation mark).
    This will remove all slashes from the invoice number.
    Open the table and inspect it visually. If everything is OK, you can go into design view and change the invoice number field from text to number.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •