Part of my explanation was missing: The DATE() function does not accept year, month and day parameters that are equal to 0 (zero) and responds with the 'Function argument value, type, or count is invalid.' error. Because the VAL(substr()) starts with an empty string, the call to the DATE() function is DATE(2000,0,0). So there's your error!
Regards, Sietse > > 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? _______________________________________________ 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.
