titanium76 Posted November 8, 2007 Share Posted November 8, 2007 I have a field that calculates the date into text: 1/2/2006 changes to January 2, 2006 easy enough, but there are instances where the day may not be available, generally because it was omitted from the document, so I need to be able to manually enter it as January _, 2006... I added a catch to the calc that if the left most character is an "*" then the calculation isn't activated and the "*" is automatically removed when printed.... ex: *January _, 2006 But I would rather do it differently. If I enter 1/_/2006 I get ? ?, ? is there any function that can look at the calc and see if it will fail instead of me using the "*" to force it not to calculate? Link to comment Share on other sites More sharing options...
comment Posted November 8, 2007 Share Posted November 8, 2007 This is a bit confusing. "1/_/2006" is not a date, so you cannot be converting date into text - more like text into text. What is the calculation you're using now? Link to comment Share on other sites More sharing options...
Vaughan Posted November 8, 2007 Share Posted November 8, 2007 I'm nt sure what you mean, or want. FMP date fields cannot handle date ranges like 1/--/2006, they can only work with valid dates. Do you want to enter a date range just for printing, or do you want it to be searchable? (Printing is easy, searchable is much harder.) You'll have to enter the date range into a text field (not a date field) because FMP only works with valid dates. The date range will need to entered in using a fixed, consistent format because you'll create a calculation to parse out the month, day and year values. This parsing-out isn't too difficult. To make it work for searches requires much work. I posted a demo file to the forums years ago that used the Smart Ranges technique to build a date range that can be searched. It might still be around on the Forums, I no longer have a copy. Link to comment Share on other sites More sharing options...
LaRetta Posted November 8, 2007 Share Posted November 8, 2007 (edited) You've lost me, Vaughan. FM works with more than valid dates and can handle range searches, such as 7/*/* and all kinds of partial and range searches now. Edited November 8, 2007 by Guest Link to comment Share on other sites More sharing options...
titanium76 Posted November 8, 2007 Author Share Posted November 8, 2007 Sorry for all the confusion.... We enter fields for real estate documents. Occasionally they are recorded without a day added to it. For example, a Deed would be dated January _ 2007. We need to report that the day is missing by using "_" instead of a number. My current solution is: Case( Left(EntryField;1) = "*";EntryField; ReferenceField = "Date and Time"; Let ( [ posval = Position ( EntryField ; "@" ; 1 ; 1 ); lenval = Length ( EntryField ) ] ; Case(IsEmpty(EntryField); EntryField; TrimReturnsSpaces (TextFormat ( Date2Text(Left(EntryField; posval-1)) & " @ " & Right ( EntryField ; lenval-(posval+1) )); True )) ); ReferenceField = "Date"; Date2Text ( EntryField ); ReferenceField = "Dollar Value"; DollarFormat (EntryField ); ReferenceField = "Text"; TextFormat (EntryField ); ReferenceField = "Consideration"; DollarFormat ( Filter ( EntryField ; "1234567890$.," )) & If(Right ( EntryField ; 1 ) = "c"; " & c") ; TextFormat( EntryField)) I am looking for a way to avoid placing an "*" at the beginning of the string and instead have the calculation know it errors. The results work like this now: 1/2/2006 calculates to January 2, 2006 1/_/2006 calculates to ? ?, ? *1/_/2006 calculates to *1/_/2006 *January _, 2006 calculates to *January _, 2006 Is there a way to do something like: Case(IfError(Date2Text ( EntryField ); EntryField) Link to comment Share on other sites More sharing options...
comment Posted November 8, 2007 Share Posted November 8, 2007 (edited) That doesn't tell me much without knowing what all those custom functions do. And how does the ReferenceField fit into this, and what has "DollarFormat" to do with date formatting??? In any case, assuming EntryField is a Text field, you could try something like this (result is Text) Let ( [ words = Substitute ( EntryField ; [ "/" ; " " ] ; [ "_" ; "none" ] ) ; m = LeftWords ( words ; 1 ) ; d = MiddleWords ( words ; 2 ; 1 ) ; y = RightWords ( words ; 1 ) ; mmmm = MonthName ( Date ( m ; 1 ; 1 ) ) ; dd = Case ( d ; d ; "_" ) ] ; mmmm & " " & dd & ", " & y ) --- Note that there's no error checking here - you could enter "15/45/abc" and the result will be "March 45, abc". Edited November 8, 2007 by Guest Link to comment Share on other sites More sharing options...
Vaughan Posted November 9, 2007 Share Posted November 9, 2007 LaRetta wrote: "FM works with more than valid dates and can handle range searches, such as 7/*/* and all kinds of partial and range searches now." Sure. But as I understand it, the OP wants to be able to enter a *partial* date into a date field, like 1/--/2006 (January 2006) without specifying a day. This comes up a lot in archives, libraries and the such where an item might have an uncertain date: only its year might be known, so it needs to be entered as "--/--/1925". But if a search were to be performed in the database for, say, October 3 1925 you'd want the "--/--/1925" item to be included in the search results as well. FMP's date field does not allow a date range to be entered *as data* though you rightly point out it does as a find request. Link to comment Share on other sites More sharing options...
comment Posted November 9, 2007 Share Posted November 9, 2007 as I understand it, the OP wants to be able to enter a *partial* date into a date field Since OP stated they are able to enter a "*" prefix, the field cannot be a date field - and that's all the 'sherlocking' I'm willing to do until OP clarifies. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6148 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now