Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Problem with RTrim

    I am using RTrim in a query to remove trailing spaces. However, when it gets to a field where there are only space characters, it brings up an error. Does anyone know of a way round this, perhaps a custom VBA function that accounts for this eventuality?

  2. #2
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by bcass
    I am using RTrim in a query to remove trailing spaces. However, when it gets to a field where there are only space characters, it brings up an error. Does anyone know of a way round this, perhaps a custom VBA function that accounts for this eventuality?
    errr.. Very obvious: An If-then statement before you rtrim?

    If field = ""(or o or whatever) then end sub

    Or ifnull is a statement that could help
    etc.
    etc.

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    But I want to make the field Null if it only contains spaces (some fields may contains a series of spaces). How can I account for that?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What kind of error do you get?
    If you had a string that contained 5 spaces, then
    RTRIM(strSpace) would return "" (zero length string), but not an error.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Oct 2003
    Posts
    357
    Code:
    if trim(field)="" then
    'Make the field null
    end if
    Madhivanan

    Failing to plan is Planning to fail

  6. #6
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Still no joy. The error I get from Access is:

    Access didn't update 5689 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

    I used this in my update query: IIf([FieldName]="",Null,RTrim([FieldName]))

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What is the data type of the field you are updating?
    Does it allow null values and/or zero length strings?
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I have 24 fields in the table. Data Types are Text; Number (Integer and Double); and Date/Time. Non of the fields have any Validation Rules, and Required in not set to Yes for any. A few of the fields have Unicode Compression set to yes.

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I suspect the function is working ok on the Text fields, but not the others.
    You may also need to use the Cint, CDate functions also.

    If you are trying to update all 24 fields at once, it's going to be difficult to tell which one is giving you a problem.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    do you have null values in your field?

    edit: please ignore this post, I didn't see the follow up replies.
    ghozy.

  11. #11
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    there cannot be trailing spaces in number and date fields. it is only possible with text type fields. therefore i will ask again, do you have any null values in that certain field? you can't rtrim null values and null is not equal to an empty string.
    ghozy.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    too true ghozy, maybe this fixes it:
    IIf(nz([FieldName],"") = "", Null, RTrim([FieldName]))

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks a lot izyrider, your suggestion worked. There were fields with both Null and empty strings present, which I hadn't accounted for.

  14. #14
    Join Date
    Oct 2003
    Posts
    357
    izyrider,
    what does nz function do?
    Madhivanan

    Failing to plan is Planning to fail

  15. #15
    Join Date
    Dec 2004
    Posts
    13
    Quote Originally Posted by Madhivanan
    izyrider,
    what does nz function do?
    am abit of a novice really with Access, but often use the nz function

    the description from Access Help is:
    You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Posting Permissions

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