Using DCount in Access with multiple criteria

1
votes

I am trying to count the number of records in a table which satisfy criteria for two different fields. Both fields are string values.

The first field is what type of Test appears e.g. 'Manometry'. I can get this field to work on it's own.

I experience a problem when trying to add the second criteria.

The second field is the TestID, which is in the format A_155_19, where 155 is the investigation number and 19 identifies the year it took place. I would like to count all the manometry tests which occur in the current year.

DCount("[Test]", "Visits", "[Test] = 'Manometry'" & "[TestID] = *Right((Year(Date)), 2)'")

I am currently getting the error message 3075, which is missing syntax.

Any help would be greatly appreciated.

2
vbams-access

2 Answers

0
votes

What about: 

DCount("[Test]", "Visits", "[Test] = 'Manometry' And [TestID] Like " & SomeExpression)
0
votes

You can use Format:

DCount("*", "Visits", "[Test] = 'Manometry' And [TestID] Like '*_' & Format(Date(), "yy") & "'")