Hmm. strange indeed.
What I'm seeing: The 1st call to the date function, the curTemp.cDate
contains an empty value! I'm not sure but I'm bettiong it has to do with
optimization of the query.
I've seen this result by using an UDF that accepts 1 param and prints it out
to the screen.
Then include that UDF as a column, passing the curTemp.cDate as the param
The solution:
use EVL() in the expression:
Date(2000+Val(Substr(EVL(curTemp.cDate, "01/01/00"),7,2)), ;
Val(Substr(EVL(curTemp.cDate, "01/01/00"),4,2)), ;
Val(Substr(EVL(curTemp.cDate, "01/01/00"),1,2))) ;
Another solution:
You're only using the join to filter out the records from the lookup:
Don't use the join but use
Where curTemp.F1="A01" AND curTemp.FK1 IN (SELECT pk FROM curTemp2)
Regards,
Sietse Wijnker
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Dave Crozier
> Sent: maandag 31 juli 2006 12:04
> To: [EMAIL PROTECTED]
> Subject: Wierd SQL Error - Any Ideas?
>
> To All,
> I had a WEIRD error come up over the weekend in a set of
> programs that have been working for ages. I managed to tie
> the problem down to what I think is an error in VFP and
> wonder what you all think.
>
> Run the program below and you should find that the first and
> second select statement works OK and the third one doesn't!
> My question is WHY?
> I can't come up with a reasonable answer here other than the
> fact that in
> (3) VFP seems to be trying to "pre process" or parse the
> Date() expression with a dataset containing "null" records
> (maybe?) caused by the "Join".
>
> As the only difference is the Join in statement (3), this is
> obviously the culprit but WHY?
>
> Important Note:
> If you replace the date() statement with
> Ctod(Substr(curTemp.cDate,1,8)) ;
> as Date ;
>
> Then ALL the statements work.
>
> Surely this is a deep error with the Date() - or am I missing
> something obvious here. All I know is that it ate up 4 hours
> to find the problem!
>
> *******************
> * Start of Program
> *
> Close Tables all
>
> Set Date British && DD/MM/YY
> Create Cursor curTemp (FK1 I, F1 C(10), cDate C(8))
>
> Insert Into curTemp Values (1, "A01","01/01/06") Insert Into
> curTemp Values (1, "A02","02/01/06") Insert Into curTemp
> Values (2, "A03","03/01/06") Insert Into curTemp Values (2,
> "A04","04/01/06") Insert Into curTemp Values (1, "A05","05/01/06")
>
> Create Cursor curTemp2 (PK I, F1 C(10))
> Insert Into curTemp2 Values (1, "Lookup")
>
> * (1) This statement works OK it has one record Select All ;
> Date(2000+Val(Substr(curTemp.cDate,7,2)), ;
> Val(Substr(curTemp.cDate,4,2)), ;
> Val(Substr(curTemp.cDate,1,2))) ;
> as Date ;
> From curTemp ;
> Where curTemp.F1="A01" ;
> Into Cursor curResult
> *
> Browse
>
> * (2) This is OK, no records
> Select All ;
> Date(2000+Val(Substr(curTemp.cDate,7,2)), ;
> Val(Substr(curTemp.cDate,4,2)),;
> Val(Substr(curTemp.cDate,1,2))) ;
> as Date ;
> From curTemp ;
> Where curTemp.F1="A99" ;
> Into Cursor curResult
> *
> Browse
>
> * (3) This errors. Also No records
> Select All ;
> Date(2000+Val(Substr(curTemp.cDate,7,2)), ;
> Val(Substr(curTemp.cDate,4,2)), ;
> Val(Substr(curTemp.cDate,1,2))) ;
> as Date ;
> From curTemp ;
> Join curTemp2 on curTemp2.PK=curTemp.FK1 ;
> Where curTemp.F1="A01" ;
> Into Cursor curResult
> *
> Browse
> *
> * End of Program
> ****************
>
> Dave Crozier
> The best way to make a Million pounds in computing.
> ...Start with 5 million!
>
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.10.5/403 - Release
> Date: 28/07/2006
>
>
>
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
