Calculated field in a query/table based on a calculated field (1 Viewer)

So, I guess I can't do this. I have calculated fields in my table (DateAddW) and I'm trying to create more calculated fields based on those calculated fields, but it seems it can't be done.
Is there a better way?
I don't really understand functions so I need all the help I can get!!!

What I'm trying to do is something like this:

Field 1 - Type

Field 2 - Date received

Field 3 - Date Processed

Field 4 - Projected Date Processed: IIf(Not IsNull[Date Processed]),"",IIf([Type]=x,DateAddW([Date Processed],5),IIf([Type]=y,DateAddW([Date Processed],15))))

Field 5 - Date Filed

Field 6 - Projected Date Filed: IIf(Not IsNull[Date Filed]),"",DateAddW([Projected Date Processed],30)))

Field 7 - Date Approved

Field 8 - Projected Date Approved: IIf(Not IsNull[Date Approved]),"",IIf([Type]=x,DateAddW([Projected Date Filed],45),IIf([Type]=y,DateAddW(Projected Date Filed],30))))

Etc.

I can make it work in a form, but then I can't write queries based on the calculated fields from it. Sigh.

HELP!!!!

Remember in 5th grade when you asked your math teacher 'When will I ever need to use Algebraic Substitution'? (http://www.regentsprep.org/regents/math/algebra/ae3/algsys.htm) Well today's the day.

Instead of using the calculated fields to determine Projected Date Approved, use the underlying calculations. It will work, but it's going to make your code horrible to read, so maybe create functions to calculate them.

Thanks, Plog.

I tried and I kept getting "query too complicated" messages. That's why I was trying the other way. It worked on the form...

It's the using functions part that I don't get. Not the logic part, but how I use them in Access. I don't know how to make the equation a function and use the function in the table. Is there a crash course link that you would recommend?

Thanks again!

I'm sure if you google it you will find something out there, but its pretty simple to create a function and use it. It looks like you didn't provide actual code for how to calculate [Projected Date Processed] so I will just give you an example to get you started:

In the Access ribbon click on Create, in the Other section select Module (it may say Macro by default, click on that instead and then on Module). A module screen will appear with 'Option Compare Database' at the top. Underneath that, paste this code:


Then to use it in a query you would do this:

ProjectedDateProcessed: get_ProjectedDateProcessed([Date Processed], [Type])

Of course your logic inside the function will be different

You are so awesome! Thank you!!! I'm going to give it a shot.

I've been playing with this and it's pretty cool. I have a couple of questions...

I don't want the default to be "", I only want the projected date to be "" if there is a completed date. (If there is a Date in the Date Processed field I don't need a Projected date and it can be "".) How does that affect the function code?

Also, I have been using DateAddW([FieldName],number) instead of DateAdd("criteria",number,[FieldName]) because I need weekdays only calculated. I already have a module (that I copied and pasted from online) for that. Can I use it in these functions? Which leads to my next question...

Since I want to use fields that already exist in the query do I just use the field names instead of the dp and t used as examples in your formula? Like, do I type [Date Processed] or whatever my field name is?

I know this is probably beyond stupid for someone like you, but I would love to have a basic understanding of how this works!

Thanks so much for helping a newbie out!