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

[5pac3m0nk3y]

Hello,

Thanks ahead of time for any advice.
I have a query that pulls the startdate and enddate criteria from a form. In the criteria field I built the following expression: [Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txtBeginOrderDate]

The same is true of the enddate only the form field is txtEndOrderDate.

Last I entered '=' prior to the expression.

The = sign keeps disappearing everytime I run the query?
Anyone run into this before and found a solution?

Thanks,
Al

 

[Ranman256]

If the task is to get the data between the 2 dates,you don't need the equal sign,

Where [date] between forms!myForm!txtStart and forms!myForm!txtEnd

 

[MarkK]

I think the equals sign is assumed if you type a single item in the criteria field. What matters is if it is present in the SQL, which you can view by displaying the query in SQL view. Is the equal sign there in the WHERE clause of the query?

 

[5pac3m0nk3y]

If the task is to get the data between the 2 dates,you don't need the equal sign,

Where [date] between forms!myForm!txtStart and forms!myForm!txtEnd

Thanks for the suggestion. My query is a little more complicated because I had to remove HH:MM::SS from the date derived from a linked table ODBCed into a DB I can't alter.

How would I follow your suggestion with this SQL:

SELECT DISTINCT DateValue([CLASSROSTERVIEW.STARTDATETIME]) AS ShortStartDate, DateValue([CLASSROSTERVIEW.ENDDATETIME]) AS ShortEndDate, CLASSROSTERVIEW.COURSE_ID, CLASSROSTERVIEW.COURSETITLE, CLASSROSTERVIEW.PERSONNELNUMBER, CLASSROSTERVIEW.PERSONNELDISPLAYNAME, PERSONNELVIEW2.EMAILADDRESS, ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY
FROM ENROLLEDSTUDENTSVIEW1 INNER JOIN (CLASSROSTERVIEW INNER JOIN PERSONNELVIEW2 ON CLASSROSTERVIEW.PERSONNELNUMBER = PERSONNELVIEW2.PERSONNELBARCODE) ON ENROLLEDSTUDENTSVIEW1.COURSEID = CLASSROSTERVIEW.COURSE_ID
WHERE (((DateValue([CLASSROSTERVIEW.STARTDATETIME]))>=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txtBeginOrderDate]) AND ((DateValue([CLASSROSTERVIEW.ENDDATETIME]))<=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txEndOrderDate]) AND ((PERSONNELVIEW2.EMAILADDRESS) Is Not Null) AND ((ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY)='Enrolled'));

 

[5pac3m0nk3y]

I think the equals sign is assumed if you type a single item in the criteria field. What matters is if it is present in the SQL, which you can view by displaying the query in SQL view. Is the equal sign there in the WHERE clause of the query?

Hello,

Yes it is there in the SQL query. Another person here suggested changing the query to find dates between the start and end date. In my SQL, it is set to do just that.

I'm trying to write a custom report for a program called Training Manager to be used in a mail merge. I'm almost there. In Training Manager, if I search for all courses between say 3/1/2017 and 3/23/2017, I will see courses that start and end on different dates within that time span, but with my query, I have to enter in the precise start date and end date of a given course to pull it up.

Seems that if I search between two dates I would get everything. Not sure why that is. Here's the SQL in case you're good at analyzing those sort of problems.

SELECT DISTINCT DateValue([CLASSROSTERVIEW.STARTDATETIME]) AS ShortStartDate, DateValue([CLASSROSTERVIEW.ENDDATETIME]) AS ShortEndDate, CLASSROSTERVIEW.COURSE_ID, CLASSROSTERVIEW.COURSETITLE, CLASSROSTERVIEW.PERSONNELNUMBER, CLASSROSTERVIEW.PERSONNELDISPLAYNAME, PERSONNELVIEW2.EMAILADDRESS, ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY
FROM ENROLLEDSTUDENTSVIEW1 INNER JOIN (CLASSROSTERVIEW INNER JOIN PERSONNELVIEW2 ON CLASSROSTERVIEW.PERSONNELNUMBER = PERSONNELVIEW2.PERSONNELBARCODE) ON ENROLLEDSTUDENTSVIEW1.COURSEID = CLASSROSTERVIEW.COURSE_ID
WHERE (((DateValue([CLASSROSTERVIEW.STARTDATETIME]))>=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txtBeginOrderDate]) AND ((DateValue([CLASSROSTERVIEW.ENDDATETIME]))<=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txEndOrderDate]) AND ((PERSONNELVIEW2.EMAILADDRESS) Is Not Null) AND ((ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY)='Enrolled'));

Thanks,
Al

 

[MarkK]

So does that SQL work?

Yes, if you are wanting to find a date that falls between two other dates, then you cannot use equality, you have to compare to a range. In that case your test date must fall between the start and end dates of the range. In SQL there are two ways to do that...

WHERE TestDate BETWEEN StartDate AND EndDate
[COLOR="Green"]'or[/COLOR]
WHERE TestDate >= StartDate AND TestDate <= EndDate

I never use BETWEEN, because I can never remember exactly how it works. Semantically, BETWEEN to me means that it does NOT include the end dates, like...

WHERE TestDate > StartDate AND TestDate < EndDate

...But sometimes I need to do...

WHERE TestDate > StartDate AND TestDate <= EndDate

...so BETWEEN, to my mind, is somewhat less clear. How exactly does it handle end dates? Just > and <, or >= and <=? I never remember.

 

[5pac3m0nk3y]

So does that SQL work?

Yes, if you are wanting to find a date that falls between two other dates, then you cannot use equality, you have to compare to a range. In that case your test date must fall between the start and end dates of the range. In SQL there are two ways to do that...

WHERE TestDate BETWEEN StartDate AND EndDate
[COLOR=Green]'or[/COLOR]
WHERE TestDate >= StartDate AND TestDate <= EndDate

I never use BETWEEN, because I can never remember exactly how it works. Semantically, BETWEEN to me means that it does NOT include the end dates, like...

WHERE TestDate > StartDate AND TestDate < EndDate

...But sometimes I need to do...

WHERE TestDate > StartDate AND TestDate <= EndDate

...so BETWEEN, to my mind, is somewhat less clear. How exactly does it handle end dates? Just > and <, or >= and <=? I never remember.

If I enter in >= and <= I get any courses with start and end dates withing that range. But if a course starts before the startdate and ends midway within the range, it doesn't list the course. I want it do do that though. Not sure how to fix the query to do that.

 

[Minty]

That's because you need to test both course start and end dates for both criteria;

WHERE (CourseStart >= ReportStartDate AND CourseStart <= ReportEndDate) OR (CourseEnd >= ReportStartDate AND CourseEnd <= ReportEndDate)

 

[CJ_London]

or if you want any course which is active at any point between the two dates you would use

WHERE CourseStartDate <= EndDate AND CourseEndDate >= StartDate

 

[MarkK]

For this problem Paul has a very simple and clear graphic at his web page...
http://baldyweb.com/OverLap.htm

 

[5pac3m0nk3y]

That's because you need to test both course start and end dates for both criteria;

WHERE (CourseStart >= ReportStartDate AND CourseStart <= ReportEndDate) OR (CourseEnd >= ReportStartDate AND CourseEnd <= ReportEndDate)

Hi, thanks again for your reply. I appreciate it.

So I changed the code to:

WHERE (((DateValue([CLASSROSTERVIEW.STARTDATETIME]))>=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txtBeginOrderDate] And (DateValue([CLASSROSTERVIEW.STARTDATETIME]))<=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txEndOrderDate]) AND ((DateValue([CLASSROSTERVIEW.ENDDATETIME]))<=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txEndOrderDate] And (DateValue([CLASSROSTERVIEW.ENDDATETIME]))>=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txtBeginOrderDate]) AND ((PERSONNELVIEW2.EMAILADDRESS) Is Not Null) AND ((ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY)='Enrolled'));

But it still won't find a course that started prior to the start date but ends sometime during the range.

Hmmm.

 

[Minty]

Hi, thanks again for your reply. I appreciate it.

So I changed the code to:

WHERE (((DateValue([CLASSROSTERVIEW.STARTDATETIME]))>=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txtBeginOrderDate] And (DateValue([CLASSROSTERVIEW.STARTDATETIME]))<=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txEndOrderDate]) AND ((DateValue([CLASSROSTERVIEW.ENDDATETIME]))<=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txEndOrderDate] And (DateValue([CLASSROSTERVIEW.ENDDATETIME]))>=[Forms]![FRM_EMAIL_TRAINEES_BY_DATE]![txtBeginOrderDate]) AND ((PERSONNELVIEW2.EMAILADDRESS) Is Not Null) AND ((ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY)='Enrolled'));

But it still won't find a course that started prior to the start date but ends sometime during the range.

Hmmm.

You've used AND with the criteria blocks. Try an OR as per my post.

Sent from my Nexus 7 using Tapatalk

 

[CJ_London]

and perhaps take a look at posts 9 and 10

 

[5pac3m0nk3y]

and perhaps take a look at posts 9 and 10

Thanks I did and edited the query accordingly...at least I thought I did. I think I posted it. Not sure if you saw that. Maybe I misunderstood?

 

[5pac3m0nk3y]

You've used AND with the criteria blocks. Try an OR as per my post.

Sent from my Nexus 7 using Tapatalk

Will do. Thank you.

 

[CJ_London]

Thanks I did and edited the query accordingly...at least I thought I did. I think I posted it. Not sure if you saw that. Maybe I misunderstood?

I don't see your adoption of the suggestion made by myself and Mark so think you may have misunderstood

 

[5pac3m0nk3y]

So does that SQL work?

Yes, if you are wanting to find a date that falls between two other dates, then you cannot use equality, you have to compare to a range. In that case your test date must fall between the start and end dates of the range. In SQL there are two ways to do that...

WHERE TestDate BETWEEN StartDate AND EndDate
[COLOR=Green]'or[/COLOR]
WHERE TestDate >= StartDate AND TestDate <= EndDate

I never use BETWEEN, because I can never remember exactly how it works. Semantically, BETWEEN to me means that it does NOT include the end dates, like...

WHERE TestDate > StartDate AND TestDate < EndDate

...But sometimes I need to do...

WHERE TestDate > StartDate AND TestDate <= EndDate

...so BETWEEN, to my mind, is somewhat less clear. How exactly does it handle end dates? Just > and <, or >= and <=? I never remember.

Thanks for your reply. I tried to simplify the query by removing the form specific code and just put in the dates I'm trying to search. I did it in design view and the SQL comes out like this:

SELECT DISTINCT CLASSROSTERVIEW.STARTDATETIME, CLASSROSTERVIEW.ENDDATETIME, ENROLLEDSTUDENTSVIEW1.PERSONNELNUMBER, ENROLLEDSTUDENTSVIEW1.PERSONNELDISPLAYNAME, ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY, COURSECATALOG1.COURSENUMBER, COURSECATALOG1.COURSETITLE, PERSONNELVIEW2.EMAILADDRESS, ENROLLEDSTUDENTSVIEW1.COMPLETIONDATE
FROM CLASSROSTERVIEW INNER JOIN ((COURSECATALOG1 INNER JOIN ENROLLEDSTUDENTSVIEW1 ON COURSECATALOG1.COURSEID = ENROLLEDSTUDENTSVIEW1.COURSEID) INNER JOIN PERSONNELVIEW2 ON ENROLLEDSTUDENTSVIEW1.PERSONNELNUMBER = PERSONNELVIEW2.PERSONNELBARCODE) ON CLASSROSTERVIEW.PERSONNELID = ENROLLEDSTUDENTSVIEW1.PERSONNELID
WHERE (((CLASSROSTERVIEW.STARTDATETIME)>=#3/1/2017# And (CLASSROSTERVIEW.STARTDATETIME)<=#3/31/2017#) AND ((ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY)="Enrolled") AND ((PERSONNELVIEW2.EMAILADDRESS) Is Not Null) AND ((ENROLLEDSTUDENTSVIEW1.COMPLETIONDATE) Is Null)) OR (((CLASSROSTERVIEW.ENDDATETIME)>=#3/1/2017# And (CLASSROSTERVIEW.ENDDATETIME)<=#3/31/2017#));

But Access freezes everytime. If I use my old approach it works fine though not getting courses that started before the startdate and ended during the range. Maybe this is like a WYSIWYG editor where the code is terrible. I'll try to hand code it.

 

[MarkK]

...

WHERE (((CLASSROSTERVIEW.STARTDATETIME)>=#3/1/2017# And (CLASSROSTERVIEW.STARTDATETIME)<=#3/31/2017#)

...though not getting courses that started before the startdate and ended during the range.

This SQL only selects for rows that have a start date in March 2017.

 

[CJ_London]

this

WHERE (((CLASSROSTERVIEW.STARTDATETIME)>=#3/1/2017# And (CLASSROSTERVIEW.STARTDATETIME)<=#3/31/2017#)

should be
WHERE (((CLASSROSTERVIEW.ENDDATETIME)>=#3/1/2017# And (CLASSROSTERVIEW.STARTDATETIME)<=#3/31/2017#)

to show all classes that had any period in March

 

[5pac3m0nk3y]

this

WHERE (((CLASSROSTERVIEW.STARTDATETIME)>=#3/1/2017# And (CLASSROSTERVIEW.STARTDATETIME)<=#3/31/2017#)

should be
WHERE (((CLASSROSTERVIEW.ENDDATETIME)>=#3/1/2017# And (CLASSROSTERVIEW.STARTDATETIME)<=#3/31/2017#)

to show all classes that had any period in March

Thank you but This OR statement times out Access. The logic is most likely correct, but something else is up. I could change the time out value? Its been going for a few minutes though. :banghead: