Dcount with a Data range

[Eureka99]

Hi,

I'm trying to count the instances of a vendor ID appearing within my inspection log table within the last 12 months.
The base DCount works, but I'm having issues adding on the date criteria on the end. I've cobbled this together from various peoples reposonses I found on google.

Can someone take a look and assist please.

DRCVD = Date - 365
PREJ = DCount("ID", "Tbl_Inspection_log", "[Vendor]='" & Vendor2 & "'" And "[DATE RECEIVED] => # " & DRCVD & "#")

 

[CJ_London]

you have too many double quotes, => must be >= and you need to format your date as US format (mm/dd/yyyy) or ISO format (yyyy-mm-dd). You also have a superflous space after your first #

DRCVD = Date - 365
PREJ = DCount("ID", "Tbl_Inspection_log", "[Vendor]='" & Vendor2 & "' And [DATE RECEIVED] >= #" & format(DRCVD,"yyyy-mm-dd") & "#")

or just include the date function in your criteria

PREJ = DCount("ID", "Tbl_Inspection_log", "[Vendor]='" & Vendor2 & "' And [DATE RECEIVED] >=date()-365")

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:

tt="Eg'g"

? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

 

[Mark_]

I've seen too many "We need to go back 12 months" meaning "We need to go back to the same month, one year ago, but starting on the first day (or first business day) of the month".

Your current code wouldn't return "1 December" as 12 months ago.
Your looking more for DRCVD = DateSerial(Year(Date())-1, Month(Date()), 1)

OR

Change And [DATE RECEIVED] >=date()-365 to And [DATE RECEIVED] >= DateSerial(Year(Date())-1, Month(Date()), 1)