Iif statement query (1 Viewer)

[evanhughes]

Hi All

I have a query to which I need to add a charge.
The query is based on table1 but the charge is only added if a yes/no field in table2 is true.

This is what I have tried but it does not work.

Charge: IIf([Table2]![Field1]=True,Format("25","Currency"),Format("0","Currency"))

Any ideas on what I am doing wrong.

I tried adding table2 to the query and it just upset everything.

Evan

 

[vbaInet]

When you say it doesn't work what exactly does it show? Can we see a screenshot?

If the field is a Yes/No field you will be better off using Yes or -1, as follows:

Yes:

Charge: IIf([Table2]![Field1]=Yes,Format("25","Currency"),Format("0","Currency  "))

-1:

Charge: IIf([Table2]![Field1]=-1,Format("25","Currency"),Format("0","Currency  "))

 

[Beetle]

If Table2 is not included in the query then this -

Charge: IIf([Table2]![Field1]=True,Format("25","Currency"),Format("0","Currency "))

isn't going to work period. You would need to use a DLookup inside the IIf statement and you would also need to include some criteria to determine which row should be returned from Table2. However, it would be more efficient to include Table2, and the relevant field, in the query. So, my first question would be;

I tried adding table2 to the query and it just upset everything.

What, exactly, do you mean by this? "it upset everything" is too vague for anyone to offer you advice about what the problem might be.

 

[evanhughes]

Thankyou for the speedy response.

I see what you mean and if I add the table2 to the query it returns hundreds of extra records all the same.

How would I use a DLookup in an if statement. Not something I have done before.

Could you possibly give me some code.

Evan

 

[vbaInet]

You would need to use a DLookup inside the IIf statement and you would also need to include some criteria to determine which row should be returned from Table2.

So:

Charge: IIf(Nz(DLookup([COLOR=Red]...[/COLOR]))=-1,Format("25","Currency"),Format("0","Currency "))

Here's a link on DLookup(). See what you can come up with:

http://www.techonthenet.com/access/functions/domain/dlookup.php