Can you use iif ( And in access query

[tagteam]

In excel i use a formula like this

=IF(AND(D6<D7,D7<D8),"OK", "Not OK")

But when I try this in access it doesn't work:

JanuaryBegin: IIf(AND([ShopArrival] < #1/1/2022#, [ShopOut] >#1/31/2022#), 31, [ShopArrival])

It says:
The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

Is access not able to the the If(AND thing?

[pbaldy]

The syntax is different, more like:

JanuaryBegin: IIf([ShopArrival] < #1/1/2022# AND [ShopOut] > #1/31/2022#, 31, [ShopArrival])

[tagteam]

Yeah, I got that to work as well. I guess I just need to do some nested iff.

I use this to see if the car is in shop in a particular month:

January: IIf([ShopArrival] Between #1/1/2022# And #1/31/2022#,"In Shop",IIf([ShopArrival]<#1/1/2022# And [ShopOut] Between #1/1/2022# And #1/31/2022#,"In Shop",IIf([ShopArrival]<#1/1/2022# And [ShopOut]>#1/31/2022#,"In Shop",IIf([ShopArrival]<=#1/31/2022# And [ShopOut] Is Null,"In Shop","Not In Shop"))))

That may be a bit heavy handed, not sure if there is a better way.

My next step is to try to figure out how many days it was actually in shop. That is what I am trying to accomplish here. Is this nested iif the best way?

[pbaldy]

Yuck. What's the goal? And what's your table structure? I have a table of vehicles and a table of repair orders. This will tell me every vehicle that was in the shop at some point during June:

SELECT tblRONumbers.car_no, tblRONumbers.OpenDateTime, tblRONumbers.CloseDateTime
FROM tblRONumbers
WHERE (((tblRONumbers.OpenDateTime)<=#6/30/2023#) AND ((tblRONumbers.CloseDateTime)>=#6/1/2023#));

In real life you wouldn't hard-code dates like that, I'd enter them on a form and get them from there.

To get the number of days in the shop you should be able to use DateDiff() on your 2 date fields, perhaps using the Nz() function to use today's date when the end date is null.

[tagteam]

My goal is to look at they year and see how many cars were in a shop each month. That is why I need the nested iif. A car can go in shop and be there several months and may not have an out date. That is why I don't think that the above query would work because I need to catch all the scenarios.

1. It went in shop and out of shop in the same month
2. It went in shop but is still in shop that month
3. It went in shop in a previous month and is still in shop
4. It went in shop in a previous month and out this month
5. It went in shop this month or a previous month and still does not have a shop out date.

So far it looks like my nested iif catches all the scenarios, even though it is a bit yuck.

Even more crazy, I almost have the IIF working for the count as well with this.

JanuaryDays: IIf([ShopArrival]=[ShopOut],1,IIf([ShopArrival]>#1/31/2022#,0,IIf([ShopArrival]>=#1/1/2022# And [ShopOut]<=#1/31/2022#,[ShopArrival]-[ShopOut],IIf([ShopArrival] Between #1/1/2022# And #1/31/2022# And [ShopOut]>#1/31/2022# Or [ShopOut] Is Null,#1/31/2022#-[ShopArrival],IIf([ShopArrival]<#1/1/2022# And [ShopOut]>#1/31/2022# Or [ShopOut] Is Null,31,IIf([ShopArrival]<#1/1/2022# And [ShopOut] Between #1/1/2022# And #1/31/2022#,#1/1/2022#-[ShopOut],0))))))

Still tweaking a bit because one of them is giving me a negative number so i have to switch the order somewhere.

[pbaldy]

Try this for chuckles:

JanuaryDays: IIf([ShopArrival]<=#1/31/2022# AND Nz([ShopOut], Date())>=#1/1/2022#, "InShop", "NotInShop")

[pbaldy]

For days perhaps:

JanuaryDays: IIf([ShopArrival]<=#1/31/2022# AND Nz([ShopOut], Date())>=#1/1/2022#, Nz([ShopOut], Date()) - [ShopArrival], 0)

[tagteam]

Yeah, I got the days all working with:

JanuaryDays: IIf([ShopArrival] Between #1/1/2022# And #1/31/2022# And [ShopArrival]=[ShopOut],1,IIf([ShopArrival]>#1/31/2022#,0,IIf([ShopArrival]>=#1/1/2022# And [ShopOut]<=#1/31/2022#,[ShopOut]-[ShopArrival],IIf([ShopArrival] Between #1/1/2022# And #1/31/2022# And [ShopOut]>#1/31/2022# Or [ShopArrival] Between #1/1/2022# And #1/31/2022# And [ShopOut] Is Null,#1/31/2022#-[ShopArrival],IIf([ShopArrival]<#1/1/2022# And [ShopOut]>#1/31/2022# Or [ShopOut] Is Null,31,IIf([ShopArrival]<#1/1/2022# And [ShopOut] Between #1/1/2022# And #1/31/2022#,[ShopOut]-#1/1/2022#,0))))))

[pbaldy]

What did post 7's SQL produce in comparison?

[tagteam]

Post # 7 works to get the correct number of total days in the shop if the car goes into the shop in January. But it doesn't show how many days each month it is in.

For example: ShopArrival = 1/2/22 and ShopOut = 6/17/22
Post 7 will show the correct amount of 166 days in shop in total, but it doesn't show the 31 for January and just 28 for Feb, and just 31 for Mar etc

If I change the formula for February it still gives me the 166 total

I changed it for February to be the below and it still shows the 166. So basically it works but only for the total for cars that went in the month the formula states.

FebruaryDays: IIf([ShopArrival]<=#2/28/2022# And Nz([ShopOut],Date())>=#2/1/2022#,Nz([ShopOut],Date())-[ShopArrival],0)

Can you please explain this part of the iif statement
Nz([ShopOut],Date())>=#2/1/2022#

I get the first part IIF ([ShopArrival <=#1/1/22# AND

But I am not sure why there is a comma between ShopOut and Date. I know that the NZ takes the form NZ(variant, value_if_null) but I don't understand what is happening with the ShopOut, Date part.

Thanks

[pbaldy]

The Nz() formula basically says "If ShopOut is Null, use today's date instead".