Results 1 to 10 of 10
  1. #1
    frederic007 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    5

    query syntex for columns

    1.how to wrote syntex in query at column 4 to show 'y' if column 3 matches column 2 or column 1 data?
    2. how to substract current date from column data to see it is over 6 months or not? tried many times but not sucess.
    thank you in advance!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please provide an example of your starting data and what you want as a result.
    As for Date related calculations, research the DateDiff function.
    Good luck.

  3. #3
    frederic007 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    5
    tks for your reply! i am thinking to add something for column 2 criteria..
    1. or [column 1]
    2. at column 4 criteria..
    match [column 3] and [column 2]
    is the syntex and logic correct?
    thank you for any advice.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Frederic,

    For clarity, please provide an example or 2 showing:
    -typical values for your column2, 3 and 4
    -what exactly your columns represent
    -a step by step breakdown of what matches what and the next step if there is/is not a match
    -the final result of each example

  5. #5
    frederic007 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    5
    Click image for larger version. 

Name:	20191215_002244.png 
Views:	12 
Size:	42.4 KB 
ID:	40445
    just attached a sample data.
    thank you.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Frederic,

    Based on your limited response, here is a mock up that may be helpful

    Code:
    Sub frederic()
        Dim oldname As String, newname As String, othername As String, matchresult As Boolean
        Dim i As Integer
        oldname = "abc"
        newname = "bca"
        othername = "acb"
        Debug.Print "Oldname", "OtherName", "This vs", "This ", "Result "
        For i = 1 To Len(oldname)
        If Mid(othername, i, 1) = Mid(oldname, i, 1) Then
            matchresult = True
            Debug.Print oldname, othername, Mid(othername, i, 1), Mid(oldname, i, 1), matchresult
        Else
            matchresult = False
            Debug.Print oldname, othername, Mid(othername, i, 1), Mid(oldname, i, 1), matchresult
        End If
        Next i
    End Sub
    This is the output.

    Code:
    Oldname       OtherName     This vs       This          Result 
    abc           acb           a             a             True
    abc           acb           c             b             False
    abc           acb           b             c             False

  7. #7
    frederic007 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    5
    thanks! but problems with
    1. all records numbet over 17k and should not be hard code?
    2. abc not match with a
    in fact, just compare 2 columns of data to see match or not. that is true or false. just think of only one column has new and old name but they are the same record. either one (match new 'or' old should be true with column 3 (the other record). just to check any discrepancy input.
    thank you once again.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is 'other record'? Other record from where?

    IIf([oldname]=[other name] Or [newname]=[other name], True, False)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Fred,
    As mentioned previously in #2 and #4, you should provide more details of your requirement and/or samples showing the type of data you are dealing with and whatever transformations you expect on that data.
    I provided a simple mock up in post 6, but it was just one of many possible responses.
    If you can't describe and communicate what you are trying to accomplish, no one can provide a solution.
    We are willing to help, but need information from you who knows the requirements.

    Are you dealing with records from tables???? An existing database??
    Readers need to know "what" before suggesting "how".
    Good luck.

  10. #10
    frederic007 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    5
    Yes. Will try the statment when back to office.
    Thank you all of you!

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

Similar Threads

  1. Replies: 3
    Last Post: 10-24-2017, 05:16 PM
  2. Replies: 2
    Last Post: 04-13-2017, 02:49 PM
  3. Replies: 2
    Last Post: 10-26-2015, 06:14 AM
  4. How to solve syntex Error In INSERT INTO Statement?
    By kingsoh in forum Programming
    Replies: 0
    Last Post: 06-07-2012, 12:00 PM
  5. Replies: 2
    Last Post: 05-17-2012, 03:52 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