iff expression problem

[Davide77]

Hallo,
I have a parameter query that refers to a form.
If the checkbox is checked (0 or No) then it should display the records where the field is = 0 (this field is a count field); if checkbox is not checked it should display all the records where the count fields is <> different than 0.

The expression iI'm using is this:

IIf([Forms]![frm_main]![Chk_Org]=0;0;<>0)

but it doesn't work.
the strange thing is that if I try the two statements (0 ; <>=) everything is fine, but if I put them into the iff function they don't work.
I also tried different variations like [chk_Org]="No" or Not 0 instead of <> 0...

Any idea?

 

[BNPMike]

I can make a similar thing work . Use -1 instead of <>0

 

[Golom]

Couple of problems that I see ...

First, the argument separator for IIF is a comma ... not a semi-colon.

Second, your clauses need to return values and I'm unsure what sort of "value" a thing line "<>0" will return. You probably want something like
[tt]
WHERE IIF([Forms]![frm_main]![Chk_Org]=0,
[Field] = 0,
[Field] <> 0 )
[/tt]

 

[TonyJollans]

Hi Golom,

I believe that the argument separator is a semicolon when the international settings are set to use the comma as a decimal point.

I agree with your solution otherwise. It is not possible to return partial clauses as strings like Davide is trying to do; he must either return a complete clause (as you show) or a single operand (or part of a string operand).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Golom]

TonyJollans

Really? That's scary!

Does that mean that all my applications with hard-coded commas will die horribly if I switch the settings to exchange commas and decimal points?

(I'm not about to try it if that's the case.)

 

[Davide77]

Hallo everybody,
Still I cannot make it work properly.
The problem is in the <<false>> part of the iff expression.

I'm using this (I changed the chekbox with a value list combo ("yes","no"), in order to avoid any ambiguity:

IIf([Forms]![frm_main]![Combo21]="Yes";0;2)

The field contains numbers and if a put numbers as statements (like in the above example) it works fine. But as soon as I want to say: all numbers but 0 in the false part it doesn't work:

IIf([Forms]![frm_main]![Combo21]="Yes";0;<>0)
or
IIf([Forms]![frm_main]![Combo21]="Yes";0;[field]<>0)
or
IIf([Forms]![frm_main]![Combo21]="Yes";0;not 0)

 

[BNPMike]

You can't use "<>0" or "not 0". It's -1. There can't be any other values so why are you complicating it??

 

[Davide77]

hallo BNPMike,
I'm not sure if you understood the problem.
the field where i put the condition contains numbers: 0,1,2...
And I want to get the records depending on this field content. Sometime I want to get the records having the field with "0", sometime the records having the field with all the others numbers. If I put -1 the query won't show me anything because there are no records where the field is -1. It contains just natural numbers from 0 on.

 

[BNPMike]

Sorry. I read 'checkbox' and then didn't concentrate.

Try >0

 

[Davide77]

Also tried but with the same result:

the query will return always the same result: it seems to consider only the true part, that means that it always returns me all the records having the field with 0.
And access automatically change may expression from:

IIf([Forms]![frm_main]![Combo21]="Yes";0;>0)

to:

IIf([Forms]![frm_main]![Combo21]="Yes";0;(Count([tbl_Timeslot].[Tim_ID]))>0)

the (count([tbl_Timeslot].[Tim_ID])) identifies the field.

The strange thing is that if I put ONLY the false part of the expression: Count([tbl_Timeslot].[Tim_ID]))>0
the query will return me all the field with >0 as requested, but with the iff expression don't work.

 

[RoyVidar]

Davide77:
Sorry, can't help you with the query, I'm afraid. Could an alternative be to switch recordsource on the fly? Create two different queries, and (form example):

[tt]If Me!Combo21.Value ="Yes" then
Form!SomeForm.RecordSource = "query1"
Else
Form!SomeForm.RecordSource = "query2"
End If
Form!SomeForm.Requery[/tt]

- or assign the sql thru VBA...

Golom:
Concerning Access, I don't think you'll have to worry much about ";" vs ",".

After entering a controlsource at design time:

[tt]=Dmax("Field";"Table")[/tt]

Access transform argument separators when changing regional settings like a charm (or just when changing decimal separator).

We (people residing in non default contries;-)) must use the US way in VBA coding, so setting a control source for a control, might look like this:

[tt]Me!txtBox.ControlSource = "=DMax(" & chr(34) & "Field" & _
chr(34) & "," & chr(34) & "Table" & chr(34) & ")"[/tt]

With default syntax (if I've been able to put it right here). Haven't yet had any problems with this. It seems, not that I've tested it, that the argument separators are more a kind of "format", when vieved by VBA, but are separators at design time.

Doing a debug.print on the same control source, would show:

[tt]=DMax("Field","Table")[/tt]

As far as I've seen, all programmatic manipulations with numbers, uses ".", but there might be issues with programmatic validation of controls bound to numeric fields.

What might be a cause for concern, is when using dates in VBA sql strings. For instance, I believe the UK date format to be something like "dd/mm/yyyy", which works well as long as the day is 13 or higher, else Jet might interpret 8. May (08/05/2004) as 5. Aug. Thats a bit easier where I reside "dd.mm.yyyy", where concatinating a date according to our settings into the sql string, provides a syntax error. Here's the need to format if the app is supposed to work with other settings than US.

Roy-Vidar

 

[Davide77]

thanks Roy, your suggestion gave me food for thought

 

[TonyJollans]

Hi Davide,

Just come back to see you're still stuck on this. Golom's answer is the one you want, which I think in your case should be this:

[blue]WHERE IIF([Forms]![frm_main]![Combo21]="Yes", 
             count([tbl_Timeslot].[Tim_ID])  = 0,
             count([tbl_Timeslot].[Tim_ID]) <> 0)[/blue]

If you can't make it work, can you post your complete WHERE clause (instead of just the IIF bit).


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.