multiple criteria in DCount function

  • Thread starter Thread starter Darren \(at work\)
  • Start date Start date
D

Darren \(at work\)

Hi,
I need to have multiple criteria in a DCount function but have been unable
to make this work.
What I need is to count all the records from the 'Inspection' table WHERE:

(Date BETWEEN Variable1 AND Variable2) AND ('Prod_Rej' = Variable3) AND
(PartCode = Variable4)

Something like the following:

intProdfaults = intProdfaults = DCount("*", "Inspection", "[Partcode] =
PartVariable" & "[Date] between DateVariable1 and DateVariable2" &
"[Prod_Rej]=true")

Many thanks for any and all help
Darren
 
Try something like

intProdfaults = DCount("*", "Inspection", "[Partcode]=PartVariable And
([Date] Between #" & DateVariable1 "# And #" & DateVariable2 & "#) And
[Prod_Rej]=True")

If the Between doesn't work, you may have to use [Date]>= DateVariable1 And
[Date]<=DateVariable2. Also, The # signs may not be needed. They are
sometimes needed as date delimiters so that Access doen't think the date
(i.e. 12/10/2003) is a division problem instead of a date.
 
"Wayne Morgan" very kindly wrote in reply to my message

| Try something like
|
| intProdfaults = DCount("*", "Inspection", "[Partcode]=PartVariable And
| ([Date] Between #" & DateVariable1 "# And #" & DateVariable2 & "#) And
| [Prod_Rej]=True")

I have broken my problem into small parts as I cannot get them to wok as a
whole as yet.

1) Filter by PartCode alone (this works):

intProdfaults = DCount("*", "Inspection", "[PartCode]='" & strPartCode &
"'")
-----------------------------
2) Filter where Prod_Rej = True (This works):

intProdfaults = DCount("*", "Inspection", "[Prod_rej] = true")
-----------------------------
3) Filter Between 2 dates(This does not bring back the correct number of
entries):

intProdfaults = DCount("*", "Inspection", "([InspectDate] BETWEEN #" &
Format(dDate1, "dd\/mm\/yyyy") & _
"# AND #" & Format(dDate2, "dd\/mm\/yyyy") & "#)")
-----------------------------
| If the Between doesn't work, you may have to use [Date]>= DateVariable1
And
| [Date]<=DateVariable2. Also, The # signs may not be needed. They are
| sometimes needed as date delimiters so that Access doen't think the date
| (i.e. 12/10/2003) is a division problem instead of a date.

I have tried using '>=' and '<=', this does not work.

I have tries to get the two parts that do work, to work as a whole, but have
been unable to do so. I think that this is probably an error in the syntax?

I cannot understand why it is not returning the correct number of entries
when filtering between dates. I know that Access has some issues with local
dates (or something to that effect), that's why I have my date variable
formatted in such a way (taken from this ng ages ago :-) ).

If anyone can shed some more light on this it would be most greatfully
appreciated.

Darren
 
Wayne's code looks good except he missed an "&" after "DateVariable1"
It may also need "[Partcode]='" & partvariable & "' And.... " instead of
the code without the ampersands

If the fields and variables are date type then you don't need to format the
dates just compare them as wayne has done

Tom
Wayne Morgan said:
Try something like

intProdfaults = DCount("*", "Inspection", "[Partcode]=PartVariable And
([Date] Between #" & DateVariable1 "# And #" & DateVariable2 & "#) And
[Prod_Rej]=True")

If the Between doesn't work, you may have to use [Date]>= DateVariable1 And
[Date]<=DateVariable2. Also, The # signs may not be needed. They are
sometimes needed as date delimiters so that Access doen't think the date
(i.e. 12/10/2003) is a division problem instead of a date.

--
Wayne Morgan
MS Access MVP


Darren (at work) said:
Hi,
I need to have multiple criteria in a DCount function but have been unable
to make this work.
What I need is to count all the records from the 'Inspection' table WHERE:

(Date BETWEEN Variable1 AND Variable2) AND ('Prod_Rej' = Variable3) AND
(PartCode = Variable4)

Something like the following:

intProdfaults = intProdfaults = DCount("*", "Inspection", "[Partcode] =
PartVariable" & "[Date] between DateVariable1 and DateVariable2" &
"[Prod_Rej]=true")

Many thanks for any and all help
Darren
Click to expand...
Click to expand...
 
Back
Top