Replacing results from a query

[fenfool]

I run an expression in a query (in Access 2010) that divides two fields (Let's say 'FieldX' and 'FieldY') to get a percentage. Sometimes the data is not present, resulting in a #Num because it's dividing by 0, which is fine, but my question is...how do I replace the #Num with something like a dash (-)? I can do this in a report, but is it possible to do in the query itself? Also acceptable would be a way to do this prior to running the expression...'if FieldY = 0, then "-"' kind of thing.

Thanks!

 

[boblarson]

The expression would be something like this:

MyFieldName:IIf([FieldY] Is Null OR [FieldY]=0, "-", [FieldX]/[FieldY])

But I don't think that will work as you are mixing a string (-) with a numeric (the divided amount) as a field so it would need to return a string.

I would either use a 1 or Null

MyFieldName:IIf([FieldY] Is Null Or [FieldY] = 0, NULL, [FieldX]/[FieldY])

 

[fenfool]

Perfect, thanks! Null works just fine, it really doesn't like the number "-%".