Date range to determine another field value 1

[d22]

I am creating a Access DB for a Little League registration and would to put a value in a field determined by the applicants DOB. I want to write a query to put the value Pony in the league field if the date is between 12/1/96 and 12/31/00 for example. I will need to for 6 different divisions meaning if date between XXXX and XXXX update table league set division = Mustang where DOB between xxxx and xxxx, and so on. Thank you in advance for your time and knowledge. D.

 

[PaulBricker]

You can put this right on the Field Line of your query. You will have to delimit your dates with the # sign.

MyLeague:IIf(DOB > #12/1/96# And DOB < #12/31/00#,&quot;Pony&quot;,IIf(DOB> #XXXX# And DOB < #YYYY#,&quot;Mustang&quot;,IIf(DOB > #XXXX# And DOB < #YYYY#,&quot;TBall&quot;,IIf(DOB > #XXXX# And DOB < #YYYY#, &quot;SlowPitch&quot;,IIF(DOB > #XXXX# And DOB < #YYYY#,&quot;LittleLeague&quot;,&quot;AllStars&quot))))

You can try the Switch function as well. It might be a little cleaner but with all the comparisons this seemed easy enough.

Paul

 

[PaulBricker]

You can simplify this a little by putting your date comparisons in incrementing order.

MyLeague:IIf(DOB > #12/1/96# And DOB < #12/31/00#,&quot;Pony&quot;,IIf(DOB < #C/C/CC#,&quot;Mustang&quot;,IIf(DOB < #D/D/DD#,&quot;TBall&quot;,IIf(DOB < #E/E/EE#, &quot;SlowPitch&quot;,IIF(DOB < #F/F/FF#,&quot;LittleLeague&quot;,&quot;To Old&quot))))

Paul

 

[d22]

Paul, sorry for the delay, I have been on a business trip. The code is cool. Could I add this to a form, so when the DOB is input, the Division field is populated with the correct value automatically. Obviously it will use the query information to evaluate and populate correctly. Thank you in advance for your time.

 

[PaulBricker]

If you put this right in a new column in the underlying query then you can just add a textbox to your form and set the Control Source to
MyLeague
I think that would be the best way to use it. Then the values become part of the underlying Recordset and when you add a new DOB, it will automatically compute the League.

By the way, you can change the alias MyLeague to anything you want.

Paul

 

[d22]

Thank you so much for your assistance. I plan to try it this weekend, but I am sure it will be fine. D.

 

[PaulBricker]

I'm away all next week with family and golf clubs but no computers. I'm sure someone will help if there is a problem.

Good luck.

Paul

 

[d22]

Paul,
I created the text box but wasn't sure what you meant by putting the value in a column in an underlying query? I tried to put this in a VB script with a DoCmd.RunSQL, but it failed obviously. If you don't mind can you walk me through it? I was going to try to write a query using it then statements, but like your code better. Thanks.

 

[Onyxpurr]

Another thing you can do is create a table for the each league. Something like tbl_Leagues with fields: name, startdob, enddob

Then when you do queries you could do:

SELECT table1.name, table1.dob, tbl_Leagues.name
FROM table1, tbl_Leagues
WHERE (((table1.dob)>=[startdob] And (table1.dob)<=[enddob]));

Or for a field in a form:

DLookup(&quot;[name]&quot;,&quot;tbl_League&quot;,&quot;[startdob]<=#&quot; & dob & &quot;# and [enddate]>=#&quot; & dob & &quot;#&quot

 

[d22]

I will play around with the DLookup. I want something that will evaluate the DOB then fill in the appropriate value, so kids don't get put in the wrong league. This is mainly for evaluations and the draft. Thank you for the ideas. I really appreciate all of the feedback. D.

 

[PaulBricker]

If you want to try this in the query, you just put the expression on the Field line of the query. The field will have the name MyLeague (which you can change to anything you want)and then in the form, you set the control source of a textbox to the field MyLeague. Then, any time a DOB is entered your MyLeague textbox will populate automatically with the league.
If you have problems, post the expression and SQL from your query and we should be able to clear it up.

Paul

 

[d22]

Paul,
I created the test record with the control set of Division. I created a query with the following information Division:IIf(DOB > #8/1/89# And DOB < #7/31/91#,&quot;Pony&quot;,IIf(DOB < #7/31/93#,&quot;Bronco&quot;,IIf(DOB < #7/31/95#,&quot; Mustang &quot;,IIf(DOB < #7/31/97#, IIf(DOB < #7/31/99#, &quot;Instructional&quot;, IIf(DOB > #8/1/99# And DOB < #7/31/01#,&quot;T-Ball&quot;,IIF(DOB < #7/31/89#,&quot;To Old&quot))))

I also have a field called Division in the table tblPlayers. I guess I am not experienced with Access enough to know how the DOB talks to the query then populates the field. To I have to trigger it within the DOB properties like through the on exit event? Thank you for your time. D.

 

[PaulBricker]

D, a query constantly updates information without any type of addtional procedure so if you had the query open and filled in the value for DOB directly in the query, the field Division would fill in automatically without you doing anything else. The problem is this method of calculating isn't designed to be stored in the Table. If you want to store the value in the Division field in your table we have to do the calculations in a different place (same expression but we would have to run them thru an event procedure in the form).
In order to give you the best info, let me ask you this. If a player, say Mary Smith, has a birthday May 24 and goes from age 12 to 13 the current set up will change the League she is in from Pony to Bronco, but I assume that she would not move to the next league until the following season. Also, how do you handle returning players. Do you completely reregister them and enter all the info, including DOB as a new record in your Players table? Let me know and I think I can give you an appropriate way to handle it.


Paul

 

[d22]

Paul, The date range is defined by Pony at the beginning of the year. The range on my last post is for next year (2004). I don't plan to re-register players on the DB each year, so I would need a method that could change dynamically each season. Your assumption is correct, that once Mary is assigned to a Division she will play the entire season in that division. Do you think manually running an update query before each season would be a better idea? You have been a great help, thank you. Happy holiday. D.

 

[PaulBricker]

If you don't mind, I'd like a little more info. The problem I'm seeing now is that you will need to rewrite this expression each year inorder for it to be valid. To avoid that I need to understand the cut off dates a little better. Can you explain it by age. That is &quot;If Mary Smith is 13 by such and such a date then she plays in the Pony League other wise she plays in the Bronco League. I've got the expression written, I just need to clarify the dates that things cut off/change.

Paul

 

[d22]

You are correct the expression will have to be changed yearly. The way it works is if Mary Smith was born on 8/2/89, then you would be in the Pony Division for the entire year next year, as per Pony rules. Pony for next year spans from 8/1/89 - 7/31/91. A twist in the whole thing is that the league I volunteer for has a Fall season, which Pony doesn't calculate in their rules. I don't mind changing the expression twice a year. The league have treated the entire process as a paper based system, which as you know could cause a lot of problems. Thanks again Paul. Let me know if you need any further information.

 

[PaulBricker]

Well then this expression should work but look at the part in BOLD. You have an IIF with no value in the true arugment (like Bronco or Pony), then you have another IIF following it immediately. You will have to adjust that.

Division:IIf(DOB > #8/1/89# And DOB < #7/31/91#,&quot;Pony&quot;,IIf(DOB < #7/31/93#,&quot;Bronco&quot;,IIf(DOB < #7/31/95#,&quot; Mustang &quot;,IIf(DOB < #7/31/97#, IIf(DOB < #7/31/99#, &quot;Instructional&quot;, IIf(DOB > #8/1/99# And DOB < #7/31/01#,&quot;T-Ball&quot;,IIF(DOB < #7/31/89#,&quot;To Old&quot)))))

So, put this expression on the Field line of a blank column in your query. Then just run the query. You may have to adjust some > and < values to include the ='s operator so your dates work, but run it for a shake down and see how it looks in the query for values returned.
Let me know. I think we are geting there.

Paul

 

[d22]

Paul - Good catch, that extra iff was a typo on my part. I ran the query and it worked, but it didn't update the record. I have a Division query with the query you sent me and a test box with the control source of Division. How do I get it to update after I input the DOB? I know how to do this in Oracle, I am a little green in Access. Thank you for your time.

 

[PaulBricker]

You've got me a little confused so let me go one step at a time. You have a table called tblPlayers. For right now, there should be NO field in that table called Division. If there is, delete it. Create a query using tblPlayers and add the fields you want to see on your form including the field DOB. Now in a new column of the query, on the Field line, put your expression for Division. You can now create a form from this query using the Form Wizard. Add all the fields from the query to your form. When you go to enter a new DOB or change and existing DOB, the Division will change automatically. YOu should also be able to just add a record directly to the query and as soon as you tab out of the DOB field, your Division field will update.
Let me know how it goes.

Paul

 

[d22]

Paul - I was trying to update a field, but have re-thought the process. Your idea is killer and worked well. Thank you for all of your assistance and time. I have to go play Thanksgivings now, but I am sure I will post again as I continue to create the database. I was looking for a demo of a registration database that included some type of fees, because my table is not only going to track, players, seasons, it is also going to track refundable fees by family. Thanks again Paul, your assistance has been quite helpful. Take care. D.