DCOUNT

[ghostman via AccessMonster.com]

i have this DCOUNT formula on a text box which counts number of training
sessions in my record. The text box is in my main form.

=DCount("[TSID]","Training Sessions","[TSID]")

how can i make it count the number of training sessions on the current month??


Training Session table:
TSID
ModuleName
SessionDate
(some fields..)

I am counting records from THIS MONTH & Year To Date. (I have no problem in
counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

 

[John Spencer]

For the current month, you can use the following expression:
DCOUNT("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

For Last Month the third argument would be
"SessionDate between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date())+1-1,0)"

The expression
DateSerial(Year(Date()),Month(Date())+1,0)
returns the last day of the month. It helps to think of this as
DateSerial(Year(Date()),Month(Date())+1,1) -1
which can be expressed as
DateSerial(Year(Date()),Month(Date())+1,1-1)
and 1-1 is zero.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ghostman via AccessMonster.com]

Perfect!!

thank you very much!

For the current month, you can use the following expression:
DCOUNT("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

For Last Month the third argument would be
"SessionDate between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date())+1-1,0)"

The expression
DateSerial(Year(Date()),Month(Date())+1,0)
returns the last day of the month. It helps to think of this as
DateSerial(Year(Date()),Month(Date())+1,1) -1
which can be expressed as
DateSerial(Year(Date()),Month(Date())+1,1-1)
and 1-1 is zero.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

i have this DCOUNT formula on a text box which counts number of training
sessions in my record. The text box is in my main form.

[quoted text clipped - 12 lines]

counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

 

[ghostman via AccessMonster.com]

how about counting the number of NOSHOWS on each training session?

I have two tables:

[Training Sessions] (the details of a training session)
- TSID (pk)
- ModuleName
- SessionDate
- SessionTimeFrom
- SessionTimeTo
- SessionVenue
- InstructorID

[Training Records] (holds the records of trainees who attended the training
session)
- ID
- TSID (fk)
- TraineeID
- NTSMonth
- NTSModule
- NoShow

then i created a query using the above

qryTrainingRecordsNoShow

SELECT [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
InstructorID, Count([Training Records].TraineeID) AS CountOfTraineeID, Count
([Training Records].NoShow) AS CountOfNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID = [Training Records].TSID
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
InstructorID
HAVING (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))
ORDER BY [Training Sessions].SessionDate DESC;


the query should be counting something like this:

TSID ModuleName SessionDate CountOfTraineeID CountOfNoShow
401-MAS-18-08 Life Guards 401 8/18/2009 2 1
101-MAS-05-08 Attendant 201 8/5/2009 1 0

- CountOfTraineeID shows how many trainees attended
- CountOfNoShow should be showing how many trainees failed to attend a
session

What i've got so far is a query showing like this:

TSID ModuleName SessionDate CountOfTraineeID CountOfNoShow
401-MAS-18-08 Life Guards 401 8/18/2009 2 2
101-MAS-05-08 Attendant 201 8/5/2009 1 1

- CountOfNoShow is counting the same number as CountOfTraineeID...

im stuck with this...pls help!

For the current month, you can use the following expression:
DCOUNT("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

For Last Month the third argument would be
"SessionDate between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date())+1-1,0)"

The expression
DateSerial(Year(Date()),Month(Date())+1,0)
returns the last day of the month. It helps to think of this as
DateSerial(Year(Date()),Month(Date())+1,1) -1
which can be expressed as
DateSerial(Year(Date()),Month(Date())+1,1-1)
and 1-1 is zero.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

i have this DCOUNT formula on a text box which counts number of training
sessions in my record. The text box is in my main form.

[quoted text clipped - 12 lines]

counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

 

[John Spencer]

Assumption: NoShow is a Boolean (yes/no) field.

Count counts the presence of a value (True and False are both values) so
that is why you get the same number with Count(NoShow).

You can count the true values with either of the following expressions.

Abs(Sum(NoShow))

OR

COUNT(IIF(NoShow,1,Null))

So your query might look like the following. By the way, I moved the
criteria to a WHERE clause (faster performance) and I tested a date
range (again faster then testing 3 parts of the date).

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
, [Training Sessions].SessionTimeFrom
, [Training Sessions].SessionTimeTo
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
, Count([Training Records].TraineeID) AS CountOfTraineeID

, Abs(Sum(NoShow)) as CountNoShow

FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID
WHERE SessionDate Between DateSerial(Year(Date()),Month(Date()),1)
AND Date()
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
, [Training Sessions].SessionTimeFrom
, [Training Sessions].SessionTimeTo
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
ORDER BY [Training Sessions].SessionDate DESC;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

how about counting the number of NOSHOWS on each training session?

I have two tables:

[Training Sessions] (the details of a training session)
- TSID (pk)
- ModuleName
- SessionDate
- SessionTimeFrom
- SessionTimeTo
- SessionVenue
- InstructorID

[Training Records] (holds the records of trainees who attended the training
session)
- ID
- TSID (fk)
- TraineeID
- NTSMonth
- NTSModule
- NoShow

then i created a query using the above

qryTrainingRecordsNoShow

SELECT [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID, Count([Training Records].TraineeID) AS CountOfTraineeID, Count
([Training Records].NoShow) AS CountOfNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID = [Training Records].TSID
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
HAVING (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))
ORDER BY [Training Sessions].SessionDate DESC;


the query should be counting something like this:

TSID ModuleName SessionDate CountOfTraineeID CountOfNoShow
401-MAS-18-08 Life Guards 401 8/18/2009 2 1
101-MAS-05-08 Attendant 201 8/5/2009 1 0

- CountOfTraineeID shows how many trainees attended
- CountOfNoShow should be showing how many trainees failed to attend a
session

What i've got so far is a query showing like this:

TSID ModuleName SessionDate CountOfTraineeID CountOfNoShow
401-MAS-18-08 Life Guards 401 8/18/2009 2 2
101-MAS-05-08 Attendant 201 8/5/2009 1 1

- CountOfNoShow is counting the same number as CountOfTraineeID...

im stuck with this...pls help!

For the current month, you can use the following expression:
DCOUNT("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

For Last Month the third argument would be
"SessionDate between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date())+1-1,0)"

The expression
DateSerial(Year(Date()),Month(Date())+1,0)
returns the last day of the month. It helps to think of this as
DateSerial(Year(Date()),Month(Date())+1,1) -1
which can be expressed as
DateSerial(Year(Date()),Month(Date())+1,1-1)
and 1-1 is zero.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

i have this DCOUNT formula on a text box which counts number of training
sessions in my record. The text box is in my main form.

[quoted text clipped - 12 lines]

counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

 

[ghostman via AccessMonster.com]

wow! that works like a charm!
thank you very much John!

Assumption: NoShow is a Boolean (yes/no) field.

Count counts the presence of a value (True and False are both values) so
that is why you get the same number with Count(NoShow).

You can count the true values with either of the following expressions.

Abs(Sum(NoShow))

OR

COUNT(IIF(NoShow,1,Null))

So your query might look like the following. By the way, I moved the
criteria to a WHERE clause (faster performance) and I tested a date
range (again faster then testing 3 parts of the date).

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
, [Training Sessions].SessionTimeFrom
, [Training Sessions].SessionTimeTo
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
, Count([Training Records].TraineeID) AS CountOfTraineeID

, Abs(Sum(NoShow)) as CountNoShow

FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID
WHERE SessionDate Between DateSerial(Year(Date()),Month(Date()),1)
AND Date()
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
, [Training Sessions].SessionTimeFrom
, [Training Sessions].SessionTimeTo
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
ORDER BY [Training Sessions].SessionDate DESC;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

how about counting the number of NOSHOWS on each training session?

[quoted text clipped - 86 lines]

 

[ghostman via AccessMonster.com]

now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
want this to be in a textbox and ill put it in my form.

i am using this:

=DCount("[NoShow]","Training Records","[NoShow] = True")

and it counts all NOSHOWS in my record which includes also NOSHOWs from
previous months.

i have no idea how to do this by month...i want something the same as:

=DCount("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")


which count Training Session on the current month.
please help..

here is my query SQL:

SELECT [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID, Count([Training Records].TraineeID) AS CountOfTraineeID, Abs
(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID=[Training Records].TSID
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
HAVING (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
<=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))
ORDER BY [Training Sessions].SessionDate DESC;

wow! that works like a charm!
thank you very much John!

Assumption: NoShow is a Boolean (yes/no) field.

[quoted text clipped - 49 lines]

 

[John Spencer]

Perhaps something like the following. Eliminate group by fields to get
counts my groups. For instance if you need a count by instructor then
include instructor, but if you need the count by sessionvenue but not by
Session Venue plus instructor, remove instructor from the group by and
select clauses of the queryl

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM") as YearMonth
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
, Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID=[Training Records].
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM")
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID

WHERE (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
<=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))

ORDER BY Format([Training Sessions].SessionDate,"YYYY-MM")




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
want this to be in a textbox and ill put it in my form.

i am using this:

=DCount("[NoShow]","Training Records","[NoShow] = True")

and it counts all NOSHOWS in my record which includes also NOSHOWs from
previous months.

i have no idea how to do this by month...i want something the same as:

=DCount("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")


which count Training Session on the current month.
please help..

here is my query SQL:

SELECT [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID, Count([Training Records].TraineeID) AS CountOfTraineeID, Abs
(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID=[Training Records].TSID
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
HAVING (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
<=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))
ORDER BY [Training Sessions].SessionDate DESC;

wow! that works like a charm!
thank you very much John!

Assumption: NoShow is a Boolean (yes/no) field.

[quoted text clipped - 49 lines]

counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

 

[ghostman via AccessMonster.com]

how can i apply this to a TEXTBOX?

i am adding a summary of the training on my main form (i.e., how many
training session conducted, trainees attended, training noshows, etc.,) so it
will give an instant info of the records.

This Month Year to Date
Training Sessions Conducted 01 05
Training NoShow 00 01

something like that.



on textbox1 (Training Sessions Conducted - This Month count) i have this:
=DCount("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

while textbox2 (year to date count):
=DCount("[TSID]","Training Sessions","[TSID] = True")


for textbox3 (NOSHOW - Year To Date)
=DCount("[NoShow]","Training Records","[NoShow] = True")

and for textbox4 (NOSHOW - This Month), i tried this:
=DCount("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

and showing ERROR!

Perhaps something like the following. Eliminate group by fields to get
counts my groups. For instance if you need a count by instructor then
include instructor, but if you need the count by sessionvenue but not by
Session Venue plus instructor, remove instructor from the group by and
select clauses of the queryl

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM") as YearMonth
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
, Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID=[Training Records].
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM")
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID

WHERE (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
<=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))

ORDER BY Format([Training Sessions].SessionDate,"YYYY-MM")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
want this to be in a textbox and ill put it in my form.

[quoted text clipped - 40 lines]

 

[John Spencer]

As I recall you NoShow field is a boolean - which means it always has a
value of True (yes) or False (no). Count and DCount both count the
presence of ANY value.

One way to do this is to apply one more bit of criteria in the DCount.

DCount("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0) AND CountNoShow = True")

Alternative is to use DSum instead of DCount. True is equal to -1 and
False is equal to zero. So summing all this will give you a negative
count of NoShow. You can strip the negative off by using the ABS function.

Abs(DSUM("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

how can i apply this to a TEXTBOX?

i am adding a summary of the training on my main form (i.e., how many
training session conducted, trainees attended, training noshows, etc.,) so it
will give an instant info of the records.

This Month Year to Date
Training Sessions Conducted 01 05
Training NoShow 00 01

something like that.



on textbox1 (Training Sessions Conducted - This Month count) i have this:
=DCount("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

while textbox2 (year to date count):
=DCount("[TSID]","Training Sessions","[TSID] = True")


for textbox3 (NOSHOW - Year To Date)
=DCount("[NoShow]","Training Records","[NoShow] = True")

and for textbox4 (NOSHOW - This Month), i tried this:
=DCount("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

and showing ERROR!

Perhaps something like the following. Eliminate group by fields to get
counts my groups. For instance if you need a count by instructor then
include instructor, but if you need the count by sessionvenue but not by
Session Venue plus instructor, remove instructor from the group by and
select clauses of the queryl

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM") as YearMonth
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
, Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID=[Training Records].
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM")
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID

WHERE (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
<=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))

ORDER BY Format([Training Sessions].SessionDate,"YYYY-MM")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
want this to be in a textbox and ill put it in my form.

[quoted text clipped - 40 lines]

counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

 

[ghostman via AccessMonster.com]

No luck with it sir... It shows error...

can you have a look on my query, please

SELECT [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID, Count([Training Records].TraineeID) AS CountOfTraineeID, Abs
(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID = [Training Records].TSID
WHERE ((([Training Sessions].SessionDate) Between DateSerial(Year(Date()),
Month(Date()),1) And Date()))
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
ORDER BY [Training Sessions].SessionDate DESC;

i am counting THIS MONTH numbers based on the above query.

As I recall you NoShow field is a boolean - which means it always has a
value of True (yes) or False (no). Count and DCount both count the
presence of ANY value.

One way to do this is to apply one more bit of criteria in the DCount.

DCount("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0) AND CountNoShow = True")

Alternative is to use DSum instead of DCount. True is equal to -1 and
False is equal to zero. So summing all this will give you a negative
count of NoShow. You can strip the negative off by using the ABS function.

Abs(DSUM("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

how can i apply this to a TEXTBOX?

[quoted text clipped - 64 lines]

 

[John Spencer]

Simplify. Start off with something like this and see if you get no
errors and the number you want. If it errors then try to track down why.

SELECT Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID
WHERE [Training Sessions].SessionDate
Between DateSerial(Year(Date()),Month(Date()),1) And Date()

If you get no errors add in one or two fields and see if that works and
if you get the desired result. Continue this process until you have the
final desired outcome.

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID
WHERE [Training Sessions].SessionDate
Between DateSerial(Year(Date()),Month(Date()),1) And Date()
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName

That said, I'm not sure what this has to do with using DCount or DSum as
the source for a text box.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

No luck with it sir... It shows error...

can you have a look on my query, please

SELECT [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID, Count([Training Records].TraineeID) AS CountOfTraineeID, Abs
(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID = [Training Records].TSID
WHERE ((([Training Sessions].SessionDate) Between DateSerial(Year(Date()),
Month(Date()),1) And Date()))
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
ORDER BY [Training Sessions].SessionDate DESC;

i am counting THIS MONTH numbers based on the above query.

As I recall you NoShow field is a boolean - which means it always has a
value of True (yes) or False (no). Count and DCount both count the
presence of ANY value.

One way to do this is to apply one more bit of criteria in the DCount.

DCount("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0) AND CountNoShow = True")

Alternative is to use DSum instead of DCount. True is equal to -1 and
False is equal to zero. So summing all this will give you a negative
count of NoShow. You can strip the negative off by using the ABS function.

Abs(DSUM("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

how can i apply this to a TEXTBOX?

[quoted text clipped - 64 lines]

counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.