Query Error In Reprot

[asm]

Hi

I Am Using Access2000 Database.

Query
------
Select Gen.glname As Glname, Gen.groupc As Groupc, Gen.opbal As Opamt, Sum(txn.amount) As Txnamt, (opamt+nz(txnamt,0)) As Clbal
From Gen Left Join Txn On Gen.glcode=txn.glcode
Group By Gen.glname, Gen.opbal, Gen.groupc;

While Calling This Query In Crystal Error Message Shown -

Odbc Error: [microsoft][odbc Microsoft Access Driver ]
Undefined Function 'nz' In Expression.


Pls Help How To Overcome From This Problem.


Thanks

Asm

[RobDog888]

Looks like you have a valid Access query but if your using it as your CR query source then its not valid as CR doesnt know what nz is. Take your query and save it as an Access query. Then in CVR set that saved query as your datasource.

[asm]

I am saved the query in Access than I call in CR for reporting.



asm

[RobDog888]

Did you set it as the data source like in the sql string -

SELECT * FROM Query1

Do you have the report option to show stored procedures? CR looks at Access as stored procedures. What connection technology are you using (ODBC, ADO, DNS)?

[asm]

same as you give us example


asm

[RobDog888]

Change the nz function to an IIF statement. That should solve it.

[asm]

I Am Using Iif Then #error In Result Pls Guide How To Write The
Statement.


Select Gen.glname As Glname, Gen.groupc As Groupc, Gen.opbal As Opamt, (iif(sum(txn.amount)=' ', Sum(txn.amount),0)) As Txnamt, (opamt+txnamt) As Clbal
From Gen Left Join Txn On Gen.glcode=txn.glcode
Group By Gen.glname, Gen.opbal, Gen.groupc;



Asm

[RobDog888]

Looks like your trying to evaluate some value in txn.amount field although it seems its a text field.

'Original
(iif(sum(txn.amount)=' ', Sum(txn.amount),0)) As Txnamt,'
'Determines if field is null
(IIf(txn.amount='', SUM(txn.amount), 0)) As Txnamt,'