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","")
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","")
I *think* it is taking into account that the supplier name might have a ' in it, like O'Brien ?Code:=IIf(DCount("*","[Suppliers Extended]","[ID]<>" & Nz([ID],0) & " And [Supplier Name] = '" & Replace(Nz([Supplier Name]),"'","''") & "'")>0,"Possible Duplicate","")
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
* 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.
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
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.
1 year old thread?
old thread?