DCount in Access 2016

Question

I am trying to use the DCount() function with limited success.  I can get it to work with one variable but when I try to use it to check for two variables I get a Data Type Mismatch error.  Here is what I have done so far.

I have a test table with a short list of fictitious names and random dates.  I have a simple form where I enter a User ID number and a date and I would like DCount to see if there are any matches in the table with that ID Number and date.  I am creating a database for people to enter their activity but I'd like to limit them to only being able to enter once per date.  I'd like DCount to check the table and if there are matches on the ID and Date to not allow them to enter another record. 

I can get DCount to tell me how many matches there are in the ID field:

FoundCust1 = DCount("*", "Customers", "[CustNum] =" & Me.txtGetCustNum)

And I can get DCount to tell me how many matches there are in the Date field:

FoundDate1 = DCount("*", "Customers", "[DateEntered] = #" & Format(Me.txtDateEntered, "mm/dd/yyyy") & "#")

But, when I put them together, I get a data type mismatch error:

FoundRecord = DCount("*", "Customers", "[CustNum] =" & Me.txtGetCustNum And "[DateEntered] = #" & Format(Me.txtDateEntered, "mm/dd/yyyy") & "#")

I've tried adding extra parentheses and modeling the Criteria statement after the SQL that works in a query, but to no avail.

Kurt

Answer 1

Try this assuming CustNum is numeric:

FoundRecord = DCount("*", "Customers", "[CustNum] =" & Me.txtGetCustNum &" And [DateEntered] = #" & Format(Me.txtDateEntered, "mm/dd/yyyy") & "#")

Answer 2

Thanks, Duane.  That did work.  I do appreciate it, very much.  This will get saved to a three or four places so it won't get lost.

Kurt Holmquist

Wisconsin

Answer 3

Duane's answer should work. the problem was that you have to concatenate in the values and this means closing your quotes. So you need something like:

"fieldname = " & variable & " AND fieldname = " & Variable

Then depending on the data type of the variable you may need to use delimiters (quotes and octothorpes) around the variables.