Between And Operator

[lotus18]

Hello World!

I know that The BETWEEN ... AND operator selects a range of data between two values and I found article about this operator that it is treated differently in different databases. Some scenarios

1. With some databases a person with the LastName of "Hansen" or "Pettersen" will not be listed (BETWEEN..AND only selects fields that are between and excluding the test values).
2. With some databases a person with the last name of "Hansen" or "Pettersen" will be listed (BETWEEN..AND selects fields that are between and including the test values).
3. With other databases a person with the last name of "Hansen" will be listed, but "Pettersen" will not be listed (BETWEEN..AND selects fields between the test values, including the first test value and excluding the last test value).

From http://www.w3schools.com/sql/sql_between.asp

At the above scenarios, what scenario that access 2003 belongs? I need an urgent reply. Thanks

[Stewart Ross]

Hello World!

I know that The BETWEEN ... AND operator selects a range of data between two values and I found article about this operator that it is treated differently in different databases. Some scenarios

1. With some databases a person with the LastName of "Hansen" or "Pettersen" will not be listed (BETWEEN..AND only selects fields that are between and excluding the test values).
2. With some databases a person with the last name of "Hansen" or "Pettersen" will be listed (BETWEEN..AND selects fields that are between and including the test values).
3. With other databases a person with the last name of "Hansen" will be listed, but "Pettersen" will not be listed (BETWEEN..AND selects fields between the test values, including the first test value and excluding the last test value).

From http://www.w3schools.com/sql/sql_between.asp

At the above scenarios, what scenario that access 2003 belongs? I need an urgent reply. Thanks

Hi. In Access 2003 the Between operator behaves as in scenario 2. It is equivalent to

([fieldname] >= [firstvalue]) AND ([fieldname] <= [lastvalue])

-Stewart

[lotus18]

Hi. In Access 2003 the Between operator behaves as in scenario 2. It is equivalent to

([fieldname] >= [firstvalue]) AND ([fieldname] <= [lastvalue])

-Stewart

Thaks for your reply. Actually I'm plannung to create a scheduler for other classes. And I want to apply this. Can you help me an algorithm to detect conflicts?

Rey Sean

[NeoPa]

Rey Sean,

We can help with specific questions. It is your job to produce your project. If you need help during that process, then please post your specific questions.

[lotus18]

Sorry for that admin,

I came up with this algo to detect conflict schedules

• If Existing Time Started >= New Time Started AND Existing Time Started < New Time Finished
• Existing Time Finished > New Time Started AND Existing Time Finished < = New Time Finished
• Existing Time Started = New Time Started
• New Time Started > = Existing Time Started AND New Time Started < Existing Time Finished

Did I miss anything? But anyway thanks for the concern.

[NeoPa]

Schedule conflict can be broken down more simply than that (See related thread How to prevent overlapping schedules?).
The logic for checking schedule A does not overlap schedule B is :
A is completed before B starts
OR
A starts after B completes

Breaking this down we have :
End(A) < Start(B)
OR
Start(A) > End(B)

An overlap is the reverse so :
End(A) >= Start(B)
AND
Start(A) <= End(B)

[lotus18]

You have a simple algo than me ^ _ ^
Thanks again! NeoPa

[NeoPa]

I've had to deal with it a few times before ;)

(oh, and I'm clever too :-> - lol)

[lotus18]

I've had to deal with it a few times before ;)

(oh, and I'm clever too :-> - lol)

Oh, I see. : )