Results 1 to 4 of 4
  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
    2,521
    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
    10,895
    * 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,020
    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

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