Cannot calculate between dates

[mexiro]

Hello Everyone,

I'm a beginner in access but trying to do smthing with forums and internet. I have a report in which I have a box which will count a specific fields but as it is count if I want the criteria to count all non blank fields between 14th and 17th day of every month. So I used the below format:

between #*/14/*# and #*/17/*#

The error is that invalid data range or smth like that.

What am I missing? How can I use asteriks to set a criteria between those two dates ?

Thank you ...

 

[namliam]

how about if you just use "DayOfMonth: Day([YourDateField])" this will return only the day of the month ie. 17 for today
Then simply do "between 14 and 17" to get what you desire.

 

[raskew]

Nice solution!

Best Wishes - Bob

 

[mexiro]

how about if you just use "DayOfMonth: Day([YourDateField])" this will return only the day of the month ie. 17 for today
Then simply do "between 14 and 17" to get what you desire.

Hi, Thank you for your reply,

If doesn't find any thing:

see below my expression:

count (IIf [clients]<>0 and IIf(day ([date file created]) between 14 and 17,0),0)

Why this does not work i do not understand. All I want is clients different than 0 between dates 14 -17 of each months.

In this way the counter is always 0

Please help ,,,

 

[namliam]

You never said anything about a count or IIF you wanted a criteria...

It will always return the same number, as you have always 0 as your output.

Read the help on IIF... IIF (Criteria,True, False) you need the true and false properties set.

Something like:
Sum (IIf [clients]<>0 and day ([date file created]) between 14 and 17,1,0)

Is what you are looking for.

 

[namliam]

Nice solution!

Thanks bob...

Curious about your sig, what is Jan 20 all about?

 

[raskew]

Jan. 20, 2009 - U.S. Presidential Inauguration Day!

But who's counting?

Added: Interesting message when I attempted to add to Namliam's reputation:

You must spread some Reputation around before giving it to namliam again. Never saw that before.

Bob

 

[mexiro]

You never said anything about a count or IIF you wanted a criteria...

It will always return the same number, as you have always 0 as your output.

Read the help on IIF... IIF (Criteria,True, False) you need the true and false properties set.

Something like:
Sum (IIf [clients]<>0 and day ([date file created]) between 14 and 17,1,0)

Is what you are looking for.

Hi,


How am I suppose to count clients different than 0:
count (IIf [clients]<>0 and day ([date file created]) between 14 and 17,1,0)

In this way it returns 1 or 0 ???

Sorry for being a starter

 

[namliam]

I made a typo...
count (IIf([clients]<>0 and day ([date file created]) between 14 and 17,1,0) )

Or better, I forgot to add () for the IIF.

Count counts the number of values, both 1 and 0 are values so count will count all ones and zero's.

Now if you are bend on using the count you, you need to NULL the false part.
count (IIf([clients]<>0 and day ([date file created]) between 14 and 17,1,null) )
Whenever I am doing stuff like this... which is rarely as one should minimize the IIFs one uses... I use Sum not count as it is a little more flexible IMHO.

@Bob,
re: You must spread some Reputation around before giving it to namliam again.
This means you have hit the rep button for me semi recently you must "spread" it around (i.e. rep some others) before you can "hit" me again.
Thanks for the sentiment though

 

[Singh400]

Added: Interesting message when I attempted to add to Namliam's reputation:

You must spread some Reputation around before giving it to namliam again. Never saw that before.

Bob

Yeaaah I had that too. Look at Namliam hoggin all the rep. Tut tut

 

[mexiro]

I made a typo...
count (IIf([clients]<>0 and day ([date file created]) between 14 and 17,1,0) )

Or better, I forgot to add () for the IIF.

Count counts the number of values, both 1 and 0 are values so count will count all ones and zero's.

Now if you are bend on using the count you, you need to NULL the false part.
count (IIf([clients]<>0 and day ([date file created]) between 14 and 17,1,null) )
Whenever I am doing stuff like this... which is rarely as one should minimize the IIFs one uses... I use Sum not count as it is a little more flexible IMHO.

@Bob,
re: You must spread some Reputation around before giving it to namliam again.
This means you have hit the rep button for me semi recently you must "spread" it around (i.e. rep some others) before you can "hit" me again.
Thanks for the sentiment though

Hey,

This is not working again,
Does anyone have an alternative solution ?
The problem is that I have a group by [product type] in my reports and this count has to be sum/count for this group not for the whole report. I put group on the text box value to sum for the group but again is not working. I'm going nuts with this access.

 

[Brianwarnock]

The problem is that I have a group by [product type] in my reports and this count has to be sum/count for this group

Isn't that new info?

The approach using Sum should still work.
else in design grid try

Group on product
Where on client critreia <>0
where on day([date file created]) criteria the between clause
expression for Count(*)

Brian

 

[mexiro]

Sorry but I don't know how to do that,

Can u be more specific ?

 

[namliam]

Select [product type], SUM (IIF([clients]<>0 and day ([date file created]) between 14 and 17,1,0) )
from yourtable
group by [product type]

That works (tested and all), make sure your [date file created] is a REAL date field.

Other than that I cannot think of a solution, maybe if you post your DB we can find the problem.

 

[namliam]

Isn't that new info?

Not exactly you must group something if you are doing a count/sum mustnt you??

else in design grid try

Somewhat criptic, but in actuallity I was thinking you want this...
Try my above though and see how you get on.

 

[Brianwarnock]

Not exactly you must group something if you are doing a count/sum mustnt you??

QUOTE]

Whislt one would expect so I've learnt not to assume anything from our posters, why cant they just tell us everything up front as it makes it easier to explain.

I was trying to offer a second approach as he did not seem to appreciate the use of Sum, your approach is the one I would have used from the start.

Brian

PS I've helped Stopher out - see rep

 

[namliam]

PS I've helped Stopher out - see rep

LOL...

Guess the rep is important?? But I am not here for that though...
But Thanks

This Stopher thread could actually help the OP as well...
http://www.access-programmers.co.uk/forums/showthread.php?p=775030#post775030

It does a simular thing as this question

 

[Brianwarnock]

When I finally found the post to do it discovered it was Bob (Raskew) not Chris (Stopher)

Brian

 

[mexiro]

Thank you thank you very much guys,

The problem was that I wanted this text box in the group header field. When I copied this on the body it works,

buye.
Ps. It's the first time I work on smth in access and cracked it, thanks to you..