I like the reminder to remember the BETWEEN function.  You are teaching me what I have learned (outside basic in Jr High 20 yrs ago) about computer programming.

It could be pointed out that the BETWEEN functions like [>=1500 and <=3000], not [>=1500 and <3000].

I love your lectures!  Do you teach online university courses, or is this great and cheap tuition?

Access 2010 Beginner 5 Lesson 1 16:00/16:30

---

Reply from Richard Rost:

Thank you very much for your compliments.

Yes, you are correct that with NUMBER VALUES the statement:

BETWEEN 1500 AND 3000

is the same as

>=1500 AND <=3000

However, when you're dealing with Date/Time values you want to be careful, because:

BETWEEN #1/1/2000# AND #2/1/2000#

is the same as

>=#1/1/2000# AND <=#2/1/2000#

The problem is that #2/1/2000 5:00# is NOT included.

This becomes an issue when people use the =NOW() function to store values that are accurate to the SECOND in their date fields. Let's say you're working with invoices. You've got the default value of the DateTime field set to =NOW() because you might want to track your busy times of the day for taking orders (a reasonable request). Now when it comes time for your month end reporting, you specify a criteria of:

BETWEEN #1/1/2000# AND #1/31/2000#

Guess what happens... you now are missing all of the orders that occurred AFTER midnight on 1/31. And if you use:

BETWEEN #1/1/2000# AND #2/1/2000#

Now you might accidentally get any orders from 2/1 that came in at EXACTLY midnight (which could happen if a user types in a manual date of 2/1 and it defaults to midnight).

So... that's why I wanted to remind people that FOR PRACTICAL PURPOSES when dealing with DATES you want to say:

>=#1/1/2000# AND <#2/1/2000#

Thank you for bringing this up so I could clarify it. That's a VERY good point to bring up!

And, no, I've never taught at the university level. I attended college myself for a few years (University at Buffalo) back in the early 90s (yea, I'm old) but I was bored with the material they were teaching so I dropped out and started my own business.