What's wrong with my SQL Code?

[ComradeGrumbles]

Hi again everyone. I have a query that "sorta" works, but doesn't seem to abide by one of my criteria.

I am trying to search by two criteria in this query. First, I want the query to select records based on what "System" they relate to. Next, I want it to sort those tickets by "Release Date." Ex. System 1 has a bunch of tickets with release dates ranging from July, 2014 to August, 2016. I want to find System 1 tickets with release dates before November, 2015.

Here's the SQL code for the Where portion of my query. The Select and From portions work fine as far as I can tell.

WHERE ((([SPR Priority].[Release Date]) Between Date() And Forms![Navigation Form].Form!NavigationSubform.Form!ReleaseDateEnter) And (([SPR Priority].[System(s)])=Forms![Navigation Form].Form!NavigationSubform.Form!SystemReleaseSearch));


This code correctly searches by the system I select from the dropdown "SystemReleaseSearch" on my form. It doesn't seem to properly abide by the textbox "ReleaseDateEnter" though.

What I think that it's doing is it gives me any ticket with a release date before the month and day that I put in the ReaseDateEnter textbox. It seems to disregard the year though. Ex. I put in 09/21/2014 and want to see dates between today and that date. Access gives me those dates, PLUS dates in 2013 that fall between 07/28/2013 and 09/21/2013. I can't really make any sense of this.

Any ideas?

 

[pbaldy]

Is that field actually a date/time field, or is it perhaps text (the data type). It's acting like it's text.

 

[ComradeGrumbles]

It's an unbound textbox and the "Text format" is set to "Plain Text." I didn't see any date in there. Format is "Short Date." How can I set this as a date field?

Pardon my inexperience. I'm learning as I go. Thanks for the fast help!

 

[pbaldy]

I mean the data type of the field "Release Date". By the way, you'll find that spaces and symbols in object names aren't worth the bother in the long run.

 

[ComradeGrumbles]

Ohhhh, I didn't even think about that. Good catch and thank you! I'll give that a try

 

[pbaldy]

No problem. If the field is text, you'll get an alphabetic sort and everything between 07 and 09 would be returned, regardless of year.

 

[ComradeGrumbles]

Thank you! This worked. Now it makes sense as to why Access was giving me those seemingly random results before.

PS: If I go through my column names and eliminate spaces, will those name changes be automatically reflected in my queries and macros? I'm fairly sure I'll have to manually change any references to those fields in my VBA modules though.

Thanks again!

 

[pbaldy]

Happy to help! The "automatic" bit depends on whether you have Name Autocorrect on, but frankly I wouldn't count on it, don't trust it and always turn it off. Perhaps something to keep in mind for the future and let sleeping dogs lie if this project is too far down the road to change.