'=' sign disappears from query criteria (1 Viewer)

5pac3m0nk3y

Registered User.
Local time
Today, 13:52
Joined
Mar 9, 2017
Messages
11
This SQL only selects for rows that have a start date in March 2017.

This is supposed to catch courses that have a start date prior to March but an end date in March.

Before I was simply sorting by >=#3/1/2017# and <=#3/31/2017#. But a course that starts prior to 3/1/2017 yet ends mid march should be included.
 

MarkK

bit cruncher
Local time
Today, 10:52
Joined
Mar 17, 2004
Messages
8,178
This is supposed to catch courses that have a start date prior to March but an end date in March.
You want to pay attention to the exact language you are using here, and write a statement that honors those constraints, so a "start date prior to March" is...
Code:
WHERE StartDate < #3/1/2017#
...[and] "an end date in March" is...
Code:
AND Year(EndDate) = 2017 AND Month(EndDate) = 3
...for a WHERE clause like....
Code:
WHERE StartDate < #3/1/2017# AND Year(EndDate) = 2017 AND Month(EndDate) = 3
The important part is to be very specific in the language you use to describe the constraint, and then write the WHERE clause that honors that language.
 

5pac3m0nk3y

Registered User.
Local time
Today, 13:52
Joined
Mar 9, 2017
Messages
11
You want to pay attention to the exact language you are using here, and write a statement that honors those constraints, so a "start date prior to March" is...
Code:
WHERE StartDate < #3/1/2017#
...[and] "an end date in March" is...
Code:
AND Year(EndDate) = 2017 AND Month(EndDate) = 3
...for a WHERE clause like....
Code:
WHERE StartDate < #3/1/2017# AND Year(EndDate) = 2017 AND Month(EndDate) = 3
The important part is to be very specific in the language you use to describe the constraint, and then write the WHERE clause that honors that language.

Sorry about that. I'll try to be more precise in future. It seems the query finally runs, it just takes a long time. It works now. Thank you kindly.
 

MarkK

bit cruncher
Local time
Today, 10:52
Joined
Mar 17, 2004
Messages
8,178
No, no apology required. I'm just describing the process of how to go from the idea in your head to the code on the screen.

To speed up the query, make sure your date fields in the table are indexed (allow duplicates). In order to determine what rows should and should not be returned, this query has to sort all your data by StartDate and EndDate first. That sorting is way, way, way faster if you index the fields. An index is like pre-sorting them. Similarly, it may run faster if you index the StatusDisplay field.

hth
 

Users who are viewing this thread

Top Bottom