DayOfWeekCount

  • Thread starter Thread starter NotGood@All
  • Start date Start date
N

NotGood@All

This code (in a query) counts how many things were done on Mondays, I would
like it to count how many things were done on Mondays that were less than
$25. (I stink up the place with Access)

xMon: Count(IIf(Weekday([ToDaysDate])=2,1,Null))

Thanks
NotGood@All
 
While it may seem counterintuitive, I feel safer Sum rather than relying on
how Count treats Null values.

For your existing query, I'd use

xMon: Sum(IIf(Weekday([ToDaysDate])=2,1,0))

What does "less than $25" mean? Is it whether or not the specific record has
a value of $25? If so, you'd use

xMon: Sum(IIf(Weekday([ToDaysDate])=2 And [ValueField] >= 25,1,0))

Otherwise, you'll have to provide more details.
 
Douglas, thank you for responding! I'm trying to do 2 things within this
query, Count the number of things that happened on each day of the week, and
sum the value of the things that happened on each day of the week. I used
your suggestion of:

xMon: Sum(IIf(Weekday([ToDaysDate])=2 And [ValueField] < 25,1,0))
but replaced "Sum" with "Count", it works! I added this to give me a total
for all days:

xSum: Sum(IIf(Weekday([ToDaysDate]) Between 1 And 7 And
[collected]<25,1,Null)) but I get the total number of entries in the database

Thanks

Douglas J. Steele said:
While it may seem counterintuitive, I feel safer Sum rather than relying on
how Count treats Null values.

For your existing query, I'd use

xMon: Sum(IIf(Weekday([ToDaysDate])=2,1,0))

What does "less than $25" mean? Is it whether or not the specific record has
a value of $25? If so, you'd use

xMon: Sum(IIf(Weekday([ToDaysDate])=2 And [ValueField] >= 25,1,0))

Otherwise, you'll have to provide more details.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



NotGood@All said:
This code (in a query) counts how many things were done on Mondays, I
would
like it to count how many things were done on Mondays that were less than
$25. (I stink up the place with Access)

xMon: Count(IIf(Weekday([ToDaysDate])=2,1,Null))

Thanks
NotGood@All
Click to expand...
Click to expand...
 
Back
Top