Results 1 to 5 of 5
  1. #1
    Siiig is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2013
    Posts
    30

    "Simple" Expression

    This hardly could be defined as programming, but I'm not sure it fits into the other threads particularly well either.



    I have a query, and I'd like to modify one of the fields to remove the first to characters. Data looks like this:

    "D-asdf"
    "I-asdf"
    "D-poiu"
    "I-poiu"

    Obviously, I would like to remove the "I-" and "D-" from the fields. The "Replace" function seemed to be the most applicable, so I've come to this formula:

    Replace([Desc],"D-","")

    But the above returns a "Data Type Mismatch" error. The tables "Data Type" is set to "Text", and the data in it reflects that.

    Does anyone have any advice on how to get the results I'm looking for? Obviously I'm only anticipating the above expression to remove the "D-" markers. I could manage this in Excel with a Left() plus a Substitute()--and I could probably do the same with the Replace(), if I could get it working!

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you want to remove the first two characters, why not just use:
    Mid([Desc],3,Len([Desc])-2)

    Note, if you have blank or Null characters in this fields, it could cause problems with a lot of calculations if you don't check for that first.

  3. #3
    Siiig is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2013
    Posts
    30
    I'm still new to compounding expressions in Access. I never even thought of doing that! My mind went straight to replacing based on the "-".

    Do you know why Replace() would be giving me grief, but Mid/Len seem to work out fine?

  4. #4
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    That's a little strange. You could always try explicitly converting to string using cstr(), although I'm not sure it should be necessary in this case. Worth trying. You could also try referring to the field more explicitly ([TableName].[Field]), just to make sure the query isn't actually doing something different than you were expecting.

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, Replace() should work too, but with that you need to explicitly tell it exactly what you want replaced. So if there is more than one "string" you need to replace, you need to do it multiple times. As opposed to just dropping the first two characters using MID.

    Does Replace() return errors with all your records, or just certain ones?
    Can you provide an example of one that is returning an error?
    And note the tips that drexasaurus provided.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2013, 12:59 PM
  2. Replies: 8
    Last Post: 03-05-2013, 01:20 PM
  3. "IIf" "AND/OR" Expression Problem
    By SpdRacerX in forum Forms
    Replies: 1
    Last Post: 02-08-2012, 08:52 AM
  4. Replies: 13
    Last Post: 12-05-2011, 05:10 AM
  5. Replies: 8
    Last Post: 08-05-2011, 02:55 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