VBA Function problem

[clifftech]

I developed a VBA rounding function "MyRound()" that resolves the difference between Excel's rounding and the Access' rounding function. The function works fine in a stand alone query but when I use the query in a left join with another table it gives me an #ERROR in fields that have no value. If I replace the vba MyRound function with the Access Round() function the ERROR# does away with the left join (but the rounding is incorrect). I have tried different formats for my vba function (long, double, currency) - no difference.

This one has me stumped - Please help!!!

 

[PHV]

Have a look at the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[clifftech]

The function works fine on Query1, with and without nulls. It is when Query1 is used with an inner join on table All_Records (the MyRound function is not used on any of the All_Records fields. All_Records has more records than Query1 therefore the result of the inner join (All All_Records records and only those records from Query1 where the joined fields are equal) shows some rows with values and some rows without. The rows that do not have values show ERROR# in the fields that used the MyRound() function. The function is only used on Query1 not on the All_Records table.

I tried the Nz function like you mentioned and still got the ERROR# result.

Any other suggestions?

 

[PHV]

Any chance you could post the SQL and function codes ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[clifftech]

PHV

After trial and error I found out that you were right about the null values and used the Nz function to resolve the problem. The trouble is by using this function the query slowed down by a magnitude of 10! I am trying to rewrite the query to speed it up.

Should the Nz function slow the query down so much?