>>>>>Hello All;
>>>>>
>>>>> I have a simple select statement on a table that just continues to error out on me(Fatal Error: Exception Code C0000005), but the SQL Statement is soooo simple, it just doesnt make any sense at all! Here is the SQL Statement:
>>>>>
>>>>>SELECT ;
>>>>> slsv, ;
>>>>> Notes, ;
>>>>> AcctNum, ;
>>>>> Buyer,;
>>>>> Call1AGt, ;
>>>>> Call2Agt, ;
>>>>> Call3Agt, ;
>>>>> Call4Agt, ;
>>>>> Call5Agt, ;
>>>>> Status, ;
>>>>> LastCall, ;
>>>>> Phone1, ;
>>>>> Phone2 ;
>>>>> FROM Xcaller ;
>>>>> WHERE LastCall Between(CallDate,(date()-nDaysback), Date()) ;
>>>>> INTO TABLE C:\TEMP\CALLTMP1
>>>>>
>>>>>Nothing crazy in there, pretty straight forward, right?? so what am I missing Guys?(and Gals ofcourse)
>>>>
>>>>WHERE clause is invalid. it must be:
>>>>
>>>>WHERE Between(LastCall ,(date()-nDaysback), Date())
>>>>
>>>>
>>>>or something else but not: WHERE LastCall Between(CallDate,(date()-nDaysback), Date()) ;
>>>>
>>>>What type is LastCall field?
>>>
>>>The LastCall Field is a Date field... it is a valid statement.
>>
>>Nope, it is not valid.
>>
>>Check carefuly this:
>>WHERE
LastCall Between(CallDate,(date()-nDaysback), Date()) ;
>>
>>????????
>>Where is condition for LastCall? There is only one condition that returns true or false and it is based to CallDate [Between(CallDate,(date()-nDaysback), Date())]
>>
>>so WHERE DATE() .t. can't work.
>
>Ok, I hear what you are saying... but your missing what is in the statement. I dont mean to split hairs... but really, the statement is truly Valid. BETWEEN(LastCall, (Date()-nDaysBack), DATE()) if you think the problem is with DATE()-nDaysBack, your wrong... because that translates to the calandar date, minus the Variable number(lets say 20 days in this example) - nothing wrong with that. You must do the math in the parenthesis first, just like in Algebra. BETWEEN(FieldtoCompare,(Low end of Value), (High End of Value)) Date()-20 is the same as saying {^2005/06/16} so the between statement above directly translates to BETWEEN(LastCall,{^2005/06/16}, {^2005/07/06})
No I didn't think the BETWEEN() function and all calculations made it there are wrong, but check your original WHERE. It was:
WHERE LastCall Between(CallDate,(date()-nDaysback), Date()) this is not the same as
WHERE Between(LastCall,(date()-nDaysback), Date()) did you see the difference?
BTW I'll do the calculations before SELECT for speed reason. Something like:
m.ldDateBegin = (date()-nDaysback)
m.ldDateEnd = date()
and then:
SELECT .... WHERE Between(LastCall,m.ldDateBegin, m.ldDateEnd)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.