DCount function wording of the CRITERIA

Question

I have problems with the wording of the criteria of a DCount function.

On click() I would like to have the number of occurrences of the field "YearMonth" in a tale in accordance with the value entered in an inbound fields called "Txt_entry".

My code ignores the criteria given (and returns 0) given that its wording is wrong but I cannot find out what would be the correct wording.

Private Sub Ctl3_Click()
   Dim db As Database
   Dim r As Recordset
   Dim YearMonth As Field
   Dim Txt_entry As String
   Set db = CurrentDb()
   Set r = db.OpenRecordset("Table")
   Set YearMonth = r.Fields("YearMonth")
   MsgBox (DCount("YearMonth", "Table", "[YearMonth]=" & Me.Txt_entry))
   End Sub

Thanks!

Is YearMonth a text field? Have you tried a value egYearMonth = 201510 — Fionnuala

chdelamo chdelamo · Accepted Answer · 2015-07-08T14:35:26

You can always try:

For a string DCount("YearMonth", "Table", "[YearMonth]= '" & Me.Txt_entry & "'")

For a date: DCount("YearMonth", "Table", "[YearMonth]= #" & Me.Txt_entry & "#")

Depending how you store it in your database. You dont need to set those variables for Dcount or Dlookup(assuming all you want is the count)

Variables only need to be set if you have some other criteria you want to enter in the Where clause of the dcount.

As for the me.Text_Entry , try me.Text_entry.value and see if the box has an actual value.

You can test it by throwing it into msgbox(me.txt_entry.value) That may be the cause of not getting a proper count. As the query would end up as YearMonth = "" without a proper value.

DCount function wording of the CRITERIA

2 Answers