DCOUNT Muliple criteria

[civl_eng]

Hi all, i,m using Dcount for find duplicate value in table by tow criteria, in this table i want to find duplicate of Text480 in floor field by tow criteria, i write this code but it's not working :

x = DCount("Floor", "tbl_Floor", "[fgk]= " & Me.Text60 & "And [Floor]= '" & Me.Text480 & "'")

[CJ_London]

you are missing a space before And

[civl_eng]

thank you for reply, i correct this but its doesn't work again.

[June7]

Doesn't work means what - error message, wrong result, nothing happens?

[civl_eng]

sorry wrong, with space before and it worked...now i have a question, can i do this with SQL in vba??? (Just for learning)

[June7]

Not sure what you want. Yes, SQL statements can be constructed in VBA.

[civl_eng]

i want to find Count of value in a Field by other field criteria, for example in above table i'm going to find Count of "Nine" when fgk is "5000" (Of course this values type in Text60 and Text480 ) , i wrote Below code in vba :

DCount("Floor", "tbl_Floor", "[fgk]= " & Me.Text60 & " And [Floor]= '" & Me.Text480 & "'")

i don't know whether I wrote it right or not, but its working for me, now my question is can i do this another way or with SQL code????

[June7]

Still not sure what you are asking for.

Domain aggregate functions can be used in query, textbox, VBA.

Also, an aggregate (GROUP BY) query can summarize data (count, sum, avg, etc).

[Minty]

You could open a recordset in VBA and then check the returned reordcount to see if there are existing records.
In this case though , the DCount() will almost certainly be quicker and is simpler to write.

[ArviLaanemets]

In case you want to apply the formula to unbound tect box on form

=DCount("Floor","tbl_Floor","fgk = " & Nz([text60],9999999999) & " AND Floor = '" & [text480] & "'")

[civl_eng]

it's great, thanks you ArviLaanemets for guide, can you write this code In the form of SQL code in vba????

[June7]

As Minty said, can construct an SQL SELECT statement to open recordset object.

Do some research and learn about recordset objects. Start with http://allenbrowne.com/ser-29.html

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Count(*) AS CountRecs FROM tbl_Floor WHERE [fgk]= " & Me.Text60 & " And [Floor]= '" & Me.Text480 & "'", dbOpenDynaset)
MsgBox rs!CountRecs


Action SQL (DELETE, UPDATE, INSERT) can also be constructed and executed with VBA and do not involve recordset object. Research DoCmd.RunSQL and CurrentDb.Execute methods. DoCmd.OpenQuery can be used to run an Access action query object.