'=' sign disappears from query criteria

[5pac3m0nk3y]

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]

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...

WHERE StartDate < #3/1/2017#

...[and] "an end date in March" is...

AND Year(EndDate) = 2017 AND Month(EndDate) = 3

...for a WHERE clause like....

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]

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...

WHERE StartDate < #3/1/2017#

...[and] "an end date in March" is...

AND Year(EndDate) = 2017 AND Month(EndDate) = 3

...for a WHERE clause like....

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]

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