Query returing accurate results sometimes, inaccurate at others

[bradp1979]

I must have done something wrong when I took it from post 2. Initially, I entered that in, and when I clicked into the form, it was giving me a missing parameter error. This time, it didn't. Whatever transpired between the first and last attempt, who knows? I'm just glad it works now. Thank everyone for your help.

[bradp1979]

One last question: I'm trying to make it to where if the result is 0, then leave it blank. I tried "If 0 then """ in the criteria, but that makes the whole list box blank. Then I tried "Is Null OR > 0", but that just took away all of the complete row instead of just the data in the Ratios column.
I also tried "IF((Nz([actual#carsgarage],0)+Nz([actual#carsoutside],0))/[expected#guests]=0,"","(Nz([actual#carsgarage],0)+Nz([actual#carsoutside],0))/[expected#guests]")"

I've tried everything I can think of, and although I'm getting results, they're not the ones I want.

[June7]

Maybe don't use Nz() function. Arithmetic with null returns null.

[bradp1979]

No dice. I give up. the 0.00% isn't breaking or hurting anything. Though I wouldn't mind it being just 0%, or 28% instead of 0.00% and 28.00%. I changed the format to be a percent with 0 decimals, but it's still showing the 2 decimals.

[June7]

The function is IIf() not IF() - was that just a typo in post?

Why do you have quote marks around the If False argument expression?

You tried expression without Nz()?

[bradp1979]

It's not a typo in the post, I had it as IF.

I do'nt know why I have quote marks around the if false argument, I'm not sure which one.

I changed it to IIf, and it still just removes all of the data in all of the rows.

I did try it without Nz, and I keep getting an error message of "Syntax error (comma) in query expression (([actual#carsgarage],0)+([actual#carsoutside],0))/[expected#guests]"

[bradp1979]

With this as the expression: Ratio: (([actual#carsgarage])+([actual#carsoutside]))/[expected#guests], I'm not getting any results in the ratio column at all.

[June7]

Also have to remove the ,0 parameter that goes with the Nz() function.


([actual#carsgarage]+[actual#carsoutside])/[expected#guests]

[bradp1979]

Same problem. I put Ratio: ([actual#carsgarage]+[actual#carsoutside])/[expected#guests] and I'm not getting any results in the Ratio column at all now.

[ssanfu]

One last question: I'm trying to make it to where if the result is 0, then leave it blank.

You could use a custom format for the control.... From Help:


Format Property - Number and Currency Data Types

Custom Formats
Custom number formats can have one to four sections with semicolons ( ; ) as the list separator. Each section contains the format specification for a different type of number.

Section Description

First	The format for positive numbers.
Second	The format for negative numbers.
Third	The format for zero values.
Fourth	The format for Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) values.

For example, you could use the following custom Currency format:
$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"


You could try:

##0.00;(##0.00)[Red];"";""

or

0.00;(0.00)[Red];"";""

-------------------------
BTW, when you post your dB for analysis, if the db is split, YOU should ensure that the FE/BE will link without problems. It takes a lot of time to try and figure out why the tables are not linking. You know the dB much better than we do - ensuring that FE/BE link and that the data is visible in forms/reports makes it easier to troubleshoot.
I'm just saying........

[June7]

Very odd. Should calculate properly. Would have to review data and structure of queries.

[CJ_London]

you are mixing text and numbers in the same field try

Ratio: IIF(Nz([actual#carsgarage],0)+Nz([actual#carsoutside],0)=0 or [expected#guests] is null, null, (Nz([actual#carsgarage],0)+Nz([actual#carsoutside],0))/[expected#guests])

[rpeare]

that's why I said

Your formula is not what other suggested

though there should be an s on other