Results 1 to 3 of 3
  1. #1
    frankivalli is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2014

    Dealing with the # character

    Thanks in advance.

    I have a database that was populated using many excel sheets and one of the fields [partDescription] uses the # character over and over to represent "lbs". I realize that this is a wild card for digits in VB. Is there a quick fix for finding and replacing this character?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    it's not a wildcard it notates the beginning/end of a date field.

    what do you want your result to be?

    Two separate fields one for the quantity, one for the measure


    ItemWeight  ItemUnit
    1           Pounds
    2           Ounces
    10          Grams
    if you don't care about the unit measure or just want to replace it with a string (which I don't recommend)

    you could run an update query on the current field that and in the UPDATE TO row of the query put something like Replace([Fieldname], "#", "lbs")

    if you want to split the units and the actual value that would also be a update query but you'd have to split the field using something like

    left([fieldname], instr([fieldname], "#") -1)
    right([fieldname], len([fieldname]) - instr([fieldname],"#"))

  3. #3
    booismycat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Once you have imported to Access, you can run an Update Query to remove the # from the field(s) in question.
    Easiest way to remove unwanted characters (in mass) that I know of.

    I would suggest importing to a temp table first until you are comfortable with your process.

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

Similar Threads

  1. Dealing with Old Balance
    By Altaful Haq in forum Forms
    Replies: 2
    Last Post: 02-16-2014, 06:29 AM
  2. Dealing With Expression Of The Form
    By azhar2006 in forum Queries
    Replies: 4
    Last Post: 02-07-2014, 02:37 PM
  3. Replies: 3
    Last Post: 06-12-2013, 04:03 AM
  4. Help - Query Dealing with Dates!!
    By bbanks2 in forum Queries
    Replies: 3
    Last Post: 12-18-2012, 11:31 AM
  5. Help dealing with duplicate records
    By jswenson3 in forum Access
    Replies: 12
    Last Post: 02-01-2012, 10:17 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