IIF Query too long!

[KEKeogh]

Ok I have an IIF statement in the Field cell in the Query wizard in Access 2000 which works perfectly if I shorten it but if I put in all the criteria it's too long.

What I'm wanting to do is this essentially

If [MON P/U] is not null AND [WEEKDAY]=2 AND [TIME]=AM
Then the field will =AM
If false the field is null

If [MON D/O] is not null AND [WEEKDAY]=2 AND [TIME]=PM
Then the field will =PM
If false the field is null

This I need done for the rest of the weekday's as well.


This is the expression I'm using.

IIf([MON P/U] Is Not Null and [WEEKDAY]=2 and [TIME]="AM","AM", IIf([MON D/O] Is Not Null and [WEEKDAY]=2 and [TIME]="PM","PM", IIf([TUE P/U] Is Not Null and [WEEKDAY]=3 and [TIME]="AM","AM", IIf([TUE D/O] Is Not Null and [WEEKDAY]=3 and [TIME]="PM","PM", IIf([WED P/U] Is Not Null and [WEEKDAY]=4 and [TIME]="AM","AM", IIf([WED D/O] Is Not Null and [WEEKDAY]=4 and [TIME]="PM","PM", IIf([THUR P/U] Is Not Null and [WEEKDAY]=5 and [TIME]="AM","AM", IIf([THUR D/O] Is Not Null and [WEEKDAY]=5 and [TIME]="PM","PM", IIf([FRI P/U] Is Not Null and [WEEKDAY]=6 and [TIME]="AM","AM", IIf([FRI D/O] Is Not Null and [WEEKDAY]=6 and [TIME]="PM","PM",Null))))))))))

I'm not familiar enough with SQL to do it there and I can't seem to find a way to get to a Visual Basic Code Builder from the Query Builder.

Any help is appreciated.

Thanks
Kathie

 

[MarkK]

What kind of data goes in a field called [MON P/U]? To me it looks like you have some problems with how this data is structured, and that might be why you need such a huge expression to evaluate it.
Monday, for instance, is data. So is P/U. Those are variable values, like it could be monday or it could be wednesday, and it could be drop-off or pick-up. So then you want field names like TheDay, and ActivityType.
Do pick-ups always happen in the AM? If so do you need to store both pieces of data?
Hope this helps,
Mark

 

[KEKeogh]

[MON P/U] is a time (the pick up time for a student in the AM)
[MON D/O) is a time (the drop off time for a student in the PM)

[WEEKDAY] the the numerical equivalent of the weekday (eg Monday=2 & Tuesday = 3)

[TIME] is simply AM or PM

I have a list of dates the kids can go but need to have a line for each AM and PM trip based on their scheduled pick up times

For instance: This student should look like this

LASTMON P/UMON D/OTUE P/UTUE D/OWED P/UWED D/OTHUR P/UTHUR D/OFRI P/UFRI D/ODateTimeWeekdayStudent1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/1/11 2Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/1/11 2Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/2/11AM3Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/2/11PM3Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/3/11AM4Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/3/11PM4Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/4/11AM5Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/4/11PM5Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/5/11 6Student1 7:30 AM4:00 PM7:30 AM4:00 PM7:30 AM4:00 PM 8/5/11 6

Thanks for your quick response. This is just the first part of a report that has been bugging me all week.

Kathie

 

[KEKeogh]

Didn't really that table wouldn't come in like I wanted it to.

Attached is a screen capture of what I should geKathie

 

[MarkK]

Yeah, that table, that's not really how a database works. That's how Excel works. A database is quite different. Are you sure you need a database?
Like, if you just need to put together a grid like that I'd use Excel.
Lemme know
Mark

 

[KEKeogh]

Yep! Considering all the pick up and drop off times are in Access already.

I'm sure I'll figure something out. I know it can do it cause it's fine when I put the IIF statements in for Monday through Wednesday. Gives me just what I need but it's too long when I get the Thursday and Friday in there.

Problem is some kids go in the mornings but not in the afternoons so I kind of has to be for each column.

Kathie

 

[MarkK]

K. Cheers,
Mark

 

[KEKeogh]

Does anyone think I could do these IIF statements in a Public Function Module to evualate the corresponding fields in my Query?

IIf([MON P/U] Is Not Null and [WEEKDAY]=2 and [TIME]="AM","AM",Null)
IIf([TUE P/U] Is Not Null and [WEEKDAY]=3 and [TIME]="AM","AM",Null)
IIf([WED P/U] Is Not Null and [WEEKDAY]=4 and [TIME]="AM","AM",Null)
IIf([THUR P/U] Is Not Null and [WEEKDAY]=5 and [TIME]="AM","AM",Null)
IIf([FRI P/U] Is Not Null and [WEEKDAY]=6 and [TIME]="AM","AM",Null)
IIf([MON D/O] Is Not Null and [WEEKDAY]=2 and [TIME]="PM","PM",Null)
IIf([TUE D/O] Is Not Null and [WEEKDAY]=3 and [TIME]="PM","PM",Null)
IIf([WED D/O] Is Not Null and [WEEKDAY]=4 and [TIME]="PM","PM",Null)
IIf([THUR D/O] Is Not Null and [WEEKDAY]=5 and [TIME]="PM","PM",Null)
IIf([FRI D/O] Is Not Null and [WEEKDAY]=6 and [TIME]="PM","PM",Null)

If so, what would the could syntax be?

Kathie