IIf statement not working

What exactly are you expecting from you boolean comparison in the IIF statement?

Date() and WeekDayNumber=7

Date() returns the current date.

If you want to return the current weekday, then use the WeekDay function. Note it has two arguments, one is the date you want to check, the other is which day of the week you would consider the first (usually either vbMonday og vbSunday)

I think you miss to compare [BilledDate] with the current date:

IIf([BilledDate]=Date() And [WeekdayNumber]=7, [BilledDate]<=(Date()-26), [BilledDate]<=(Date()-31)

Select allOpen in new window

Thank you. IIf([BilledDate]=Date() And [WeekdayNumber]=7, [BilledDate]<=(Date()-26), [BilledDate]<=(Date()-31) 

Did not worked.

Too bad, but no one knows what "should work" or why not. We can only guess ...

Thanks for trying

If you revealed some info about your business rules, we might even find a solution.

What would you like to know?

To quote Anders:

What exactly are you expecting from you boolean comparison in the IIF statement?

What the last 2 screenshots look like

Yes, but why do they look like that? What are your rule(s) and conditions?

That tells us the end result, but not the logic behind it. The current expression 

Date() and [WeekdayNumber]=7 

doesn't make any sense.

That is why we ask what logic you want to achieve. Then maybe we can write an expression that DOES make sense.

In the last 2 screenshots the condition or criteria is on the "BilledDate" <=<=Date()-26  

SELECT ColdStorageInvoicingQuery.BilledDate, IIf([BilledDate]=Date() And [WeekdayNumber]=7,[BilledDate]<=(Date()-26),[BilledDate]<=(Date()-31)) AS Expr1, Weekday([BilledDate]) AS WeekdayNumber, WeekdayName([WeekdayNumber]) AS WeekdayName, ColdStorage2323.LotNumber, ColdStorageInvoicingQuery.ShipName, ColdStorage2323.ProductDescription, ColdStorage2323.[Renewal Rate], ColdStorage2323.BoxesIn AS PalletsIn, ColdStorageInvoiceQuery2.SumOfPalletsOut, [PalletsIn]-[SumofPalletsOut] AS BillablePallets, [Renewal Rate]*[BillablePallets] AS InvAmt, ColdStorageInvoicingQuery.OID, ColdStorageInvoicingQuery.DateIn, ColdStorage2323.CID, ColdStorage2323.Fresh, ColdStorage2323.Frozen, IIf(Month([BilledDate])=2,DateAdd("d",28,[BilledDate]),DateAdd("d",31,[BilledDate])) AS [Month]

FROM (ColdStorage2323 LEFT JOIN ColdStorageInvoicingQuery ON ColdStorage2323.LotNumber = ColdStorageInvoicingQuery.ColdStorage2323.LotNumber) LEFT JOIN ColdStorageInvoiceQuery2 ON ColdStorage2323.LotNumber = ColdStorageInvoiceQuery2.LotNumber

WHERE (((ColdStorageInvoicingQuery.BilledDate)<=Date()-26) AND ((ColdStorage2323.DateClosed) Is Null))

ORDER BY ColdStorageInvoicingQuery.BilledDate, ColdStorage2323.LotNumber;

and on the other screen shot <=Date()-31

SELECT ColdStorageInvoicingQuery.BilledDate, IIf([BilledDate]=Date() And [WeekdayNumber]=7,[BilledDate]<=(Date()-26),[BilledDate]<=(Date()-31)) AS Expr1, Weekday([BilledDate]) AS WeekdayNumber, WeekdayName([WeekdayNumber]) AS WeekdayName, ColdStorage2323.LotNumber, ColdStorageInvoicingQuery.ShipName, ColdStorage2323.ProductDescription, ColdStorage2323.[Renewal Rate], ColdStorage2323.BoxesIn AS PalletsIn, ColdStorageInvoiceQuery2.SumOfPalletsOut, [PalletsIn]-[SumofPalletsOut] AS BillablePallets, [Renewal Rate]*[BillablePallets] AS InvAmt, ColdStorageInvoicingQuery.OID, ColdStorageInvoicingQuery.DateIn, ColdStorage2323.CID, ColdStorage2323.Fresh, ColdStorage2323.Frozen, IIf(Month([BilledDate])=2,DateAdd("d",28,[BilledDate]),DateAdd("d",31,[BilledDate])) AS [Month]

FROM (ColdStorage2323 LEFT JOIN ColdStorageInvoicingQuery ON ColdStorage2323.LotNumber = ColdStorageInvoicingQuery.ColdStorage2323.LotNumber) LEFT JOIN ColdStorageInvoiceQuery2 ON ColdStorage2323.LotNumber = ColdStorageInvoiceQuery2.LotNumber

WHERE (((ColdStorageInvoicingQuery.BilledDate)<=Date()-31) AND ((ColdStorage2323.DateClosed) Is Null))

ORDER BY ColdStorageInvoicingQuery.BilledDate, ColdStorage2323.LotNumber;

What we need to achieve; we have a business of storage and every 30 days we bill our customers through a "Renewal Form" that tell us which customer's are due hence "BilledDate". Currently we have the "BilledDate" criteria <=Date()-31.

What we need is that if there are customers that their "BilledDate" falls on a Saturday, Sunday or Monday that they show up in the "Renewal Form" on Saturday so we can bill them that Saturday.

That makes sense. Try this:

IIf(Weekday(Date())=7 And [WeekdayNumber]=7, [BilledDate]<=(Date()-26), [BilledDate]<=(Date()-31)

Select allOpen in new window

Thanks.

Did not worked, same result as the first screenshot in datasheet view

But how do you check it? Today is not Saturday.

Because if is not Saturday then is BilledDate<=(Date()-31) see below

But that is how I understand your rule.
If today was Saturday, use Date()-26 and find BilledDates that are Saturday; otherwise use Date()-31.
Or?

Yes you understand what we need but your latest code don't work, in the latest screen shot I had to go in the query and on the criteria field "BilledDate " I had to write <=(Date()-31) to show if it was not Saturday that should be the outcome.

Did you set the criteria for Expr1 to True?

I did not

Set the criteria for Expr1 to true and got this, please see attached snapshot

Then try using the expression for WeekdayNumber:

IIf(Weekday(Date())=7 And Weekday([BilledDate])=7, [BilledDate]<=(Date()-26), [BilledDate]<=(Date()-31)

Select allOpen in new window

Also, set the criteria to a clean True, not "=True"

Thanks.

It works if today is not Saturday, it does not when I set the date to Saturday 8/26, please see below

Results Expr1.docx

Thanks. I appreciate learning something new.

Regardless of whether I should use it or not, it gives me the right results so I can check the Expr1.

I tried Expr1: IIf(Weekday(Date())=7,[BilledDate]<=(Date()-26),[BilledDate]<=(Date()-31))

and still filter Expr1 for True.

And it works!

Thank you for your time and help.

How do I mark as a solution to give you credit? I can not off of this submission

Great!
I'm not sure how to close the question, though. There should be something like "Mark as a solution" to click on my latest comment.

Ok I am going to mark it as a solution in your latest comment. If you have time can you look at a question posed by me "Add records to tables " ?

Thanks.