Results 1 to 6 of 6
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need query expression which will compare 1st letters (2 fields) and update 1 field where appropriate

    Hello:

    I need some assistance with updating the *case* (lower/upper) of a word's **first** letter.

    Background:


    - Attached database contains a few sample words in [tbl_Words].[NotSimpleTense] AND [tbl_Words].[SimpleTense].
    - These words may be written in past tense, simple present tense, or simple present tense continous. All of these are fine.
    - The issues lies in field [SimpleTense]. Although the tenses are correct, the 1st letter of the words in [SimpleTense] may be different than the 1st letter of [NotSimpleTense].

    My goal:
    - I need to come up with a query expression with will "look" at the case of the 1st letter in field [SimpleTense] and then compare it to the 1st letter of the word in field [NotSimpleTense].
    - If there is a difference -- whether it's a) upper vs. lower OR b) lower vs. upper -- then the 1st letter of the word in [SimpleTense] must be updated to the 1st letter of [NotSimpleTense]'s word.

    I included additional comments in the table. I hope they'll further illustrate the error and what the corrected word should be.

    Does anyone know which expression (e.g., in qry_Correction) would update [SimpleTense]'s words correctly?
    The new expression should exactly match the outcome of manually typed words shown in [CorrectWord].

    Thanks,
    EEH
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    This query may be a start.
    Query: Copy Of qry_Correction
    Code:
    Select id,
    left(NotSimpleTense,1) & Mid(simpleTense,2) as REQd,notsimpletense
     
    from
    (
    SELECT tbl_Words.ID,simpletense,notsimpletense
    ,iif(strcomp(left(NotSimpleTense,1),left(SimpleTense,1),0) <>0, "ChangeReqd","NochangeReqd") as WhatToDO
    FROM tbl_Words
    ORDER BY tbl_Words.ID
    ) where whatToDo ="changeReqd"
    Produces:

    id REQd notsimpletense
    8 Address Addressed
    10 advance advanced
    11 advise advise
    14 build build

    OR

    Query: QRyResulttAllWords

    Code:
    SELECT tbl_Words.ID as X
    , tbl_Words.SimpleTense
    ,Tbl_words.notsimpletense
    FROM [Copy Of qry_Correction] RIGHT JOIN tbl_Words ON [Copy Of qry_Correction].id = tbl_Words.ID
    WHERE ((([Copy Of qry_Correction].id) Is Null))
    union select 
    id as x
    ,reqd
    ,notsimpletense 
    from  [Copy Of qry_Correction]
    order by X

    Output:
    X SimpleTense notsimpletense
    1 achieve achieve
    2 Acquire Acquire
    3 Acquire Acquired
    4 act act
    5 Act Acted
    6 Act Acts
    7 address address
    8 Address Addressed
    9 Adept Adept
    10 advance advanced
    11 advise advise
    12 Bring Bring
    13 Break Broke
    14 build build
    15 Build Built

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Jack -- wow, your query approach is PERFECT!!!

    Thousand this... this will help tremendously w/ the data cleanup. I appreciate your input a great deal.

    Cheers,
    Tom

    P.S. I now have to shuffle some snow too (SE Virginia). It was your "turn" last weekend... it's mine now.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good luck with the snow.
    It's -33C here and just starting a light snow. We had about 18 " of snow earlier in the week.
    Continuing about -15 to -25 for the next few days.

    update:
    Had to warm up that -255 (my fingers must be cold)

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by orange View Post
    Good luck with the snow.
    It's -33C here and just starting a light snow. We had about 18 " of snow earlier in the week.
    Continuing about -15 to -255 for the next few days.
    And we think we are going to have it bad when news like this pops up.

    https://www.dailymail.co.uk/news/art...howers-UK.html
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by orange View Post
    Good luck with the snow.
    It's -33C here and just starting a light snow. We had about 18 " of snow earlier in the week.
    Continuing about -15 to -255 for the next few days.
    I bought an Outback August 2020 and never really had a chance to challenge it until that happened. So much fun to plow effortlessly through so much snow that it washes up over the windshield like waves!
    BTW, it's cold here now as you say, but I don't think we're going to see -255C!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-28-2018, 06:17 PM
  2. Replies: 3
    Last Post: 04-27-2016, 01:47 PM
  3. update query that removes extra letters
    By alinapotter in forum Queries
    Replies: 4
    Last Post: 04-06-2013, 01:15 PM
  4. Replies: 4
    Last Post: 01-30-2012, 08:32 AM
  5. Replies: 1
    Last Post: 02-03-2010, 09:17 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