-
1. Re: Need help on Dates issue
Yes it should be two fields of type date. There should also be a date field or a calculation that returns a date for the camper's birthday.
Your calculation for Birthday might be:
If ( StartDate < Birthday and EndDate > Birthday ; "x" )
-
2. Re: Need help on Dates issue
Hello Again.
I have another person here at our office helping with improving our Outdoor Education database and we had been using the above calculation to get an X to be placed in a field if a student's birthday falls within the week they are at Outdoor Education and it was working fine. The fields of the Beginning and Ending week of Outdoor Education got changed from TEXT to DATE format and now the calculation does not work. One other problem we were seeing is that when we import the school information from EXCEL, if a student's birthday is 12/01/03 in the spreadsheet, it would come over as 12/1/2003. So if the student is at outdoor ed during the week of 12/09/14 to 12/13/14, it would put an X in the Birthday Party field, which is incorrect. So now, we cannot the format to be the same as mm/dd/yy as in the EXCEL spreadsheet, it does not put an X in the Birthday Party field and it acknowledges incorrect birthdays because of the format of date. We tried changing it back to how it was previously but
So, I don't know how to fix it or what changed it to be totally not working now. Also, is there a way to write a calculation to reformat the date to be mm/dd/yy?
Thank you in advance.
-
3. Re: Need help on Dates issue
The fields of the Beginning and Ending week of Outdoor Education got changed from TEXT to DATE format and now the calculation does not work.
That makes no sense. The fields should be of type Date and I would not expect them to work if they are of type text. If these dates are stored as type date, the display format does not affect how the date evaluates. (Dates are stored as integers counting the number of days from 12/31/000 to the date shown in the date field so they evaluate the same regardless of the display format.)
-
4. Re: Need help on Dates issue
Hello Again,
I have this field set up but I need for the calculation formula to only look at month and day because if inadvertently someone enters current year as birthdate, it puts an X in the Birthdate field. Can you revise the formula to only look at month and day?
-
5. Re: Need help on Dates issue
For some reason, you posted a question and then closed the thread to block posting comments which would prevent anyone from answering your question. Fortunately, as community leader, I can re-open a closed thread.
It's better to prevent a data entry error than to modify code to handle the error. I'd put a validation rule on the birthdate field that rejects dates where the year is this year.
-
6. Re: Need help on Dates issue
if the current calculation reads: If (SMCOE_OUT_Week_Number::OE DatesBeg ≤ Birthdate and SMCOE_OUT_Week_Number::OE DatesEnd ≥ Birthdate ; "X"), how do I add a validation to it to not allow current year?
-
7. Re: Need help on Dates issue
The validation would be added to the BirthDate year, not this calculation.
Year ( self ) < Year ( Get ( CurrentDate ) )
This would be added via the calculation option found on the validation tab in field options for the BirthDate field.
-
8. Re: Need help on Dates issue
Now I screwed something up. I added that exact phrasing to the Birthdate field under Calculation and it wiped out the birthdate entirely. I am just lost on this thing.
-
9. Re: Need help on Dates issue
We are dealing with two fields here: One that holds the student birthdate (birthdate) and one that puts the X in if their birthdate falls in the week they are at Outdoor Ed (Birthday). Which one gets this calculation written into it? I am confused! Right now, Birthday has the calculation of putting the X in that field if the Birthdate falls between OE Beginning Date and OE Ending Date. So, I am guessing from your previous instruction that the new formula goes in the calculated area for Birthdate? If so, that took away all my actual birthdate info. I must have written something wrong.
-
10. Re: Need help on Dates issue
This is a VALIDATION field option, NOT an AUTO-ENTER option.
Make sure that you select the calculation option on the Validation tab, not the auto-enter tab.
-
11. Re: Need help on Dates issue
Okay I see where you are talking about. So I put that exact formula in that area, and it is not working. Is the word SELF supposed to be there or is that a reference for the field name?
Currently, I have a calculation formula telling Birthday to place an X in the field if Birthdate is between OutDoor Ed Beginning and Ending Dates. Then I have a validation calculation in Birthdate saying that Birthdate = Year ( Self ) < Year ( Get ( CurrentDate ) ). I don't see where this formula refers to the beginning or ending dates of the outdoor ed week.
-
-
13. Re: Need help on Dates issue
You appear to be confusing which field needs the auto-enter calculation. You have a calculation that returns an "X" if the camper's birthday falls on the current week. You have a date field that stores the camper's birthdate. I am suggesting that you leave the calculation field unchanged, but to find the Birthdate field in Manage | Database | Fields, double click it to open Field Options and enter the suggested validation field option on the Birthdate field.
You should enter it exactly as written. "Self" is a term you can use in calculations to refer to the current field without having to explicitly name it. If you replace self with the name of your field, the calculation evaluates exactly the same.
But what may also be a source of confusion is that this validation rule only kicks in when you enter or edit data in the birthdate field by popping up an error message that asks the user to fix the error. If you have existing records with the wrong year already entered into the database, this validation rule will have no effect on them unless you edit those fields in some way.
But note that you can easily perform a find for all records where the year is this year, or just a few years earlier than today--thus finding all records where the camper's age is impossibly young.