--------------------------------------------------------------------------------

On my form, I need to be able to have Access autocalculate a 3-month followup:

This is what I have so far:

My field "Web Reviewed Date" and an unbound text box for 3-month follow-up (next review date in my main table)

In the unbound textbox control source, I have put the code =DateSerial(Year([Web Reviewed Date]),Month([Web Reviewed Date])+3,Day([Web Reviewed Date])).

This works just fine.

But I cannot figure out how to tie the unbound text box into my main table for reporting and querying. Please help!

Hi,
you wouldn't store this value...you just calculate it on the fly as you are already doing e.g. in a query you can use a new expression:

DueDate: DateAdd("m",3,[Web Reviewed Date])

HTH
Good luck

Thank you! I'll try it. I apologize for the double post. I didn't think the first post took. Thank you again for your help.

Freakazeud is right but if you really did have a need to store it, you could do something like this.

Private Sub Form_Current()
Me.SomeDate = DateSerial(Year([Web Reviewed Date]), Month([Web Reviewed Date]) + 3, Day([Web Reviewed Date]))
End Sub

SomeDate would be the name of the field in your table that you want to display the future date.

You're welcome.
Good luck on future projects!

There is no reason to store this value and it shouldn't be done!

Agreed. Theres rarely a time that you would ever need to store a calculated value like that. The only time that I could think of off the top of my head where you might need to store it is if you're doing snapshots in time where you want to track the progress/changes that occur, which I've done in the past where the data would be placed in a separate table from the live data entry one in order to perform comparisons such as seeing how many times the dates were changed, what they were changed to, # of different personnel being assigned, Promise Ship Date changes (Similar to your calculation - based off of a future date, which would often change)

Still don't think you would need to store the calculated dates.

Forgive me for being dumb but I can't get either of these options to work.

If I take Freakizud's suggestion, DateAdd("m",3,[Web Reviewed Date])

I put DateAdd("m",3,[Web Reviewed Date]) in my query criteria field on the "or" line as I have some records with data already in the "Next Review Date" field. This returns all of my records, but does not update the date.

If I put DateAdd("m",3,[Web Reviewed Date]) in the "Criteria" field with the other criteria for the query, I get less than half of the records - and with the correct date returned.

If I put DateAdd("m",3,[Web Reviewed Date]) in bot the criteria and the or fields, I get approx half of the records, again with the correct date.

I also tried the VBA suggestion: But couldnt get that to work at all. It wouldn't fill in the date.

Can you help me figure out what I am doing wrong?????

Hi,
you wouldn't want to use it in the criteria at all. Criteria sections are for dimming down records. I thought you want to display the duedate as a seperate expression so in an empty field in query design view use:

DueDate: DateAdd("m",3,[Web Reviewed Date])

If you now run the query it should show you all the fields you had specified in the design grid as well as the new one which is the calculated expression with the correct dates.
HTH
Good luck

Wow, that worked! Thank you!

Freak,

I agree, it's not a practiced standard but it's a needed solution at times. The best example that I could give you would be in distribution and stock inventory. The Enterprise solutions out there such as JDE Edwards, IFS etc. do not have a way to track backorders. Once an item/order is off a backorder status, theres no record that it was ever even on backorder. This is where outside applications like Access come into play where there are snapshots of the backorders taken to track all the statuss' they go through, the # of days they're on backorder, the # of days it takes them to ship from the time they come available etc.

I don't know what type of industry you work in but from my experience anyway, I've found that from time to time you'll run across some business processes where you need to use innovative approaches that often go against recommended practices. Tell you what, I couldn't put a price on how valuable UtterAccess has been for me. So many times, I've come across something that I was sure that nobody else would've ran across before but lo and behold, post it in here and sure enough someone knew exactly what I was talking about no matter how obscure it was.

You're welcome.
Good luck on future projects.

That's a different example then you gave earlier though. I was commenting on that example.

Sorry, the one I was talking about earlier was one that I had to do not too long ago but the backorder one in my experience is by far and a way the most common.

...and as I mentioned I still wouldn't have stored the calculations in that case.

To each their own, Freak, which is the nice thing about development. Everyone has their own methods of development and setup, sure theres basic standards but you always have that individual freedom. As I said, just from my experience from working with the tracking of backorders for a number of years, I've found that to be the best way to do it as have others on my development teams at various places I've worked. Is there an alternate way of doing it? I'm sure there is, theres solutions for everything pretty much.

Good book by the way.