What is wrong in the code

sbaud2003

Member
Local time
Today, 13:41
Joined
Apr 5, 2020
Messages
184
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
 
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.
Code:
U1 = DCount("OccupantID", "RoomOccupations", "DepartureDate > Date() And FY = " & D)
' or
U1 = DCount("OccupantID", "RoomOccupations", "DepartureDate > Date() And FY = Year(Date())")
 
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.
Code:
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
 

Users who are viewing this thread

Back
Top Bottom