DCount

[aziz rasul]

I have the following code: -

CurrentDb.Execute "UPDATE [" & Me.cboTableName & "] SET [" & Me.cboTableName & "].[" & rst!FieldName & "] = Replace([" & rst!FieldName & "],'|',' ') WHERE ((([" & Me.cboTableName & "].[" & rst!FieldName & "]) Like '*|*'));"

Is there a way of counting the number of records updated without physically creating the update query?

 

[skaas]

Do you wish to count the values directly from tables or from a pre-determined recordset?

 

[skaas]

General

Here are some viable options that may help put you on the right track

I hope these little snippets help


DCount counts the number of records that meet criteria specified in the criteria:

DCount("FieldToCount","Table or Query Name","Criteria")
Examples: The first example counts the number of records in the customer table that have a conatact name greater than S. The second counts contact names less than S and the third counts contact names that have S as the first letter.
DCount("[ContactName]","Customers","[ContactName] > 'S'")

DCount("[ContactName]","Customers","[ContactName] < 'S'")

DCount("[ContactName]","Customers","[ContactName] Like 'S*'")
DSum sums the field of records that meet criteria specified in the criteria:

DSum("FieldToSum or Expr","Table or Query Name","Criteria")
Examples: The first example sums all the InvoiceTotals in the CustomerInvoices Table. The second example sums all the InvoiceTotals in the CustomerInvoices Table that have an invoice date greater than 21st Febuary 2000. The third example shows how you can perform a calculated sum, this particular one takes the InvoiceSubTotal multiplies by CityTaxCode and then sums all those for each record.
DSum("[InvoiceTotal]","CustomersInvoices")

DSum("[InvoiceTotal]","CustomersInvoices","[InvoiceDate] > #21/1/2000#")

DSum("[InvoiceSubTotal] * [CityTaxCode]","CustomersInvoices")
DAvg gives the average of all records that meet criteria specified in the criteria:

DSum("FieldToAverage or Expr","Table or Query Name","Criteria")
Examples: The first example gives the average of all the InvoiceTotals in the CustomerInvoices Table. The second example gives the average of all the InvoiceTotals in the CustomerInvoices Table that have an invoice date greater than 21st Febuary 2000.
DAvg("[InvoiceTotal]","CustomersInvoices")

DAvg("[InvoiceTotal]","CustomersInvoices","[InvoiceDate] > #21/1/2000#")

 

[aziz rasul]

Thanks skaas. I tried

DCount("[fieldname]", "tablename", "[fieldname] Like '*&*'")

before I run the update query to count the number of records that contains the '&'.

 

[skaas]

Does it all work ? Or are you still having errors?

 

[aziz rasul]

It worked. Thanks.

 

[LPurvis]

Hi

Just to mention that if you're wanting to decide whether to run the Update query or not then your initial count on the database makes some sense. (There are various ways of counting though - a domain function can be a good choice, but not necessarily the best ;-)

However if you're executing the Update statement anyway - then you can determine the number of records that were updated by that statement such as

With CurrentDb
.Execute "UPDATE [" etc etc...
Msgbox "There were " & .RecordsAffected & " rows updated"
End With

A single hit upon the database - but the information still returned.

Cheers.

 

[aziz rasul]

Ah, I forgot that there was RecordsAffected. It's been a while since I used it. Thanks for that. I will remember that for future.