Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: I'm Getting Compile Error on Simple String Function, Help

    Hey guys,

    Really frustrated this morning as Access just won't run a simple query for me. I have a text field called BEGIN-SVC-YY which as a leading zero that I want to remove.

    I created a UPDATE query, and tried to use the RIGHT function. I get compile errors every time. I can't figure out what I'm doing wrong. I've tried running this SQL:

    UPDATE [APERS 2011 Statement Data]
    SET [APERS 2011 Statement Data].BEGIN-SVC-YY = Right([BEGIN-SVC-YY],1);


    I thought maybe Access doesn't like that my field name has dashes in it, but I tried the same thing on a field named SSN and I got a compile error as well.


    Can anyone tell me what I might be doing wrong here?


    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unsurprisingly the - is a reserved symbol, and therefore it may well be causing your problems
    encapsualting the BEGIN-SVC-YY with square brackets may help
    Code:
    UPDATE [APERS 2011 Statement Data] 
    SET [APERS 2011 Statement Data].[BEGIN-SVC-YY] = Right([BEGIN-SVC-YY],1);
    but then agin I'd expect a text / string / character calue to be encapsualted a s ingle or double quote

    Code:
    UPDATE [APERS 2011 Statement Data] 
    SET [BEGIN-SVC-YY] = "'" & Right([BEGIN-SVC-YY],1) & "'";
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    32
    It appears that I must have some corruption or there is some VBA reference that it is not liking. I tried exporting the table to a new access database and it ran the update query just fine. I sure don't wanna have to recreate everything though.

Posting Permissions

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