What is wrong in the code (1 Viewer)

[sbaud2003]

dim D as an intger
D=year(Date())
U1 = DCount("[OccupantID]", "[RoomOccupations]", "[DepartureDate] > Date() And [FY]=D")

i want to count the records in the table RoomOccupations where Departuredate is greater than the current date and the year is current year.
FY is a calculated filed from [Departuredate] i.e Year(departuredate) in the main table RoomOccupations

i am getting an error

 

[ebs17]

The criterion of DCount is pure SQL. The database engine running SQL doesn't know about VBA variables. So the expression has to be worded differently.

U1 = DCount("OccupantID", "RoomOccupations", "DepartureDate > Date() And FY = " & D)
' or
U1 = DCount("OccupantID", "RoomOccupations", "DepartureDate > Date() And FY = Year(Date())")

 

[sbaud2003]

The criterion of DCount is pure SQL. The database engine running SQL doesn't know about VBA variables. So the expression has to be worded differently.

U1 = DCount("OccupantID", "RoomOccupations", "DepartureDate > Date() And FY = " & D)
' or
U1 = DCount("OccupantID", "RoomOccupations", "DepartureDate > Date() And FY = Year(Date())")

Thank a lot its working fine