Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem (Office 97 SR2 WIN 98)

    Hi:
    Got a little Query Problem. I can do this in SQL but it does not seem to work in ACCESS. The data for the Sample name field in this table got misentered. So, instead of "Sample January 2000 MOD XXX" , some of the entries for some months and some MODs look like "Sample: January 2000 MOD XXX". I want to do an update query that will remove the colon. So, here is what I thought would work:
    UPDATE [IQC Proficiency]
    SET [IQC Proficiency].Sample = "Sample "&mid((IQC Proficiency].Sample,8,len([IQC Proficiency].Sample)-8)
    WHERE left([IQC Proficiency].Sample,8) = "Sample: "
    I get the error message shown in the attached paintbrush file.
    Thanks for your help.
    Attached Images Attached Images

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Office 97 SR2 WIN 98)

    Are you missing a space before and after the & ????

    Try this: SET [IQC Proficiency].Sample = "Sample " & mid((IQC Proficiency].Sample,8))

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Problem (Office 97 SR2 WIN 98)

    Try this:

    UPDATE [IQC Proficiency]
    SET [IQC Proficiency].Sample = "Sample "&mid([IQC Proficiency].Sample,8)
    WHERE [IQC Proficiency].Sample Like "Sample:*"

    Or, if you copied and pasted your SQL, insert that missing left bracket in your Mid function. You've got a closing one for [IQC Proficiency].Sample, but you left off the opening bracket.
    Charlotte

Posting Permissions

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