Date calculated based on text field

[arbucklemw]

I'm trying to figure out if it's possible to have Access calculate a date field based on the contents of a text field and another date. I have done this with Excel with "If/then/or", easy enough, but haven't been able to figure out if it's possible in Access.



I have 3 possible entries for the text field, CAR, HAZ, PAS.

I'll have a start date that will be entered by the user.

I then want Access to calculate a due date field based either 30 or 45 days out from the user entered date, 30 days out if either HAZ or PAS is in the text field being used, and 45 days out if CAR is in the text field for the record.

Can this be done in Access?

Or, would it be easier to have a number field associated with each text entry above that represents the 3 options, then have the date calculated using the DateAdd() function somehow?

Thanks!
Michael

[orange]

Are these business/working days, or calendar days, and does it ignore/process holidays?

Perhaps (untested)

dueDate = Dateadd("d",iif(userSelected= "Haz" or userSelected = "Pas",45,30),EnteredDate)

if Car is the only other option to Haz,Pas

[arbucklemw]

Straight calendar days, and not ignoring holidays. Every day counts. Thanks!

[arbucklemw]

Driving myself nuts between Googling this and flipping through The Missing Manual.

[orange]

A good reference to Access functions with examples.

[arbucklemw]

Thanks again!

[arbucklemw]

One more. How can I have Access pull a date from another field to be used as part of the DateAdd function? I've tried multiple variations of the below with no luck. The field i'm trying to pull the date from is the DateReceived field.

DateAdd ("d", 30, #[DateReceived]#)

DateAdd ("d", 30, [DateReceived])

DateAdd ("d", 30, #DateReceived#)

and more. No joy.

[orange]

If this is on the form you can try Me.DateReceived.

Or show us a jpg of the form, or post a copy of the database(remove anything personal first)