What's wrong with this SQL

[bastraker via AccessMonster.com]

Hello All

Can someone please take a look at the following Query SQL and tell me what is
wrong. Everything works fine except for [forms]![StatusReport]![DateAssigned].


A single date passes just fine, however if I put in a string BETWEEN
#11/4/05# AND #11/30/05# the thing craps out. If I manually change the SQL
to ((EngineeringIssues.DateAssigned) Between #11/4/2005# And #11/30/2005#)
everything works fine again.

Why can I manually set this criteria but not pass it?

SELECT EngineeringIssues.IssueID, EngineeringIssues.Issue, EngineeringIssues.
Category, EngineeringIssues.OnTime, EngineeringIssues.DateAssigned,
EngineeringIssues.DateCommited, EngineeringIssues.DateActual,
EngineeringIssues.Duration, EngineeringIssues.Responsible, EngineeringIssues.
Status, EngineeringIssuesActions.ActionDate, EngineeringIssuesActions.
ActionEngTech, EngineeringIssuesActions.ActionStatement
FROM EngineeringIssues INNER JOIN EngineeringIssuesActions ON
EngineeringIssues.IssueID = EngineeringIssuesActions.IssueID
WHERE (((EngineeringIssues.OnTime) Like [forms]![StatusReport]![Combo2]) AND
((EngineeringIssues.DateAssigned)=[forms]![StatusReport]![DateAssigned]) AND
((EngineeringIssuesActions.ActionDate)=(SELECT Max(ActionDate) FROM
EngineeringIssuesActions WHERE IssueID = EngineeringIssues.IssueID)));

Thanx in advance for any insight
D

 

[Guest]

If you are typing the following into one text box, it's seeing it as a string
and not criteria.

BETWEEN #11/4/05# AND #11/30/05#

I bet that it works if you type in just 11/4/05 or #11/4/05#

You probably need two text boxes on the form: one for the start date and one
for the end date.

Between [forms]![StatusReport]![DateAssignedStart] and
[forms]![StatusReport]![DateAssignedEnd]

 

[bastraker via AccessMonster.com]

Hey Jerry

NICE!!! Nail on the head bud. When I type it in myself it must be getting the
string as criteria but when I pass the same value it must register as a
string. What you provided, splitting it up, worked fine.

However, for curiousity sake, do you (or anyone else) know how I could pass
the value so that it would be interperated as criteria?

Thanx again
D

 

[Guest]

You could do it in a function where you dynamically build a SQL statement
then run it. You'd want some pretty heavy error trapping in the code as
someone could type in something pretty bizzare and cause problems. If someone
typed in something bizzare in the criteria, it probably just wouldn't return
any records.