1 of 1 people found this helpful
Personally I would keep the date field with the calendar as a straight date field and set the EDITDATE field as a calc reflecting the date field. I'm a fan of using Let () for these sorts of calcs, such as:
Let ( [
date = yourDateField ;
theYear = Year ( date ) ;
lengthM = Length ( Month ( date ) ) ;
lengthD = Length ( Day ( date ) ) ;
theMonth = If ( lengthM = 2 ; Month ( date ) ; "0" & Month ( date ) ) ;
theDay = If ( lengthD = 2 ; Day ( date ) ; "0" & Day ( date ) ) ] ;
theYear & theMonth & theDay )
Why don't you use the date formatting options to display the date in that fashion?
I agree with Keywords.
In addition, it would be helpful for us (and for you) if you were much clearer about identifying several things:
Is your field DateFIeld actually defined as a date field?
Undertand that you can use the layout tools to make the APPEARANCE of this field to look a certain way
(YYY-MM-DD or DD-YYYY-MM or dayName, YYY-MM-DD) etc
Note that none of these layout-appearance tools have anything to do with the actual data content of the field.
In particular trying to calculate Left(myDateField; 4) will not be different for any of the display formats shown above.
If you're going to to calculations on a date field they need to use the date function such as year( myDateField) or dayName( myDateField); etc.
The same descriptive detail needs to be included when you talk about EDITDATE.
Is this a text field? Number field? It cannot be a date field, given your description of how you are using it.
Good points all, Bruce. I should have said that in the calc I posted the calc result should be set to text.
Hope it is clear that my comments were intended for Bart and his explanations of what he is trying to do and where all the pieces fit and which piece he may be talking about at any particular point.
I think if the date>999/12/31, using number field makes things a bit simpler.
date = yourDateField ;
Year ( date ) * 10000 + Month ( date ) *100 + Day ( date )
This result can be text.
Usually the date formatting options malcolm said may be the simplest.
A case need the calc field is to make a relation with another field that already formatted.
Thank you for the information and explanation.
@Bruce, you're right i should have explaned it somewhat more what i was trying to. The field where the data via calendarpicker was used is of course a datefield, the EDITDATE is a textfield which is only used for export to a third party who demand this format. For calculations concerning the dates in other parts of the database i use the datepicker field
@Keywords Thank you again for your reply this also works great.
@User 19752 This was for me the most clean solution thank you
Much Much Oblidged