Multiple Switches

[jennuhw]

I have a query that needs to pull values from different fields depending on the day of the week. I have tried this, but it just gives me a -1 in the query as the answer. I am sure that I am not doing it right, but can someone show me the correct way or a better one? Thanks!

Exp: Switch(([Weekday]="Monday&quot,(TimeValue(Left([Schedules]![Monday],5))),([Weekday]="Tuesday&quot,(TimeValue(Left([Schedules]![Tuesday],5)))) Or Switch(([Weekday]="Wednesday&quot,(TimeValue(Left([Schedules]![Wednesday],5))),([Weekday]="Thursday&quot,(TimeValue(Left([Schedules]![Thursday],5)))) Or Switch(([Weekday]="Friday&quot,(TimeValue(Left([Schedules]![Friday],5))),([Weekday]="Saturday&quot,(TimeValue(Left([Schedules]![Saturday],5)))) Or Switch(([Weekday]="Sunday&quot,(TimeValue(Left([Schedules]![Sunday],5))))

 

[JeremyNYC]

I would say there's some table restructuring to be done. Instead of having seven fields for the days of the week, make a new table with seven rows. That way you'll be able to include that table in your query, joined to a field that shows the weekday. This will be much simpler to deal with, and will make your data more normalized.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[jennuhw]

I am not sure if I would be able to do that. The table that has the 7 days of the weeks as fields will be holding schedules for about 70 employees. This was the only way that I could figure out doing it. Any ideas on how I could make this work?

 

[RobbieMac]

Try this : You can't use OR operators inside a switch command!!
SQLstr = Switch([WeekDay] = "Monday", TimeValue(Left([Schedules]![Monday], 5)), [WeekDay] = "Tuesday", TimeValue(Left([Schedules]![Tuesday], 5)), [WeekDay] = "Wednesday", TimeValue(Left([Schedules]![Wednesday], 5)), [WeekDay] = "Thursday", TimeValue(Left([Schedules]![Thursday], 5)) _
, [WeekDay] = "Friday", TimeValue(Left([Schedules]![Friday], 5)), [WeekDay] = "Saturday", TimeValue(Left([Schedules]![Saturday], 5)) _
, [WeekDay] = "Sunday", TimeValue(Left([Schedules]![Sunday], 5)))

 

[jennuhw]

Yeah, I finally figured that out. Thanks though! I am still having a problem though if there isn't a scheduled time for every day in the Schedules table it gives me a #error. Is there a way to get around that? I have tried Iif(isempty... , but I am not using the correct syntax or function. Thanks!

 

[RobbieMac]

use
iif([timevalue] is null,nullresult,something )

Kind regards

 

[jennuhw]

Sorry, it has taken me so long to reply. Here is what I have. Sometimes the TimeValue may be blank because someone isn't scheduled to work that day. If there is a blank one, I get all #Error for the result. Basically what I want it to do is say 'Not Scheduled' if the time is blank on the Schedule table. Thanks. This is kinda long, but here it is!

Schedule: IIf(IsNull(Switch(([Weekday]="Monday&quot,(TimeValue(Left([Schedules]![Monday],5))),([Weekday]="Tuesday&quot,(TimeValue(Left([Schedules]![Tuesday],5))),([Weekday]="Wednesday&quot,(TimeValue(Left([Schedules]![Wednesday],5))),([Weekday]="Thursday&quot,(TimeValue(Left([Schedules]![Thursday],5))),([Weekday]="Friday&quot,(TimeValue(Left([Schedules]![Friday],5))),([Weekday]="Saturday&quot,(TimeValue(Left([Schedules]![Saturday],5))),([Weekday]="Sunday&quot,(TimeValue(Left([Schedules]![Sunday],5))))),
"Not Scheduled",
(Switch(([Weekday]="Monday&quot,(TimeValue(Left([Schedules]![Monday],5))),([Weekday]="Tuesday&quot,(TimeValue(Left([Schedules]![Tuesday],5))),([Weekday]="Wednesday&quot,(TimeValue(Left([Schedules]![Wednesday],5))),([Weekday]="Thursday&quot,(TimeValue(Left([Schedules]![Thursday],5))),([Weekday]="Friday&quot,(TimeValue(Left([Schedules]![Friday],5))),([Weekday]="Saturday&quot,(TimeValue(Left([Schedules]![Saturday],5))),([Weekday]="Sunday&quot,(TimeValue(Left([Schedules]![Sunday],5))))))