DEMENTED - PLS HELP "Query - No record in table"

[Macintosh]

I have an expression in a query which returns a value from a field in a record. But there may not always be a record which matches the criteria. If there is not I wish to return zero. I can't find a function which does this. Nz and IsNull are no help. Is IsMissing the one I should use (I can't find any info on what it does)? Or how should I go about this? I feel it should be so simple, but I've been days on this and am genuinely tearing my hair (what's left of it).

 

[Dave Eyley]

Have a look at the IIf function in your Help page. It might be what you're looking for.

 

[Rich]

With little information supplied we're guessing on your structure, but try changing the joins on the tables in your query grid

 

[Macintosh]

Sorry about the lack of info - I tried to be concise but I've overdone it! I was in fact using the <iif> with the <isnull> and <nz> as mentioned, I also tried the <"">. The problem is that if there is not a record in the table the query returns a blank (not a null or a zero) on which I'm finding it impossible to carry out further processing - ie this blank is "carried through" to the next part of the query which therefore returns a blank. Regarding the jointype, I've tried them all! Any further light shed on this would be appreciated.

 

[cogent1]

Do you mean that the query returns zero rows OR that the query returns rows where a field is blank?

Please post the query with the expression, so we can advise on how to proceed.