Can anyone debug my attempt at a DCount

[Douglas]

I want to do a record count on one table
The table is called : Vehicle_Customer and looks like this:

Vehicle_Customer

Reg_No
Make_Model
MOT_Due_Date
etc etc etc

The MOT_Due_Date is a text field in the format "dd mmm" ie "15 Sep",
and for various reasons i have kept it as a text field and not a date
field

Im trying to write a DCount that returns all the MOT_Due_Dates between
the first of the current month and 6 weeks from the first of the
current month.
ie MOT Due Date BETWEEN "01/01/2004" AND "12/02/2004"

So far I have:

tmpcount = DCount("Vehicle_Customer.Reg_No", "Vehicle_Customer", "#" &
Format(Vehicle_Customer.MOT_Due_Date & " " & Format(Now(), "yyyy"),
"mm/dd/yyyy") & "# BETWEEN #" & Format(DateSerial(Year(Now()),
Month(Now()), 1), "mm/dd/yyyy") & "# AND #" & Format(DateAdd("w",
DateSerial(Year(Now()), Month(Now()), 1), 42), "mm/dd/yyyy") & "#")


What i do is add the Current year onto the MOT_Due_Date then convert
it to the format "mm/dd/yyyy".
Im pretty sure the stuff ater the BETWEEN is ok as i use it in another
query.

The error I get is: 424 Object Required

TIA

Doug


The whole purpose of the DCount is because if it returns zero then I
want to flag up a msg that there are no records and then it wont run
the report

 

[Albert D. Kallal]

You probably do NOT want to use the now() function. The now() includes both
a date part,a and time part. This is means if you need simply date
conditions, they will NOT work, since now() <> date()

The above is very important for your defaults in fields, as if you
accidentally start using now() in place of date(), you can make a huge
message, as each date you have will also have a time portion, and thus
simply date conditions will NOT work. So, be careful!

Now, lets attack your problem:

I would try something like:

dim tmpCount as long
Dim dtStart As Date
Dim dtEnd As Date
Dim strCondition As String

dtStart = DateSerial(Year(Date), Month(Date), 1)
dtEnd = DateAdd("ww", 6, dtStart)

strCondition = "Mot_Due_Date Between " & _
Format(dtStart,"\#mm/dd/yyyy\#") & _
" and " & _
Format(dtEnd, "\#mm/dd/yyyy\#")

tmpCount = DCount("Reg_No", "Vehicle_Customer", strCondition)