An IIF statement with a Date-1

[GrandMasterTuck]

[SOLVED] An IIF statement with a Date-1

Hi everybody. I have a query that fetches the value of the EditFormWeekday field on the frmEditForm form (LOL... that sounds hilarious when I say it in my head... must be the drugs...)

Anyway, this query is supposed to filter the list of names based upon the values in two fields on the form: EditFormWeekday and EditFormShift. If the value of the EditFormShift is NOT "MIDS", it is supposed to filter out all records that match the EditFormWeekday. If the EditFormShift IS "MIDS", it's supposed to filter out all records that have an EditFormWeekday value that is the day BEFORE the EditFormWeekday shown. And I can't figure out how to word the syntax in the Query Builder.

Here's what I have in the Criteria box for the Weekday in the query:

IIF([Forms]![frmEditForm]![EditFormShift]="MIDS",
Not Like [Forms]![frmEditForm]![EditFormWeekday]-1,
Not Like [Forms]![frmEditForm]![EditFormWeekday])

Can anybody tell me what I'm doing wrong here? I also tried putting the NOT LIKE before the IIF, but that gave me an error, too.

 

[spikepl]

Slow down. When concocting something complex, like your IIF, make sure that each ingredient works before consuming this entire witch's potion.

When you have

IIF (someClause, ExprA, ExprB)

then check in the QueryDesigner that ExprA by itself does what is expected. Likewise for ExprB.

 

[Brianwarnock]

Why are you using Like when no wild cards are involved?

This old guy is a little unsure of what "filter out" means , I assume it means Select so by your narrative I would code
IIF([Forms]![frmEditForm]![EditFormShift]="MIDS",
[Forms]![frmEditForm]![EditFormWeekday]-1,
[Forms]![frmEditForm]![EditFormWeekday])

if I have got that wrong either switch the clauses round or change ="MIDS" to <>"MIDS"

Brian

 

[GrandMasterTuck]

Brian,

Well, I was trying to make it easier to understand. In actuality, there IS a wild card (there are TWO), because the employee's days off are written with two days, separated by a comma. The query is looking for the presence of the value in question contained somewhere within that field. So if the employee's days off are Thursday, Friday, and the query is filtering out "*" & "Thursday" & "*", that employee gets kicked off the results list. But if the employee's days off are "Monday, Tuesday" and the query is still looking for "*" & "Thursday" & "*", the employee is not filtered out of the record list, and appears.

I removed that part of the code so that it was easier to read. The ACTUAL code I wrote is as follows:

IIF([Forms]![frmEditForm]![EditFormShift]="MIDS",
Not Like "*" & [Forms]![frmEditForm]![EditFormWeekday]-1 & "*",
Not Like "*" & [Forms]![frmEditForm]![EditFormWeekday] & "*")

I think the EditFormWeekday-1 part is what's griefing me. Any idea of another way to get that result? Ideas?

 

[Brianwarnock]

If I understand correctly the form field editor weekday contains text for the day of the week, if so what do you expect -1 to do.?

You do not say what is happening.

I think that you are going to need to write your own function to change the day.

Brian

 

[GrandMasterTuck]

The EditFormWeekday field is just a copy of a date field, formatted to show the weekday name instead of a numeric date. I have an EditFormDate field which contains the actual date (like 5/9/14) and the EditFormWeekday field fetches that and displays it formatted as "Friday"

What I want the query statement to do is fetch a list of all employees from the employee table, and eliminate the ones that have TODAY off (the ones that, in today's example, have "Friday and Saturday" or "Thursday and Friday" in their DaysOff field. BUT... if the employee is a MIDNIGHT SHIFT worker, I want it to give me a list of everybody, but eliminate people who had YESTERDAY off... or the day PRIOR TO the scheduled date off. That's what the EditFormWeekday-1 is for.

I can get the statement to give me the correct results until I put in that "-1", and then my query doesn't return anything. That's what's got me confused.

 

[Brianwarnock]

I no longer have Access to play with this but it seems to me that you want

Format([mydate]-1,"dddd")
And what you have is

Format([mydate,"dddd")-1

Brian

 

[GrandMasterTuck]

@Brianwarnock: That was it! Thanks. For anyone that wants a more detailed explanation of my problem and the solution, please feel free to read below. You da man!

I have a field called EditFormDate that shows the date a schedule was created for. Schedules are created several days in advance, so there are lots of dates in there that are both past and future dates. I have a second field, this one only on the form, called EditFormWeekday, and the control source for that form is Format([EditFormDate], "dddd"). What I was doing was putting the EditFormWeekday in the expression, and what I should have been doing was putting the EditFormDate in there with a Format() wrapped around it.

The system doesn't understand "Friday minus one", but it DOES understand "Show me May 9th, 2014, subtract one from that, and format the result as a weekday name"

This is the formula that finally worked:

IIF([Forms]![frmEditForm]![EditFormShift]="MIDS",
Not Like "*" & Format([Forms]![frmEditForm]![EditFormDate]-1, "dddd") & "*",
Not Like "*" & [Forms]![frmEditForm]![EditFormWeekday] & "*")

Thanks again, guys!

 

[GrandMasterTuck]

ARRGH!!

Okay, I'm getting frustrated...

The fix to the EditFormDate by formatting works great. But as soon as I enter the IIF statement in my query, the query returns nothing. If I set the Criteria of the query's DaysOff column to this:

Not Like "*" & [Forms]![frmEditForm]![EditFormWeekday] & "*"

it works great. But when I get crazy and set the query's DaysOff column to this:

IIF([Forms]![frmEditForm]![EditFormShift]="MIDS",
Not Like "*" & Format([Forms]![frmEditForm]![EditFormDate]-1, "dddd") & "*",
Not Like "*" & [Forms]![frmEditForm]![EditFormWeekday] & "*")

the query suddenly starts returning nothing. Blank. Nada.

It worked great in the sample database I put together when I posted the original question, but as soon as I went to my own database and copied the code, el zippo.

What could be causing this?? This is driving me CRAZY!

I just want the query to take the list of people scheduled to work this shift and REMOVE the people who's days off match the EditFormWeekday, unless the EditFormShift is MIDS, in which case I want the people who are scheduled to work YESTERDAY removed.

ARRGH! (again)

EDIT: Do I have the placement of the IIF statement wrong? I just started wondering if I have to nest the IIF in some other manner... I'ma give it a few more tries and I'll be back to update anyone that still cares..

EDIT AGAIN: SUCCESS! As it turns out, the way the IIF was nested in there was causing an issue. You can't put the NOT LIKE statement more than once in the Criteria line. So I had to rewrite the Criteria to read as follows:

Not Like "*" &
IIf([Forms]![frmEditForm]![EditFormShift]="MIDS",
Format([Forms]![frmEditForm]![ScheduleDate]-1, "dddd"),
[Forms]![frmEditForm]![EditFormWeekday]) & "*"

That will teach me to claim a problem was solved before I actually implemented the fix into my system. NOTE TO OTHERS: make sure these things you're told to do actually work in your database and NOT just in a temporary database you created. I figure that might be common sense to anyone that uses this stuff regularly, but I'm still a stupid noob.

Anyway, thanks to all that stepped in to exchange ideas with me. It's you guys that make it possible for me to keep at this thing! The UK Access Programmers forum is THE BEST. Thanks again!

 

[Brianwarnock]

Thanks for posting your solution, I'm sure it will help others

Brian