Is the timestamp being used just for display purpose? If so then you can use something like GetAsDate ( GetAsText ( YourTable::Timestamp ) ) to pull the date out of the Timestamp and then use your Date Format options on that. If this is the case you can then display it as a Merge field and use GetAsTime ( GetAsText ( YourTable::Timestamp ) ) to pull the time portion out and show that as well. Or perhaps you can use these together in a Custom Function which returns Text having the date formatted the way you want. Your Custom Function may look like this with TS as the variable:
CF_FormatTimeStamp (TS) =
myDate = GetAsDate ( GetAsText ( TS ) );
myTime = GetAsTime ( GetAsText ( TS ) );
myDay = GetAsText ( Day ( myDate ) );
myMonth = MonthName ( myDate );
myYear = GetAsText ( Year ( myDate ) )
myDay & "-" & myMonth & "-" & myYear & " " & myTime
Hope this helps
Thanks. I looked up GetAsDate and I guess it is used in a calculation field. I have created scripts but I am not familiar with Calculations.
In looking at what you wrote I assume that this calculation is applied to a text record and refers to a timestamp record which I assume to be named TS as in your calculation? I just need a little clarification, thanks. -Derryl
There's a much simpler way to split a time stamp into separate date and time fields so you can format them:
Define a time stamp field called TS.
Define a calculation field cTStime
Select Time as its result type (drop down in lower left corner)
Define a calculation field cTSdate
Select Date as its result type.
In both cases, the name of the time stamp field is all you need to enter for the specified calculation. The result type setting does the rest.
You can also apply date and time formats directly to the time stamp field. Simply select the field in layout mode and select format | Date... Then select a time format. (For some reason the date format doesn't affect the time stamp field until you also specify a time format.)
Amazing! Thanks Phil, I never knew about having to do the secondary time format step. I just aways thought the format date for timestamp was broken! Thanks again. -Derryl
I thought it was "broken" too until I started playing with the settings to verify that my post was accurate.
Purely by accident, I discovered that if I applied both formats (date and time) to a time stamp field, I could format the displayed data. If I removed either format, changing it to "format as entered", the other format (date or time) no longer applied as well.
Phil, thanks with the help formatting the date field. The next part of the issue was creating a field generated by an (approval) button that says "11-Nov-09 - BG"
Basically a datestamp with the person's initials that authorized it. The problem is, is it can't be a date field and have the initials in it, and if it is generated by a script there is not customizing of the date. Or is there a way to generated custom dates in script? I tried your method in message #4 but no results. Any hints would be appreciated. Thanks. -dr
You can break down the date into it's parts and format the parts, building up a text result that displays what you ask. Alternatively, you can create the visual effect you describe with merge fields.
Use the text tool to start a block of layout text.
Choose Insert | Merge Field to insert the date field.
Type in the hyphen and spaces
Choose Insert | Merge Field to insert the "initials" field.
You'll see something like this:
<<YourTable:: DateField>> - <<YourTable::initialsField>>
You can select format date for this block of layout text to specify a date format for the merged date field.
In browse mode, you'll see: 11-Nov-09 - BG
All in one field:
Right("0" & Day(datefield) ; 2 ) & "-" & Left(monthname(datefield);3) & "-" & Right(Year(datefield);2) & " - " & initialsfield
Thanks Phil, very helpful. Using the merge on a text block is something I didn't know about. So, in your example the date field & initials field are pre-existing and are necessary to pull the data from somewhere. Seems redundant to have everything on the page twice though. The Help doesn't appear to have anything about hiding fields.
Also, in your second 'All in one field' example, where does this go? if you put it in a text block it just shows it verbatim. Thanks again.
You would put it in a calculation field.
"Seems redundant to have everything on the page twice though. "
Once in separate fields for editing and once as a combined object for viewing?
I wouldn't use either method to display this data on an edit layout myself, I'd just put the two data fields next to each other. I'd use one of the two options I described on report layouts for slightly cleaner printing.
Thanks Phil, in these cases I don't won't people to edit the fields. For example I have a Verify button that when pressed inputs into a field the current date + the user. It's easy to do as a script using the normal date format but needing the EU date format is what is causing grief. Same issue with timestamps in note fields, not much flexibility with the dates. -Derryl
If you are setting the info with a script you can use set field to enter the data into the two separate data fields.
When using set field, the individual data fields do not have to be placed on the current layout to work.
It is possible to modify the "all in one" expression to simply enter the data into a single text field. I'm recommending against that because there are many situations where you may need to continue to treat that data field as a date--not text.
Phil, the timestamping of individual fields works great now. But I am still stuck on the other timestamp. For example I have a log window to record notes on transactions. The process is that before someone types, they hit the button next to it which inserts a timestamp with their initials. This is easy to do as a script if you use the conventional date format. But since you can't format the large text field as a Date field I assume changing the timestamp appearance has to be done with a script.
So it seems that I need a script that
- somehow pulls up the timestamp info (date only)
- converts it into text
- Chops it into pieces and
- re-outputs it in the correct order with the other text concatenated on to it.
Am I on the right track?
You can do this as an auto-enter:
Right("0" & Day(GetCurrentDate) ; 2 ) & "-" & Left(monthname(GetCurrentDate);3) & "-" & Right(Year(GetCurrentDate);2) & " - " & initialsfield
In place of "Initialsfield" put whatever expression you are currently using to add the user's initials.
Note that this is a DATE not a time stamp. You'd have to include a Get (currentTime) somewhere in the above expression if you want the time included as well.
As a script would this go in "Insert Calculated Result"? It says a specified table can not be found. I added the table name and field in front with :: but got more syntax errors. Thanks.