Please help with function

[Guest]

The following function works:
=DCount("[ActionCode]","qryrptTrackingReport","[ActionCode] In (2,4,6) AND
[FollowUp_Date] between Date() and Date() + 7").

However, the following does not work I get (#Error):

=DCount("[TreatmentDate]","tblTreatmentSummary","[Consultation] In
('PP','Co','New') And [EntryDate] between #" & Date()-14 & "# and #" &
Date()-20 & "# and [SummaryType] = 'TreatmentSummary' and [TreatmentDate] >
Date()-180")

I think the In criteria is causing the problem. I tried without the
apostrophe and still does not work. The second case is text not numbers like
the first case. What am I doing wrong. any help please.
thanks
Al

 

[Douglas J. Steele]

I think it's the [TreatmentDate] > Date()-180

Do the same as you did with the other dates.

By the way, if there's any chance that your users might have their Short
Date format set to dd/mm/yyyy in Regional Settings, what you have will
likely fail. The safest way would be:

=DCount("[TreatmentDate]","tblTreatmentSummary","[Consultation] In
('PP','Co','New') And ([EntryDate] between " & Format(Date()-14,
"\#mm\/dd\/yyyy\#") & " and " &
Format( Date()-20, "\#mm\/dd\/yyyy\#") & ") and [SummaryType] =
'TreatmentSummary' and [TreatmentDate] > " & Format(Date()-180,
"\#mm\/dd\/yyyy\#"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The following function works:
=DCount("[ActionCode]","qryrptTrackingReport","[ActionCode] In (2,4,6) AND
[FollowUp_Date] between Date() and Date() + 7").

However, the following does not work I get (#Error):

=DCount("[TreatmentDate]","tblTreatmentSummary","[Consultation] In
('PP','Co','New') And [EntryDate] between #" & Date()-14 & "# and #" &
Date()-20 & "# and [SummaryType] = 'TreatmentSummary' and [TreatmentDate]Date()-180")

I think the In criteria is causing the problem. I tried without the
apostrophe and still does not work. The second case is text not numbers
like
the first case. What am I doing wrong. any help please.
thanks
Al