If, then syntax error

[hrford]

Ok I've read through some other questions on this one and I'm just being particularly dense because I can't seem to figure out how to do this.


Ok some background I've inherrited an Access database that is not ideally set up but it is ok. I keep track of continuing education for lawyers on it.

I have made a report that tracks all the points through a specific time frame. Now many times we know the attorney has taken a class and how many points it is worth but we don't have the certificate proving they attended yet. I've writen my report so that at the report footer it sums all the different points tehy have earned. But I don't want it to sum all of them I only want it to sum the ones that I have a certificate for.

I tried writing this:

IIf([CERTIFICATE ON FILE]=True =Sum([blah blah blah)]

but it tells me basically I need something between the True and =Sum which I would like to be a than. I know how to have it just give me the ones I have a Certificate on but I want to see all the ones in the system between the 2 dates but only the totals for the ones with a cert.

I hope I haven't thoroughly confused the issue. Please help!!

[puppydogbuddy]

Ok I've read through some other questions on this one and I'm just being particularly dense because I can't seem to figure out how to do this.


Ok some background I've inherrited an Access database that is not ideally set up but it is ok. I keep track of continuing education for lawyers on it.

I have made a report that tracks all the points through a specific time frame. Now many times we know the attorney has taken a class and how many points it is worth but we don't have the certificate proving they attended yet. I've writen my report so that at the report footer it sums all the different points tehy have earned. But I don't want it to sum all of them I only want it to sum the ones that I have a certificate for.

I tried writing this:

IIf([CERTIFICATE ON FILE]=True =Sum([blah blah blah)]

but it tells me basically I need something between the True and =Sum which I would like to be a than. I know how to have it just give me the ones I have a Certificate on but I want to see all the ones in the system between the 2 dates but only the totals for the ones with a cert.

I hope I haven't thoroughly confused the issue. Please help!!

try entering it like this in your textbox :

= IIf([CERTIFICATE ON FILE]=True, Sum([blah blah blah]))

[hrford]

try entering it like this in your textbox :

= IIf([CERTIFICATE ON FILE]=True, Sum([blah blah blah]))

it tells me Syntax Error (missing operator)

I've also tried that with and extra set of () around Cert on file and true

[Nathan H]

Where is the "else" statement part of the formula?


= IIf([CERTIFICATE ON FILE]=True, Sum([blah blah blah],""))

[puppydogbuddy]

Where is the "else" statement part of the formula?


= IIf([CERTIFICATE ON FILE]=True, Sum([blah blah blah],""))

You don't have to have an else....(it is optional-no different than a normal If/end if statement) .....it is, however, a good idea to have the else part if there is a definite action that is done if the condition in the if statement is not met.

[Nathan H]

You don't have to have an else....(it is optional-no different than a normal If/end if statement) .....it is, however, a good idea to have the else part if there is a definite action that is done if the condition in the if statement is not met.

My mistake, I thought it was required on an IIF statement and optional on an IF...

[puppydogbuddy]

it tells me Syntax Error (missing operator)

I've also tried that with and extra set of () around Cert on file and true

Please post the actual IIf statement you used. Thanks.

[missinglinq]

Nathan is correct, IIF requires all three arguments!

IIf(expr, truepart, falsepart)


You have to have a falsepart.

And what kind of field is [CERTIFICATE ON FILE]? If it's a text field the True should be "True"

Welcome to TheScripts!

Linq ;0)>

[puppydogbuddy]

Nathan is correct, IIF requires all three arguments!

IIf(expr, truepart, falsepart)


You have to have a falsepart.

And what kind of field is [CERTIFICATE ON FILE]? If it's a text field the True should be "True"

Welcome to TheScripts!

Linq ;0)>

Linq,

That is really strange because I have used the IIf many times without a false part and it worked fine. I wonder how it works? I suspect in this case, he probably does need a false part because he is summing a column of numbers and omitting the false part would be like summing some null or text values.

it should probably be ,

IIf([CERTIFICATE ON FILE] = True, Sum([blah blah]), 0)

[missinglinq]

What version are you running? It's definately required in Acc2003! It fails without a falsepart even if you're only doing a simple assignment, like

Me.MyTextContro l = IIf(Amount > 1000, "Large Order!")

and generates the error "Argument Not Optional."

Linq ;0)>

[puppydogbuddy]

What version are you running? It's definately required in Acc2003! It fails without a falsepart even if you're only doing a simple assignment, like

Me.MyTextContro l = IIf(Amount > 1000, "Large Order!")

and generates the error "Argument Not Optional."

Linq ;0)>

I am still using Access2000....t hat must be it......I seem to remember reading somewhere about a change in the IIf syntax after version 2000.

[hrford]

Sorry guys I had a computer free couple of days.

the way I have the exp written now is just to sum everying thing so it reads like this:

=Sum([Practice Management]+[skills]+[other])

What I want it to read is

=IIf[(certificate on file)=True], =Sum([Practice Management]+[skills]+[other]

Now if what I've read is correct I need to tell it that if certficate doesn't equal true then 0 needs to be summed in. How do I do that???

and thank you all sooo much!

[hrford]

I just tried this:

=IIf([CERTIFICATE ON FILE]=True), =Sum[bla blah], 0

I also tried this

=IIF(CERTIFICATE ON FILE]=True), =Sum[blah blah], IIf(CERTIFICATE ON FILE]=False, 0

Both times it told me that I didn't have the correct # of operators....

[puppydogbuddy]

Sorry guys I had a computer free couple of days.

the way I have the exp written now is just to sum everying thing so it reads like this:

=Sum([Practice Management]+[skills]+[other])

What I want it to read is

=IIf[(certificate on file)=True], =Sum([Practice Management]+[skills]+[other]

Now if what I've read is correct I need to tell it that if certficate doesn't equal true then 0 needs to be summed in. How do I do that???

and thank you all sooo much!

Try this:
=IIf([certificate on file]=True, Sum([Practice Management]+[skills]+[other]),0)

[hrford]

Try this:
=IIf([certificate on file]=True, Sum([Practice Management]+[skills]+[other]),0)

I then get

"You may have entered a comma without a precding value or identifier"

this is really annoying!