difficulty using "Or" in Dlookup

[Venus]

I am creating a report that I want to design as a timetable to show which courses are offered in a particular classroom each day of the week. I want to have a number of textboxes in the report that represent the different time slots in the week. For the control source for these textboxes, I will use dlookup that pulls the course names from my courses query like this:

=DLookUp("Course_number","Qry_Courses","timeslot = 'MW 8:30 - 10:20' And room= '0AN70' and term= 'FC'")

So far this has been working, but I would like to add an "or" to the timeslot option because some courses have a timeslot of 'MW 8:30 - 10:20' (meaning both Monday and Wednesday) and some are M 8:30 - 10:20' (Monday only).
So for my Monday 8:30 – 10:20 textbox, I need it to search for a course that matches either timeslot 'M 8:30 - 10:20' or timeslot 'MW 8:30 - 10:20'. When I try to add the "or" into the formula, it doesn’t seem to be working. I’ve tried it both of these ways:

=DLookUp("Course_number","Qry_Courses","timeslot = 'W 8:30 - 10:20' or ‘MW 8:30 – 10:20’ And room= '0AN70' and term= 'FC'")

=DLookUp("Course_number","Qry_Courses","timeslot = 'W 8:30 - 10:20' or timeslot= ‘MW 8:30 – 10:20’ And room= '0AN70' and term= 'FC'")

 

[plog]

You have unkosher logic. You have to be real careful and explicit when you mix ANDs and ORs. When you have both you need to use parenthesis to group the ones that go together, together.

Let's strip out the specifics of your code and reduce your Dlookup logic. It will become this:

C1 OR C2 AND C3 AND C4

I honestly don't know how that get's evaluated. It could be like this:

(C1 OR C2) AND C3 AND C4

Or it could be evaluated like this:

C1 OR (C2 AND C3 AND C4)

Using the first method C4 must be true for the whole thing to resolve to true. Using the second method, C4 could be false, but if C1 is true it resolves to true.

You need to put parenthesis in your logic so the system can use the correct method. I suspect it should look like the first method.

 

[Venus]

Yes, the first one is correct - it should be (C1 OR C2) AND C3 AND C4

I tried the following and I am still getting an error:

=DLookUp("Course_number","Qry_Courses","(timeslot = 'W 8:30 - 10:20' or ‘MW 8:30 – 10:20’) And room= '0AN70' and term= 'FC'")

 

[Galaxiom]

I honestly don't know how that get's evaluated.

"And" has precedence over "Or".

 

[stopher]

Yes, the first one is correct - it should be (C1 OR C2) AND C3 AND C4

I tried the following and I am still getting an error:

=DLookUp("Course_number","Qry_Courses","(timeslot = 'W 8:30 - 10:20' or ‘MW 8:30 – 10:20’) And room= '0AN70' and term= 'FC'")

Needs to be

=DLookUp("Course_number","Qry_Courses","(timeslot = 'W 8:30 - 10:20' or timeslot = ‘MW 8:30 – 10:20’) And room= '0AN70' and term= 'FC'")

 

[Venus]

Needs to be

=DLookUp("Course_number","Qry_Courses","(timeslot = 'W 8:30 - 10:20' or timeslot = ‘MW 8:30 – 10:20’) And room= '0AN70' and term= 'FC'")

I tried this and it is still coming up with an error

 

[stopher]

timeslot = 'W 8:30 - 10:20' or timeslot = ‘MW 8:30 – 10:20’

your quotes look different. The quotes on the left look fine. The quotes on the right don't. Take a close look, they are different.

What's the error message anyway?

 

[Venus]

That's strange, I don't know why the quote marks look different. I've been copying and pasting back and forth between access and word to track the different changes, so perhaps it has something to do with that.

The error I am receiving is when I view the report, the textbox says "#Error"

 

[Venus]

I just went in retyped each of the quotation marks and it worked. Thanks!

 

[stopher]

So have you changed the incorrect (curly) quotes to the straight ones?

 

[stopher]

I just went in retyped each of the quotation marks and it worked. Thanks!

That's good to hear.

 

[GohDiamond]

Your single QUOTE has to be a STRAIGHT Quote/Apostrophe. Copy one of the straight quotes from the left side and replace each quote on the Right side with a copy of it.
Chr(39) = Single straight quote mark/apostrophe (')

Best to edit it in notepad which used STRAIGHT Quotes exclusively.

Cheers!
Goh