Unbound Fields in Subform

[Precips1]

Hi - I have a subform which stores 2 bound fields and 2 that are populated via an iif statement based on the 2 results from the bound fields.

What I'm trying to do logically sound quite easy - Getting access to do it is another thing.

Bound field 1 = [date1]
Bound field 2 = [course length] ie 5 as in 5 (yrs) or 1825 days from [date1]

unbound field 1 then looks at the [date1] and adds 5 (yrs) and becomes [expiry date] eg. [date1]+ 1825=5 yrs eg. [01/01/10] --> [01/01/15]

I know this is probably not the best way to do this but it works for me.

The problem comes when I then try to get another unbound field to then show an indate or expired date based on now().

This is what it looks like from above!

textbox placed on a form and within the textbox as the control source I have
=IIf([unbound]>Date(),"Live","Expired")

I can't understand - some of the fields work but some don't. There's about 20 different course dates within the form which I have list as a datasheet rather than a form. from the 20 only 2 picks up the result correctly?

Any ideas?

In advance Thanks

 

[RuralGuy]

Try doing the calculations in the RecordSource query for the SubForm instead and bind the resulting fields to the unbound controls.

 

[Precips1]

RuralGuy - Thanks for your comment. However, still gives me the same problem.

I've attached a small .jpeg for you to look at. I've now moved the calcs into the form recordsource query. Although looks a lot neater it still gives incorrect results. Do you think it sees the whole data as being one because I'm using the same textbox to view 20 lines, Mind you! It looks like its working on a couple of the fields.

Hopefully you'll take a look at the screenshot and see if that helps?

Thanks again

 

[RuralGuy]

Instead of:
=IIf([Date2]>Date(),"Live","Expired")
...I'm going to suggest:
=IIf(DateAdd("yyyy",5,[Date1])>Date(),"Live","Expired")

 

[Precips1]

RuralGuy - I did as you suggest and still it doesn't work. Infact, this way changes all the fields to expired.

Its been a full day stuck on this problem - Do you think I should look at doing away with this idea?

Mike

 

[RuralGuy]

How about doing a Compact and Repair and then zip up your db and post it. This should work so something else is going on and we cannot see it from here.

 

[Precips1]

OK see attached. Please note the problem lies within the Attendees Subform. Its all left abit messy at the minute but I know you get the gist..

Thanks

Mike

 

[RuralGuy]

My post #4 intended for you to use the 1st date (which I now know is RegistrationDate) which I called Date1.

 

[Precips1]

WOW! OK Sorry didn't quite understand first time. Now I see it working although I don't quite understand my mistake and how it works off the RegistrationDate - can you explain how it works?

Looks pretty neat though!

Thanks...full day on this and I knew it would be simple once we knew how

Mike

 

[boblarson]

Substitute This SQL for your Form's Query (sorry kept forgetting where it was going):

SELECT DISTINCTROW Registration.RegistrationID, Registration.AttendeeID, Events.EventName, Events.CourseValidForYrs, Registration.RegistrationDate, DateAdd("yyyy",Nz([CourseValidForYrs],0),[RegistrationDate]) AS Expr1, IIf(DateAdd("yyyy",Nz([CourseValidForYrs],0),[RegistrationDate])>Date(),"Live","Expired") AS Indate
FROM Events INNER JOIN Registration ON Events.EventID = Registration.EventID
GROUP BY Registration.RegistrationID, Registration.AttendeeID, Events.EventName, Events.CourseValidForYrs, Registration.RegistrationDate
ORDER BY Registration.RegistrationID;

 

[boblarson]

Oops again - I see that I just did the same as Allan but in the query instead of in the text box control source.

 

[RuralGuy]

WOW! OK Sorry didn't quite understand first time. Now I see it working although I don't quite understand my mistake and how it works off the RegistrationDate - can you explain how it works?

Looks pretty neat though!

Thanks...full day on this and I knew it would be simple once we knew how

Mike

You could easily have been fighting a timing issue. Your scheme needed to wait for the expiration date to be calculated in order to be correct. My scheme uses the RegistrationDate directly with no intervening calculation. Access might not be smart enough to wait for the results of the calculation.

 

[RuralGuy]

Happy New Year Bob. I recommended putting the calculation in the query as well.

 

[Precips1]

Hey guys! Its working...and it flippin looks great!

Have a top New Year and thanks for you help

regards
Mike

 

[RuralGuy]

Ahh...another satisfied customer.

 

[boblarson]

...and it flippin looks great!

By the way, I was going to mention that I thought your form looked pretty nice.

 

[vbaInet]

By the way, I was going to mention that I thought your form looked pretty nice.

Precips1 copyright the design quick

 

[Precips1]

Hiya Bob - Any chance you deleting the zip file database from the reply post

 

[boblarson]

Hiya Bob - Any chance you deleting the zip file database from the reply post

It has been done as requested.