Report - sometimes works?! Why???

[bbznyc]

Hello Guys and Gals,

I have a problem. I have orders form which is filled out by a user but when payment field is not touched (no payment is ever entered initially), I can't run a report on employer's history or view employee's history - I get an error in my "BALANCE OWED" box. I enclosed a sample of the dbase!


Please help me - I am pulling my hair - very little left

i have attached the file here
http://home.mindspring.com/~bbznyc/FS_DB_v1.1.0v2002.zip

Thanks,

 

[Andrew Fergus]

Hi, I am unable to look at your database (I am using Access 2000) but I presume you have used a formula for the "Balance Owed" field and it may look something like this :

=[Total]-[Payment]

If there is nothing entered into the payment field then the formula is trying to deduct Null from [Total] resulting in the error.

If that is the case, then you can change the "Balance Owed" formula to something like this :

= [Total] - iif(IsNull([Payment]),0,[Payment])
or
= iif(IsNull([Payment]),[Total],[Total]-[Payment])

Alternatively, it may just be a matter of setting the default value of the [Payment] field to zero (the default when you created the table will be null). This should be done in both the table and on the form. Normally the form would get its default values from the table but given you have already created the form, then you will need to change the default value there too.

Lastly, if "Balance Owed" is a also field in a table (rather than a calculated control on the forms and reports) then you may want to set the default value for this field to zero too.

HTH, Andrew.