Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: split value in field (no spaces)

    I have a field that contains values along the lines of:
    Q123
    Q456
    Q34
    Q987

    These are entered via a form and barcode reader
    What I am attempting to do is ger rid of the leading Q. Either at the time of data entry in the form or at a later stage via a query.

    Cheers and many thanks!!!

  2. #2
    Join Date
    Dec 2002
    Posts
    60

    Re: split value in field (no spaces)

    How about an update query using Right function:

    UPDATE tblName SET tblName.FieldName = Right([FieldName],Len([FieldName])-1);


    Originally posted by Glimmer
    I have a field that contains values along the lines of:
    Q123
    Q456
    Q34
    Q987

    These are entered via a form and barcode reader
    What I am attempting to do is ger rid of the leading Q. Either at the time of data entry in the form or at a later stage via a query.

    Cheers and many thanks!!!

  3. #3
    Join Date
    Aug 2003
    Posts
    6

    Re: split value in field (no spaces)

    This doesn't work. All it does is return the same value that already existed. In other words, if I 'view' the query all I see is what was alreday there. If I run the query it does not alter the data contained in the field

  4. #4
    Join Date
    Dec 2002
    Posts
    60
    I don't know what to say-it worked for me. What is the SQL of your query?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps your data has spaces before (and possibly after) the characters you want. Try this:

    Right(trim([FieldName]),Len(trim([FieldName]))-1);

    blindman

  6. #6
    Join Date
    Aug 2003
    Posts
    6
    Sorry folks, not working.
    Here's the background stuff:
    The Table is called 'Main'
    The Field within that table is called 'Quantity' - dataype = Text

    The SQL statement I'm using based on suggestions above is:

    UPDATE Main SET Main.Quantity = Right([Quantity],Len([Quantity])-1);

    How does this seem?


    As an aside, I've just run a Select Query that includes the 'Main' table and pasted the following into the 'field' section:
    Exp: Right([Quantity],Len([Quantity])-1)
    and it seems to return a correct result. Maybe this in itself resolves the issue in that I can now do something with the result like maybe use a make table query??

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is no reason the syntax provided should not work, so something else screwy is going on that I can only guess at. One possibility is that the query is failing for some other reason, but you have alerts disabled. Like I said, that's just a guess.

    You say that when you view the query you don't see any change. Do you mean when you "view" the update query? Viewing an update query doesn't show the new values, it shows the values that will be changed.

    Are you sure your update query is correct? Remember that the formula needs to go in the update row, and not the field row.

    Unfortunately, this is the sort of problem that could be easily solved if I was looking over your shoulder, but may prove difficult to solve through this forum. See if there is somebody else in your organization that could give you assistance.

    blindman

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is no reason the syntax provided should not work, so something else screwy is going on that I can only guess at. One possibility is that the query is failing for some other reason, but you have alerts disabled. Like I said, that's just a guess.

    You say that when you view the query you don't see any change. Do you mean when you "view" the update query? Viewing an update query doesn't show the new values, it shows the values that will be changed.

    Are you sure your update query is correct? Remember that the formula needs to go in the update row, and not the field row.

    Unfortunately, this is the sort of problem that could be easily solved if I was looking over your shoulder, but may prove difficult to solve through this forum. See if there is somebody else in your organization that could give you assistance.

    blindman

  9. #9
    Join Date
    Aug 2003
    Posts
    6
    folks, many thanks for your help in this one. I figure that you've put me on the right track either way so will work this one out from this point forward.
    Cheers and best wishes.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No fair Glimmer! Now that you've got us curious, be sure to post us when you figure out what the problem was!

    Good luck.

    blindman

Posting Permissions

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