Limiting calculated query (1 Viewer)

[akuller]

Hi, I'm sure this is simple, but I am very new to access. I have a query which contains activity types, example, "Holiday", "Free", "Blocked" etc. Each of these fields has both a start and end date associated with it. In addition I built a simple calculated field which counts the number of days between start and end date for each activity type. I would like to NOT show the number of days on certain field types, such as "Blocked" and "Holiday". I am having difficulty doing this. Can someone help?

Thanks!

 

[PNGBill]

Hi, I'm sure this is simple, but I am very new to access. I have a query which contains activity types, example, "Holiday", "Free", "Blocked" etc. Each of these fields has both a start and end date associated with it. In addition I built a simple calculated field which counts the number of days between start and end date for each activity type. I would like to NOT show the number of days on certain field types, such as "Blocked" and "Holiday". I am having difficulty doing this. Can someone help?

Thanks!

You could use IIF in your Calculated Field but you may be limited in the values you return. eg, you are working with Dates and I guess you use Date Diff. Then how do you get Null to be a Date value?

Also, consider where you use the result ie A Form. You could have VBA make the control Invisible if some other field was a certain value - again may not be that simple if you use a continuous form.

This response doesn't resolve your question but may assist you in getting a little further along.

 

[vbaInet]

Follow up to PNGBill's advice, you would test that both dates are valid dates, and by this it would automatically take into account Nulls. You do this by using the IsDate() function.

So you test both fields, if they are not valid dates return Null else perform the DateDiff() calculation. My advice would be to have two alias fields, one to perform the above-mentioned DateDiff() calculation and the other to hide the value of the calculation if it's Blocked or holiday.

Some skeleton code:

FirstAlias: IIF(IsDate([Date1]) And IsDate([Date2]), DateDiff(...), Null)

SecondAlias: IIf([ActivityType]="Blocked" Or [ActivityType]="Holiday", Null, [FirstAlias])

 

[akuller]

I can try that...but not too saavy. I was able to Not show the fields if they were Blocked etc..so that eliminated the need to hid the # of days. I just thought there might be an easy way to do that.

Thanks!

 

[vbaInet]

I've written almost all the code for you, all you need to do is to try out the DateDiff() part and substitute field names accordingly. Here's a link on DateDiff()

http://www.techonthenet.com/access/functions/date/datediff.php

DateDiff() is the only way (without code) to get the difference between two dates.

 

[akuller]

Thanks, I totally appreciate it!

 

[vbaInet]

If you still can't figure it out just drop a quick message and we will advise.