DayOfWeekCount

[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

 

[Douglas J. Steele]

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.

 

[open a adobe file from a command button]

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

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!)

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