HELP PLEASE! For anyone looking for a challenge...and most likely a headache

[surreal1780]

Before you read this question, just know that yes, I do realize how cumbersome the function is below and that there are multiple recommendations for function building that have been violated. With that said, here's what I am having difficulty with.

I am trying to construct a Switch function that concerns two fields:

"Allocation" field and a "Pre-assessment Date" field.

I would like for the "Pre-assessment Date" field to display one of several dates based on what is selected in the "Allocation" field...here's where you might want to scream, curse me out, or simply go to the next thread...please don't...(e.g. if "Allocation" displays "Group 1A – Thursdays at 1:30pm-2:45pm from Jan. 17 to Feb. 21 with Marcy" the field should display "01/03/2008" and so forth

This is the function I've constructed, but is not working (i.e., I'm getting an invalid syntax error message pop up)

=Switch(Allocation=“Group 1A – Thursdays at 1:30pm-2:45pm from Jan. 17 to Feb. 21 with Marcy”, “01/03/2008”, Allocation=“Group 2A – Tuesdays at 10:00-11:15am from Jan. 29 to Mar. 4 with Jennifer”, “01/15/2008”, Allocation=“Group 3A – Wednesdays at 7:00pm-8:15pm from Mar. 5 to Apr. 9 with Robin”, “02/20/2008”, Allocation=“Group 4A – Saturdays at 10:00-11:15am from Mar. 29 to May 3 with Robin”, “03/15/2008”, Allocation=“Group 5A – Wednesdays at 10:00-11:15am from Feb. 13 to Mar. 19 with Julie”, “01/30/2008”, Allocation=“Group 6A – Tuesdays at 1:30pm-2:45pm from May 27 to Jul. 1 with Kathy”, “05/13/2008”, Allocation=“Group 7A – Wednesdays at 1:30pm-2:45pm from Aug. 20 to Sep. 24 with Marcy”, “08/04/2008”, Allocation=“Group 8A – Tuesdays at 10:00-11:15am from Aug. 26 to Sep. 30 with Jennifer”, “08/11/2008”, Allocation=“Group 9A – Saturdays at 10:00-11:15am from Sep. 6 to Oct. 11 with Sara”, “08/21/2008”, Allocation=“Group 10A – Mondays at 7:00pm-8:15pm from Sep. 8 to Oct. 13 with Alisa”, “08/25/2008”, Allocation=“Group 1B – Thursdays at 1:30pm-2:45pm from Feb. 28 to Apr. 3 with Kathy”, “01/03/2008”, Allocation=“Group 2B – Tuesdays at 10:00-11:15am from Mar. 29 to Apr. 15 with Jennifer”, “01/15/2008”, Allocation=“Group 3B – Wednesdays at 7:00pm-8:15pm from Apr. 16 to May 21 with Alisa”,“02/20/2008”, Allocation=“Group 4B – Saturdays at 10:00-11:15am from May 10 to Jun. 14 with Marcy”, “03/15/2008”, Allocation=“Group 5B – Wednesdays at 10:00-11:15am from Mar. 26 to Apr. 30 with Julie”, “01/30/2008”, Allocation=“Group 6B – Tuesdays at 1:30pm-2:45pm from Jun. 3 to Jul. 8 with Marcy”, “03/31/2008”, Allocation=“Group 7B – Wednesdays at 1:30pm-2:45pm from Oct. 8 to Nov. 12 with Kathy”, “08/04/2008”, Allocation=“Group 8B – Tuesdays at 10:00-11:15am from Oct. 14 to Nov. 18 with Jennifer”, “08/11/2008”, Allocation=“Group 9B – Saturdays at 10:00-11:15am from Oct. 18 to Nov. 22 with Robin”, “08/21/2008”, Allocation=“Group 10B – Mondays at 7:00pm-8:15pm from Oct. 8 to Nov. 24 with Robin”, “08/25/2008”)

Again, thanks for any help on this terrible constructed function. Any help would be MOST appreciated.

Thanks in advance,
Dan

 

[RuralGuy]

Why not just use a table and DLookup?

 

[Deleted member 30250]

Shouldn't the dates have # rather than " ?

 

[Rabbie]

Take a tip from an old programmer. Start building something simple, test it and when it works add the next bit of complication. Repeat this until everything works.

 

[surreal1780]

Rabbie,

I took your advice and simplified it:

=Switch(Allocation=1A, [01/03/2008], Allocation=2A, [01/15/2008])

However, I've still had no success.

Also, I just wanted to include this message between Robjoy & myself:

Originally Posted by surreal1780
Do you mean this?
Switch(Allocation=“Group 1A – Thursdays at 1:30pm-2:45pm from Jan. 17 to Feb. 21 with Marcy”, #01/03/2008#

as opposed to this:
Switch(Allocation=“Group 1A – Thursdays at 1:30pm-2:45pm from Jan. 17 to Feb. 21 with Marcy”, “01/03/2008”

thanks,
D.
Yes, but I'm not saying that's necessarily the problem - I'm no expert. Why did you send me a private message instead of keeping it public? The threads help many people who never take active part, it's good to share!



Any ideas ladies and gents?

Thanks,
D.

 

[RuralGuy]

How about:
=Switch(Allocation="1A", #01/03/2008#, Allocation="2A", #01/15/2008#)

 

[CraigDolphin]

Why not create a lookup table, and use dlookup to find the relevant dates? Then, if the dates ever change you just change the values in the table without having to re-write your, ah, lengthy switch function.

 

[surreal1780]

I don't have any experience with the Dlookup function, but it sounds like it would greatly simplify this problem. I'll give it a whirl and let you know how it turns out. thanks all for your help.