simplifying a complex SQL statement

[tazardous]

<DISCLAIMER: Yes, I did search the forum first on this topic.>

I've built an expression that parses out the date portion of a text field (esign), the expression is named EsignDate in the query and the query runs fine, returning the date portion.
(see expression below)

EsignDate: LTrim(Right([esign],Len([tblvalvular].[esign])-(InStr(1,[tblvalvular].[esign],":"))))

This parses the text field (example data is:
INPUT
esign = Samuel A. Wickline, MD signed on:8/13/2003
OUTPUT
EsignDate = 8/13/2003

My problem is, I'd like to be able to apply criteria, prompting the user for a start date and end date, but when I put

Between [first] And [second]
in the criteria field of the query for the above expression, I get:
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

I understand what the msg is telling me to do... but I've no clue how to simply the expression or solve this.
Can anyone give me suggestions?

Thanks!

Taz

 

[lespaul]

What is the entire SQL of the statement you are trying to run?

(switch to the SQL view to get this information)

Leslie

 

[tazardous]

Oops,
sorry, here it is:

SELECT tblvalvular.studycode, tblvalvular.testtype, LTrim(Right([esign],Len([tblvalvular].[esign])-(InStr(1,[tblvalvular].[esign],":")))) AS EsignDate, tblvalvular.esign
FROM tblvalvular
WHERE (((tblvalvular.testtype)="FunctionalMR") AND ((LTrim(Right([esign],Len([tblvalvular].[esign])-(InStr(1,[tblvalvular].[esign],":"))))) Between [first] And [second]))
ORDER BY tblvalvular.testtype;

 

[lespaul]

What happens if you convert with CDat? You may also need to add the date delimiters # around the parameters and add a parameters list to the beginning of the query. I would have to look around the site to get the correct syntax for the parameters list.

SELECT tblvalvular.studycode, tblvalvular.testtype, LTrim(Right([esign],Len([tblvalvular].[esign])-(InStr(1,[tblvalvular].[esign],":")))) AS EsignDate, tblvalvular.esign
FROM tblvalvular
WHERE (((tblvalvular.testtype)="FunctionalMR") AND CDat((LTrim(Right([esign],Len([tblvalvular].[esign])-(InStr(1,[tblvalvular].[esign],":"))))) Between [first] And [second]))
ORDER BY tblvalvular.testtype;

Leslie

 

[tazardous]

I'll try what you suggest... was hesitant to do so before because it seemed it would only make the statement more complex and the general error message seemed to suggest making it simplier.

Taz

 

[tazardous]

Nope,
Cdate has no effect, still get the same error.
the parameters are fine, same syntax was used on same query, but different field and things worked until I started parsing on this field.

thanks though!

taz

 

[Golom]

You should use a PARAMETERS declaration to let SQL know that you are expecting DateTime Values as input

Try this

PARAMETERS [First] DateTime, [Second] DateTime;

SELECT 
   V.studycode, V.testtype, 
   Mid$(V.[esign],InStr(1,V.[esign],":")+1) AS EsignDate,
   V.esign

FROM tblvalvular As V

WHERE 
   V.testtype="FunctionalMR" AND
   cDate(Mid$(V.[esign],InStr(1,V.[esign],":")+1)) 
        BETWEEN #[first]# And #[second]#

ORDER BY V.testtype;

 

[tazardous]

placing the # on the outside of the parameters as you suggest causes a syntax error on the following statement:

SELECT tblvalvular.studycode, tblvalvular.testtype, CDate(LTrim(Right([esign],Len([tblvalvular].[esign])-(InStr(1,[tblvalvular].[esign],":"))))) AS EsignDate, tblvalvular.esign
FROM tblvalvular
WHERE (((tblvalvular.testtype)="FunctionalMR") AND ((CDate(LTrim(Right([esign],Len([tblvalvular].[esign])-(InStr(1,[tblvalvular].[esign],":")))))) Between #[first]# And #[second]#))
ORDER BY tblvalvular.testtype;

 

[PHV]

You may create a saved query, say qryGetDate:
SELECT studycode, testtype, CDate(LTrim(Right(esign,Len(esign)-(InStr(1,esign,":"))))) AS EsignDate, esign
FROM tblvalvular;
And then create a new query based on it:
SELECT studycode, testtype, EsignDate, esign
FROM qryGetDate
WHERE testtype="FunctionalMR" AND EsignDate Between [first] And [second];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tazardous]

thanks to all who had a suggestion.
Quite by accident I found a solution.
My original query (never posted here) prompted the user to enter a first and second date. It worked fine and users entered dates like 02/02/2000 etc.

But the new query ONLY works when you enter the dates like 02-02-2000 etc.

Why the first one worked and the second didn't, I don't know, but the point is, if the user enters the proper format for a date using dashs, then the query succeeds.

Taz