Date Range Query and Null Records and Formula Help Please (1 Viewer)

[Lilly420]

Hello,

I have two questions, both the same query…

I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field “CO_resp_rcvd” and those that didn’t respond yet but need to -- is that possible to do both and if so how would you show me how?

This is what I have currently in my query …

CO_resp_rcvd (date field)

Criteria: Between [Start Date] And [End Date]

(I need null values as well because there will be some if the CO has not responded yet but needs to)


Formula:

This formula gives me the number of bus days from the Review Date – CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate “Review Date” – “Today’s date” to show the number of days outstanding for those that have not responded yet in the same formula?

Not sure how to combine it to work…the wrapper is a bus day function…

This is what I have so far in the query…

CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())

I thank you for any help you can provide…and I hope this is not too confusing.


Lilly

 

[Big Pat]

Just use ... Between [Start Date] And [End Date] Or Is Null ...in your criteria. I think that should do it.

For the 2nd part you need an IIF function and I assume you need that same "Wrapper" thing in both parts:

IIF([CO_resp_recd] is NOT Null,Wrapper([Review Date],[CO_resp_recd]),Wrapper([Review Date],Date()))

 

[Lilly420]

Thank you so much for the help...this works great except I left one piece out and not sure how to figure it into the below formula and I apologize for not thinking of it prior ...

In the query, there are two fields one is CO_resp_req = Yes (meaning the CO needs to respond) and then CO_resp_recd (which is the date the CO responded)...so I only need the calcuation if they are required to respond (CO_resp_req = Yes) ... so if the CO is not required to respond it would = NO, and then I don't want a calculation just the field to be blank so it does not look as though the CO is are overdue...does this make sense? The formula worked great, except on the "no" -- I am sorry for not including that piece...I did try and it didn't work...

Thank you again, you are wonderful to help me...

Lilly
IIF([CO_resp_recd] is NOT Null,Wrapper([Review Date],[CO_resp_recd]),Wrapper([Review Date],Date())) Yesterday 01:49 PM

 

[Big Pat]

You need another IIF. You can nest IIFs either by replacing an existing element with another IIF, or by wrapping another IIF round the outside. The crucial thing is that the syntax of each IIF must be preserved i.e. IIF(condition,true_part,false_part) It's easy to overlook a bracket or comma or to get them the wrong way round and to avoid this I have got into the habit of writing IIF(c,t,f) and building it up piece by piece.

The following example shows how I would use nested IIFs to colour-code a field called "Score".

First I write IIF(c,t,f) The prevents me from forgetting commas and brackets.
Then I go back and put in field names and outputs e.g. IIF([Score]<80,"Red","Not red") Scores of 80 or above need further evaluation so all I can say at this stage is that they are "not red". If it was a complex IIF I would run the query now to make sure it worked so far.

Then I change "not red" to another IIF so I have IIF([Score]<80,"Red",IIF(c,t,f)) again I am using this trick to make sure I get the brackets and commas and then I change the new parts so now I have
IIF([Score]<80,"Red",IIF([Score]<100,"Orange","Green"))

But suppose there are some scores which do not need to be rated. This is like your example. Then I would wrap another IIF around the function like this.
IIF(c,IIF([Score]<80,"Red",IIF([Score]<100,"Orange","Green")),f)

The existing part of the function is what you will do only if the new condition is true, so you don't need a "t" on this occasion.

The I go back and add in that new condition and the required output if it's false. Now I have
IIF([RatingRequired]="Yes",IIF([Score]<80,"Red",IIF([Score]<100,"Orange","Green"),NULL) If I was using this output in a report, I might use "No rating required" rather then NULL.

So to get back to your example, just wrap another IIF round the outside if your existing function, in the format shown above, using your own field names and conditions.

Let me know how you get on, but I probably won't be back online for a couple of days.

 

[Lilly420]

Hello Big Pat,

First, let me say THANK YOU so much you for taking the time to explain the IIFs to me and it makes so much more sense now. I read through your examples and then tried to apply what you outlined to my query…and this is what I had…

CO-Bus Days to Respond or Outstanding: IIf([CO_resp_req]=”Yes”,IIf([CO_resp_recd] Is Not Null,Wrapper([Review Date],[CO_resp_recd]),Wrapper([Review Date],Date())),Null)

I ran it and then received an error message that said “Expression is type incorrectly or too complicated to run…” Well, I looked and looked at it and thought it all looked correct and then decided to remove the quotes around the “Yes” so it looks like this now…

CO-Bus Days to Respond or Outstanding: IIf([CO_resp_req]=Yes,IIf([CO_resp_recd] Is Not Null,Wrapper([Review Date],[CO_resp_recd]),Wrapper([Review Date],Date())),Null)

And it ran like a dream…it worked perfect.

Would you explain to me (only if you have time) why you would use quotes in some cases—is it to assign text to something like in your example “Red”, “Orange”, etc. whereas I am putting in the value of the record-Yes – not sure I am asking this correctly…so please forgive me?

Again, a huge thank you to you, you have been so wonderful and a great teacher…and I so appreciate you taking the time to help me.

Lilly

 

[Big Pat]

Hi Lilly,

It's a pleasure! I have learned almost everything I know about Access (which still isn't a lot!!) from this forum. So occasionally I find a question that is is not above my skill level and it feels good to help.

Now as to why your query worked with Yes and not with "Yes", well I can take a guess but I could be wrong. In your underlying table, is [CO_resp_req] a Yes/No tickbox? I had assumed it was a combo (dropdown) with the options "Yes" and "No", which would be stored as text.

I always thought that Yes/No boxes stored the values True/False which can also be represented in queries as -1/0. But perhaps Yes is a logistical value in its own right.

So if you have a tickbox AND if Yes is a logical value in Access, then I suppose it must work without the quotes. If it had been stored as text, then I'm sure you WOULD need the quotes.

Maybe one of the other gurus on this forum can confirm this, or explain it to me!!

Glad you got it working.

 

[Lilly420]

Hello Big Pat,

Yes it is a Yes/No check box...so that explains it and thank you again for taking the time to help me...you have been so wonderful...

Have a great day.

Lilly