Calculation question

K

KenRamoska

Hi,
Below I have an excel equation that has served me well.
I need to put this into an Access query now.
Basically I take a given date and then add days to it based on the answer to
2 questions. Do they need core and What program.
So if they need Core and they are NSM then add 35 days....etc.
Please see below in Excel and if possible tell me how to get this into Access.
Thanks in advance
Ken

=IF(AND(D4="NPM",E4="Y"),C4+70,IF(AND(D4="NPM",E4="N"),C4+35,IF(AND(D4="CSM",E4="Y"),C4+84,IF(AND(D4="CSM",E4="N"),C4+49,IF(AND(D4="PM",E4="Y"),C4+105,IF(AND(D4="PM",E4="N"),C4+70,""))))))
 
D

Douglas J Steele

Rather than If, Access uses IIf (Immediate If). Rather than And(cond1,
cond2), Access uses cond1 And cond2

That means you'd have something like:

=IIF(D4="NPM" AND E4="Y",C4+70,IIF(D4="NPM" AND E4="N",C4+35,IIF(D4="CSM"
AND E4="Y",C4+84,IIF(D4="CSM" AND E4="N",C4+49,IIF(D4="PM" AND
E4="Y",C4+105,IIF(D4="PM" AND E4="N",C4+70,""))))))

However, the D4, E4 type references don't make sense in Access: you need to
refer to the names of the actual fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KenRamoska said:
Hi,
Below I have an excel equation that has served me well.
I need to put this into an Access query now.
Basically I take a given date and then add days to it based on the answer to
2 questions. Do they need core and What program.
So if they need Core and they are NSM then add 35 days....etc.
Please see below in Excel and if possible tell me how to get this into Access.
Thanks in advance
Ken
Click to expand...
=IF(AND(D4="NPM",E4="Y"),C4+70,IF(AND(D4="NPM",E4="N"),C4+35,IF(AND(D4="CSM"
,E4="Y"),C4+84,IF(AND(D4="CSM",E4="N"),C4+49,IF(AND(D4="PM",E4="Y"),C4+105,I
F(AND(D4="PM",E4="N"),C4+70,""))))))
 
K

Kernow Girl

Hi Ken - try just changing IF to IIF - I did not go over each step so might
or might not work - the only thing that might need to be changed are the AND
stmts, but I'm just leaving work now. Is worth a try.
Yours - Dika (KG)
 
K

KenRamoska

Doug,
Thank you so much I will try that now and post back my results.
Sincerely,
Ken
 
K

KenRamoska

Doug,
I put this as a field in a query. I get #error
Should I have put this in a form and used an AB box and have
it calculate on the fly?
I would prefer to see it in the query though.
Ken


ProjEndDate: IIf([Program]="NPM" And
[NeedCore]="Y",[StartDate]+70,IIf([Program]="NPM" And
[NeedCore]="N",[StartDate]+35,IIf([Program]="CSM" And
[NeedCore]="Y",[StartDate]+84,IIf([Program]="CSM" And
[NeedCore]="N",[StartDate]+49,IIf([Program]="PM" And
[NeedCore]="Y",[StartDate]+105,IIf([Program]="PM" And
[NeedCore]="N",[StartDate]+70,""))))))
 
D

Douglas J Steele

What's NeedCore: text, or a Yes/No field? If it's a Yes/No field (more
properly referred to as a Boolean field), replace "Y" with True and "N" with
False (no quotes)
 
K

KenRamoska

Yes it is a Yes/No field.
Here it is now....I put this in an unbound box on the form.
When I reopen the the last few True and Falses have "" around them.
Access put them in on some but not all.????
I was checking other posts and someone said And does not work in Access?
Is this true?
ken

=IIf([Program]="NPM" And [NeedCore]=True,[StartDate]+70,IIf([Program]="NPM"
And [NeedCore]=False,[StartDate]+35,IIf([Program]="CSM" And
[NeedCore]=True,[StartDate]+84,IIf([Program]="CSM" And
[NeedCore]=False,[StartDate]+49,IIf([Program]="PM" And
[NeedCore]=True,[StartDate]+105,IIf([Program]="PM" And
[NeedCore]=False,[StartDate]+70,""))))))

Douglas J Steele said:
What's NeedCore: text, or a Yes/No field? If it's a Yes/No field (more
properly referred to as a Boolean field), replace "Y" with True and "N" with
False (no quotes)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KenRamoska said:
Doug,
I put this as a field in a query. I get #error
Should I have put this in a form and used an AB box and have
it calculate on the fly?
I would prefer to see it in the query though.
Ken


ProjEndDate: IIf([Program]="NPM" And
[NeedCore]="Y",[StartDate]+70,IIf([Program]="NPM" And
[NeedCore]="N",[StartDate]+35,IIf([Program]="CSM" And
[NeedCore]="Y",[StartDate]+84,IIf([Program]="CSM" And
[NeedCore]="N",[StartDate]+49,IIf([Program]="PM" And
[NeedCore]="Y",[StartDate]+105,IIf([Program]="PM" And
[NeedCore]="N",[StartDate]+70,""))))))
Click to expand...
Click to expand...
 
K

KenRamoska

I saw someone add a null on another site.
I added NULL to the end and for some reason it now works perfectly!!
Thanks so much Doug
here it is.
Test: IIf([Program]="NPM" And
[NeedCore]=True,[StartDate]+70,IIf([Program]="NPM" And
[NeedCore]=False,[StartDate]+35,IIf([Program]="CSM" And
[NeedCore]=True,[StartDate]+84,IIf([Program]="CSM" And
[NeedCore]=False,[StartDate]+49,IIf([Program]="PM" And
[NeedCore]=True,[StartDate]+105,IIf([Program]="PM" And
[NeedCore]=False,[StartDate]+70,Null))))))

Douglas J Steele said:
What's NeedCore: text, or a Yes/No field? If it's a Yes/No field (more
properly referred to as a Boolean field), replace "Y" with True and "N" with
False (no quotes)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KenRamoska said:
Doug,
I put this as a field in a query. I get #error
Should I have put this in a form and used an AB box and have
it calculate on the fly?
I would prefer to see it in the query though.
Ken


ProjEndDate: IIf([Program]="NPM" And
[NeedCore]="Y",[StartDate]+70,IIf([Program]="NPM" And
[NeedCore]="N",[StartDate]+35,IIf([Program]="CSM" And
[NeedCore]="Y",[StartDate]+84,IIf([Program]="CSM" And
[NeedCore]="N",[StartDate]+49,IIf([Program]="PM" And
[NeedCore]="Y",[StartDate]+105,IIf([Program]="PM" And
[NeedCore]="N",[StartDate]+70,""))))))
Click to expand...
Click to expand...
 
D

Douglas J Steele

And works fine in Access.

What exactly are Program and NeedCore: fields in a recordset, or controls on
the form?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KenRamoska said:
Yes it is a Yes/No field.
Here it is now....I put this in an unbound box on the form.
When I reopen the the last few True and Falses have "" around them.
Access put them in on some but not all.????
I was checking other posts and someone said And does not work in Access?
Is this true?
ken

=IIf([Program]="NPM" And [NeedCore]=True,[StartDate]+70,IIf([Program]="NPM"
And [NeedCore]=False,[StartDate]+35,IIf([Program]="CSM" And
[NeedCore]=True,[StartDate]+84,IIf([Program]="CSM" And
[NeedCore]=False,[StartDate]+49,IIf([Program]="PM" And
[NeedCore]=True,[StartDate]+105,IIf([Program]="PM" And
[NeedCore]=False,[StartDate]+70,""))))))

Douglas J Steele said:
What's NeedCore: text, or a Yes/No field? If it's a Yes/No field (more
properly referred to as a Boolean field), replace "Y" with True and "N" with
False (no quotes)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KenRamoska said:
Doug,
I put this as a field in a query. I get #error
Should I have put this in a form and used an AB box and have
it calculate on the fly?
I would prefer to see it in the query though.
Ken


ProjEndDate: IIf([Program]="NPM" And
[NeedCore]="Y",[StartDate]+70,IIf([Program]="NPM" And
[NeedCore]="N",[StartDate]+35,IIf([Program]="CSM" And
[NeedCore]="Y",[StartDate]+84,IIf([Program]="CSM" And
[NeedCore]="N",[StartDate]+49,IIf([Program]="PM" And
[NeedCore]="Y",[StartDate]+105,IIf([Program]="PM" And
[NeedCore]="N",[StartDate]+70,""))))))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need help on MAX 12
Nesting IIf functions 1
Nested IF formula question 3
Sumproduct across mutliple columns 3
Timesheet formula problem 3
Adding a big matrix in Word 2013 5
#VALUE error add'g formulas 2
TOUGH FORMULA QUESTION - PLEASE HELP! 3

Top