David,
>You could switch it to:
>
>between(iif(extryear<'10','20','19')+extryear+extrweek, lcBeginWeek, lcEndWeek) as currmnth,
Ok, I did. The first SQL seems to work fine after this change, the second (with union) still gives me this error. In VFP6.0 or in VFP5.0 with set talk off it works without problems. I understand, that even if it's a bug, MS will not fix this version, since VFP6.0 is available, and, hopefully, VFP7.0 will be available soon. I'd rather not use VFP5.0, but I did it by my manager's request. Anyway, right now I want to stop waisting my time on this problem.
Thanks for your help.
>
>Even if it does turn out to be a bug in VFP5, it's inconsequential since that version is no longer being fixed. This all does work in VFP6 right?
>
>> In the first SQL this line seems to cause the problem:
>> between(iif(val(extryear)<10,'20','19')+extryear+extrweek, lcBeginWeek, lcEndWeek) as currmnth, because if I excluded it, it worked just fine. But why this line is problematic? Extryear is 2 characters field, like '99' or '00'.
>>
>> I used messagebox(lcWhere). I also opened debug and saw, that I had:
>>lcWhere='aa.CCODE+aa.TOWN=bb.County+bb.TOWN', if I hardcoded these values. I think, this is enough to report a strange bug. I spent a whole Friday, testing this SQL and now I want to stop here.
If it's not broken, fix it until it is.
My Blog