Set value in query depending on value in table

[Mighty]

I have a copy of a Pervasive table which I have set up in MS Access. I run the following query on the access table:

Select ORDNUM_10, PRTNUM_10, IIF(isNull(CURDUE_10), '09-SEP-09', CURDUE_10) AS DUEDATE, STATUS_10 FROM [Order Master] WHERE CREDTE_10>='2002-05-02'"

How can I modify the IIF section to get this to work directly on the Pervasive table. Mighty

 

[Vancouverite]

You can use the IfNull() function in Pervasive.SQL:

Select ORDNUM_10, PRTNUM_10, IFNULL(CURDUE_10, Convert('2009-09-09', SQL_DATE)) AS DUEDATE, STATUS_10 FROM "Order Master" WHERE CREDTE_10>='2002-05-02'

 

[Mighty]

Vancouverite,

Thanks for the response - appreciate it. However, when I try to use the IFNULL function I get an error saying invalid scalar function. Mighty

 

[Vancouverite]

What version of Pervasive are you using? I was assuming 2000, but this function may not be available in 7.0

 

[Mighty]

I am running Pervasive 2000. Mighty

 

[Vancouverite]

Is the data type of CurDue a date?

 

[Mighty]

Yes, it's a BTRVDATE field. The problem is that some of the dates are Null are some have invalid dates. I run a VBScript program which attempts to pull the last year's worth of records from a Pervasive table and write them to an Access table. However, as it loops through the records to write them to the Access table, it always fails with an invalid date/time error. Mighty

 

[Vancouverite]

Null dates shouldn't be a problem. Null is legal. However, the invalid dates are interesting... that would imply a date < 100 AD or > AD 9999 (Access/VB's minimum and maximum). You should probably modify your VBScript program to clean them up, e.g.

Dim MyDate As Variant

...

On Error Goto CleanUpDate
MyDate = MyRS!MyDate
ResumeDate:
On Error Goto MyErrorHandler

... other code here

Exit Sub

CleanUpDate:
MyRS.Edit
MyRS!MyDate = Null
MyRS.Update
MyDate = Null
Resume ResumeDate


This should clean up the dates as you go.