Results 1 to 7 of 7
  1. #1
    Liowan is offline Novice
    Windows 10 Access 2019
    Join Date
    Jul 2022
    Posts
    1

    Unhappy What is going on here????

    Can someone please explain to me in simple terms, what is happening here:



    =IIf(DCount("*","[Suppliers Extended]","[ID]<>" & Nz([ID],0) & " And [Supplier Name] = '" & Replace(Nz([Supplier Name]),"'","''") & "'")>0,"Possible Duplicate","")

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Code:
    =IIf(DCount("*","[Suppliers Extended]","[ID]<>" & Nz([ID],0) & " And [Supplier Name] = '" & Replace(Nz([Supplier Name]),"'","''") & "'")>0,"Possible Duplicate","")
    I *think* it is taking into account that the supplier name might have a ' in it, like O'Brien ?
    So just counting where ID is not zero and name matches?

    Putting the code within code tags makes it easier to see?
    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

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    * is often used in DCount as a stand in for when you don't want or need to refer to a specific field.
    If ID is null, Nz returns 0 - Nz([ID],0
    So, count in the domain (table) [Suppliers Extended] those records where ID is not 0 and Supplier Name = (Supplier Name without apostrophe's) and return only where DCount results in > 0.

    At least that's what it looks like at a quick glance.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It's looking for duplicate supplier names in the [Suppliers Extended] table.

    Code:
    count = number of records in the [Suppliers Extended] table with a ID value different than given [ID] but with the same [Supplier Name]
    
    If count > 0 Then
        result = "Possible Duplicates"
    Else
        result = ""
    End If

    =IIf(DCount("*","[Suppliers Extended]","[ID]<>" &
    Nz([ID],0) & " And [Supplier Name] = '" & Replace(Nz([Supplier Name]),"'","''") & "'")>0,"Possible Duplicate","")

    {STEP 1} => Nz([ID],0)
    If ID is equal to Null then use 0 (zero) instead

    {STEP 2} => Nz([Supplier Name])
    If supplier name is null then use empty string instead

    {STEP 3} => Replace({STEP 2}),"'","''")
    Take the resulting string from {STEP 2} above and replace any single quotation marks that are in the string with 2x single quotation marks. This is called 'escaping'. This is needed because we're essentially building an SQL query in the background and an unescaped quotation mark will cause an error

    {STEP 4} => DCount("*","[Suppliers Extended]","[ID]<>" & {STEP 1} & " And [Supplier Name] = '" & {STEP 3} & "'")
    Build and execute the following sql query:
    SELECT Count(*) FROM [Supplier Extended] WHERE [ID] <> {STEP 1} And [Suppler Name] = '{STEP 3}'
    Notice in the sql statement above the single quotes surrounding {STEP 3}? If the single quotes weren't escaped with the replace function in step 3 then that would confuse the sql engine.

    {STEP 5} => IIf( {STEP 4} > 0, "Possible Duplicate", "")
    If the count query from {STEP 4} is a number greater than zero then return "Possible Duplicates", otherwise just return an empty string.

    IIf(): https://support.microsoft.com/en-us/...0-647539c764e3
    DCount(): https://support.microsoft.com/en-us/...a-11a64acbf3d3
    Nz(): https://support.microsoft.com/en-us/...a-7fd9f4c69b6c
    Replace(): https://docs.microsoft.com/en-us/off...place-function
    Escaping quotation marks: http://allenbrowne.com/casu-17.html

  5. #5
    fronflower is offline Novice
    Windows 10 Access 2019
    Join Date
    Aug 2023
    Posts
    3
    Hey there! That looks like it's using some Access database functions to check for duplicate records. Let me break it down:


    The DCount function is counting the number of records that match a certain condition in the Suppliers Extended table.


    It's checking for records where:



    • The ID field is not equal to the current record's ID value (Nz([ID],0))
    • And where the Supplier Name field equals the current record's Supplier Name value (Nz([Supplier Name]))

    The Replace function around [Supplier Name] is just escaping any apostrophes in the name to avoid issues in the SQL query.


    So in plain English, it's checking if there are any records in the table that have the same Supplier Name but a different ID compared to the current record.


    If there are no matches, it will return 0, so the IIf statement will show "Possible Duplicate". If there are any matches, it will return the count, so the IIf will be blank/empty.


    So in summary, it's just a check for duplicate supplier names, excluding the current record, to flag possible duplicates. Hopefully this helps explain what's happening! Let me know if any part is still confusing.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1 year old thread?

  7. #7
    samdavid22 is offline Novice
    Windows 10 Access 2021
    Join Date
    Mar 2024
    Location
    Charlotte, North Carolina
    Posts
    1
    old thread?

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

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