DCount

[azizrasul]

I need to recreate a form in Excel from Access. In the Access form I use

 If DCount("*", "tblLicences") =0 Then

What the equivalent be in Excel?

 

[RoryA]

There isn't a direct equivalent. As long as you have a column in your table that is always populated for a given row, you could use COUNTA(Tablename[ColumnName])

 

[azizrasul]

I have tried

intFindLastRow = WorksheetFunction.CountA(tblLicences, [FIELD23])

But I always get a return value of 2 even though there are more than 2 rows with 23 columns all populated. It's the number of rows that I am after.

 

[RoryA]

You can't refer to ranges like that in VBA, you should use:

intFindLastRow = WorksheetFunction.CountA(Range("tblLicences[FIELD3]"))

or:

intFindLastRow = WorksheetFunction.CountA(Sheets("sheet name").Listobjects("tblLicences").Listcolumns("FIELD3").Databodyrange)

 

[azizrasul]

You can't refer to ranges like that in VBA, you should use:

intFindLastRow = WorksheetFunction.CountA(Range("tblLicences[FIELD3]"))

or:

intFindLastRow = WorksheetFunction.CountA(Sheets("sheet name").Listobjects("tblLicences").Listcolumns("FIELD3").Databodyrange)

Both worked. Many thanks.