Help please, to change the contents of a date field.
Most of the dates show "1995" when they should read "2005"
The date format in field is: m/d/yy.
All records show '05', but when I click in the field,
the date expands to 'm/d/1995'. It should read '2005'.
Can I get some help to do the replace?
Thanks.
-Dennis
--
Dennis Kessler
http://www.denniskessler.com/acupuncture
In article <13dpe0hoohdf4f0@corp.supernews.com>,
dk_
> Help please, to change the contents of a date field.
>
> Most of the dates show "1995" when they should read "2005"
>
> The date format in field is: m/d/yy.
>
> All records show '05', but when I click in the field,
> the date expands to 'm/d/1995'. It should read '2005'.
>
> Can I get some help to do the replace?
>
> Thanks.
>
> -Dennis
One method as follows (there may be others):
Make calcualtion fields for DayDate, MonthDate, YearDate
DayDate = Day(Date)
MonthDate = Month(Date)
YearDate = Year(Date)
results as numbers
make a calculation field field YearCorrect = Year + 10
result as number
Make a new date field DateCorrect
Do replace Field Contents on the DateCorrect field =
MonthDate & "/" & DayDate "/" & YearCorrect
Should work fine
After you have done that, you can delete the old date field and the
calculation fields, and then start using the DateCorrect field for data
entry (rename it as desired)
I suggest that in the field definition for the date field, you impose a
validation requirement to use 4-digit year. That should avoid the
ambiguity in the future.
--
For email, change
Bill Collins
dk_ wrote:
> Help please, to change the contents of a date field.
>
> Most of the dates show "1995" when they should read "2005"
Replace field contents in all records with this calculation:
date( month(datefield); day(datefield); year(datefield)+10 )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles
FileMaker 8 Certified Developer
Member, FileMaker Business Alliance
In article
Bill
> In article <13dpe0hoohdf4f0@corp.supernews.com>,
> dk_
>
> > Help please, to change the contents of a date field.
> >
> > Most of the dates show "1995" when they should read "2005"
> >
> > The date format in field is: m/d/yy.
> >
> > All records show '05', but when I click in the field,
> > the date expands to 'm/d/1995'. It should read '2005'.
> >
> > Can I get some help to do the replace?
> >
> > Thanks.
> >
> > -Dennis
>
> One method as follows (there may be others):
>
> Make calcualtion fields for DayDate, MonthDate, YearDate
>
> DayDate = Day(Date)
> MonthDate = Month(Date)
> YearDate = Year(Date)
>
> results as numbers
>
> make a calculation field field YearCorrect = Year + 10
> result as number
>
> Make a new date field DateCorrect
>
> Do replace Field Contents on the DateCorrect field =
> MonthDate & "/" & DayDate "/" & YearCorrect
>
> Should work fine
>
> After you have done that, you can delete the old date field and the
> calculation fields, and then start using the DateCorrect field for data
> entry (rename it as desired)
>
> I suggest that in the field definition for the date field, you impose a
> validation requirement to use 4-digit year. That should avoid the
> ambiguity in the future.
Oops! ERROR in my original question...
= "1995" should read "1905".
....so I assume all I need to do is change, (in your), explanation,
"Year + 10" to "Year + 100", right???
Thanks.
-Dennis
--
Dennis Kessler
http://www.denniskessler.com/acupuncture
In article <13dph0rc5of1b0@corp.supernews.com>,
Howard Schlossberg
> dk_ wrote:
> > Help please, to change the contents of a date field.
> >
> > Most of the dates show "1995" when they should read "2005"
>
> Replace field contents in all records with this calculation:
>
> date( month(datefield); day(datefield); year(datefield)+10 )
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg
> FM Professional Solutions, Inc. Los Angeles
>
> FileMaker 8 Certified Developer
> Member, FileMaker Business Alliance
Soultion looks simple.
In FMP3, how/where do I enter the 'replace' formula for just the records
that have the year wrong?
The field-name, (in my database), of my date-field is, believe it or not:
"date".
and...
Oops! ERROR in my original question...
= "1995", should read "1905" but the correct date would be '2005'.
....so I assume all I need to do is change, (in your), explanation,
"year(datefield)+10" to "year(datefield)+100", ...right???
....or is there a better way to fix my database?
And, I wonder how that date got entered as '1905 'rather than '2005'.
Because I probably was actually entering dates as '2/5/05', and not typing 4
digits for the year.
Thanks for the help.
-Dennis
--
Dennis Kessler
http://www.denniskessler.com/acupuncture
dk_ wrote:
> In article <13dph0rc5of1b0@corp.supernews.com>,
> Howard Schlossberg
>
>> dk_ wrote:
>>> Help please, to change the contents of a date field.
>>>
>>> Most of the dates show "1995" when they should read "2005"
>> Replace field contents in all records with this calculation:
>>
>> date( month(datefield); day(datefield); year(datefield)+10 )
>
>
> Soultion looks simple.
>
> In FMP3, how/where do I enter the 'replace' formula for just the records
> that have the year wrong?
Get your desired found set, click into the date field, go to the Records
menu, click 'replace field contents', and set the calc.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles
FileMaker 8 Certified Developer
Member, FileMaker Business Alliance
In article <13dph0rc5of1b0@corp.supernews.com>,
Howard Schlossberg
> dk_ wrote:
> > Help please, to change the contents of a date field.
> >
> > Most of the dates show "1995" when they should read "2005"
>
> Replace field contents in all records with this calculation:
>
> date( month(datefield); day(datefield); year(datefield)+10 )
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg
> FM Professional Solutions, Inc. Los Angeles
>
> FileMaker 8 Certified Developer
> Member, FileMaker Business Alliance
I believe I just got it!
Above in your formula, the semicolons (;) should be commas (,), right?
-Dennis
--
Dennis Kessler
http://www.denniskessler.com/acupuncture
On 9/3/07 8:44 PM, in article 13dpl4kk16oe711@corp.supernews.com, "dk_"
> In article <13dph0rc5of1b0@corp.supernews.com>,
> Howard Schlossberg
>
>> dk_ wrote:
>>> Help please, to change the contents of a date field.
>>>
>>> Most of the dates show "1995" when they should read "2005"
>>
>> Replace field contents in all records with this calculation:
>>
>> date( month(datefield); day(datefield); year(datefield)+10 )
>>
>>
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Howard Schlossberg
>> FM Professional Solutions, Inc. Los Angeles
>>
>> FileMaker 8 Certified Developer
>> Member, FileMaker Business Alliance
>
>
>
> I believe I just got it!
>
> Above in your formula, the semicolons (;) should be commas (,), right?
>
> -Dennis
The DATE function apparently uses semicolons as separators - see the
Helpfile under DATE function.
In article <13dpkepov4kjr70@corp.supernews.com>,
Howard Schlossberg
> dk_ wrote:
> > In article <13dph0rc5of1b0@corp.supernews.com>,
> > Howard Schlossberg
> >
> >> dk_ wrote:
> >>> Help please, to change the contents of a date field.
> >>>
> >>> Most of the dates show "1995" when they should read "2005"
> >> Replace field contents in all records with this calculation:
> >>
> >> date( month(datefield); day(datefield); year(datefield)+10 )
> >
> >
> > Soultion looks simple.
> >
> > In FMP3, how/where do I enter the 'replace' formula for just the records
> > that have the year wrong?
>
> Get your desired found set, click into the date field, go to the Records
> menu, click 'replace field contents', and set the calc.
Yep, got it! ...but I had to change the semicolons in your formula to commas,
and also correct my posting mistake, (should have read 1905 dates, not 1995
dates), so I changed your '+10' to '+100'. That did it!!!!
Many thanks for the excellent lesson!
-Dennis
--
Dennis Kessler
http://www.denniskessler.com/acupuncture
In article
> On 9/3/07 8:44 PM, in article 13dpl4kk16oe711@corp.supernews.com, "dk_"
>
>
> > In article <13dph0rc5of1b0@corp.supernews.com>,
> > Howard Schlossberg
> >
> >> dk_ wrote:
> >>> Help please, to change the contents of a date field.
> >>>
> >>> Most of the dates show "1995" when they should read "2005"
> >>
> >> Replace field contents in all records with this calculation:
> >>
> >> date( month(datefield); day(datefield); year(datefield)+10 )
> >
> > I believe I just got it!
> >
> > Above in your formula, the semicolons (;) should be commas (,), right?
>
>
> The DATE function apparently uses semicolons as separators - see the
> Helpfile under DATE function.
In some older, language localised, versions of FileMaker the parameters
for a function are separated by commas while others use a semi-colon
(both version 4 and 5 I have on this computer use commas). From what I
can gather, all newer versions of FileMaker use the semi-colon.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
In article
"David Robins, MD"
> On 9/3/07 8:44 PM, in article 13dpl4kk16oe711@corp.supernews.com, "dk_"
>
>
> > In article <13dph0rc5of1b0@corp.supernews.com>,
> > Howard Schlossberg
> >
> >> dk_ wrote:
> >>> Help please, to change the contents of a date field.
> >>>
> >>> Most of the dates show "1995" when they should read "2005"
> >>
> >> Replace field contents in all records with this calculation:
> >>
> >> date( month(datefield); day(datefield); year(datefield)+10 )
> >>
> >>
> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> Howard Schlossberg
> >> FM Professional Solutions, Inc. Los Angeles
> >>
> >> FileMaker 8 Certified Developer
> >> Member, FileMaker Business Alliance
> >
> >
> >
> > I believe I just got it!
> >
> > Above in your formula, the semicolons (;) should be commas (,), right?
> >
> > -Dennis
>
>
> The DATE function apparently uses semicolons as separators - see the
> Helpfile under DATE function.
>
David,
The Help file for FMP3 3.0v5 on the Mac, uses and shows commas, not
semicolons for the DATE function.
I did try Howard's formula in the Replace dialog box with semicolons and it
would not run. I then changed to commas in the formula, and the replace
worked perfectly.
Thanks for reading.
-Dennis
--
Dennis Kessler
http://www.denniskessler.com/acupuncture
In article <040920071717469089%helpful_harry@nom.de.plume.com>,
Helpful Harry
> In article
>
>
> > On 9/3/07 8:44 PM, in article 13dpl4kk16oe711@corp.supernews.com, "dk_"
> >
> >
> > > In article <13dph0rc5of1b0@corp.supernews.com>,
> > > Howard Schlossberg
> > >
> > >> dk_ wrote:
> > >>> Help please, to change the contents of a date field.
> > >>>
> > >>> Most of the dates show "1995" when they should read "2005"
> > >>
> > >> Replace field contents in all records with this calculation:
> > >>
> > >> date( month(datefield); day(datefield); year(datefield)+10 )
> > >
> > > I believe I just got it!
> > >
> > > Above in your formula, the semicolons (;) should be commas (,), right?
> >
> >
> > The DATE function apparently uses semicolons as separators - see the
> > Helpfile under DATE function.
>
> In some older, language localised, versions of FileMaker the parameters
> for a function are separated by commas while others use a semi-colon
> (both version 4 and 5 I have on this computer use commas). From what I
> can gather, all newer versions of FileMaker use the semi-colon.
>
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)
This is good to know, because although I am running FMP 3.0v5, I do have
FMP 6, which I just haven't been using. I'll move on up one day. ;)
Thanks.
-Dennis
dk_ wrote:
> In article
> "David Robins, MD"
>
>> On 9/3/07 8:44 PM, in article 13dpl4kk16oe711@corp.supernews.com, "dk_"
>>
>>
>>> In article <13dph0rc5of1b0@corp.supernews.com>,
>>> Howard Schlossberg
>>>
>>>> dk_ wrote:
>>>>> Help please, to change the contents of a date field.
>>>>>
>>>>> Most of the dates show "1995" when they should read "2005"
>>>> Replace field contents in all records with this calculation:
>>>>
>>>> date( month(datefield); day(datefield); year(datefield)+10 )
>>>>
>>>>
>>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>> Howard Schlossberg
>>>> FM Professional Solutions, Inc. Los Angeles
>>>>
>>>> FileMaker 8 Certified Developer
>>>> Member, FileMaker Business Alliance
>>>
>>>
>>> I believe I just got it!
>>>
>>> Above in your formula, the semicolons (;) should be commas (,), right?
>>>
>>> -Dennis
>>
>> The DATE function apparently uses semicolons as separators - see the
>> Helpfile under DATE function.
>>
>
> David,
>
> The Help file for FMP3 3.0v5 on the Mac, uses and shows commas, not
> semicolons for the DATE function.
>
> I did try Howard's formula in the Replace dialog box with semicolons and it
> would not run. I then changed to commas in the formula, and the replace
> worked perfectly.
>
> Thanks for reading.
>
> -Dennis
>
Commas were used as delimiters in formulas through 6.
They changed it to semicolons for 7+
Matt
--
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP
My Custom Functions:
http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills
On Sep 4, 7:21 am, Matt Wills
> dk_ wrote:
> > In article
> > "David Robins, MD"
>
> >> On 9/3/07 8:44 PM, in article 13dpl4kk16oe...@corp.supernews.com, "dk_"
> >>
>
> >>> In article <13dph0rc5of...@corp.supernews.com>,
> >>> Howard Schlossberg
>
> >>>> dk_ wrote:
> >>>>> Help please, to change the contents of a date field.
>
> >>>>> Most of the dates show "1995" when they should read "2005"
> >>>> Replace field contents in all records with this calculation:
>
> >>>> date( month(datefield); day(datefield); year(datefield)+10 )
>
> >>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >>>> Howard Schlossberg
> >>>> FM Professional Solutions, Inc. Los Angeles
>
> >>>> FileMaker 8 Certified Developer
> >>>> Member, FileMaker Business Alliance
>
> >>> I believe I just got it!
>
> >>> Above in your formula, the semicolons (;) should be commas (,), right?
>
> >>> -Dennis
>
> >> The DATE function apparently uses semicolons as separators - see the
> >> Helpfile under DATE function.
>
> > David,
>
> > The Help file for FMP3 3.0v5 on the Mac, uses and shows commas, not
> > semicolons for the DATE function.
>
> > I did try Howard's formula in the Replace dialog box with semicolons and it
> > would not run. I then changed to commas in the formula, and the replace
> > worked perfectly.
>
> > Thanks for reading.
>
> > -Dennis
>
> Commas were used as delimiters in formulas through 6.
>
> They changed it to semicolons for 7+
>
> Matt
>
> --
> Free FileMaker Technique Demos:http://www.VirtualVermont.com/FMP
>
> My Custom Functions:http://www.briandunning.com/filemaker-custom-funct ions/results.php?ke...
Interestingly, they're still supported. If you use commas instead of
semi colons in your calculations, FM will convert them to semis
(tested in FM9).
G
In article <1188921061.921934.238850@19g2000hsx.googlegroups.com>, Grip
> On Sep 4, 7:21 am, Matt Wills
> > dk_ wrote:
> > > In article
> > > "David Robins, MD"
> >
> > >> On 9/3/07 8:44 PM, in article 13dpl4kk16oe...@corp.supernews.com, "dk_"
> > >>
> > >>>
> > >>> I believe I just got it!
> > >>>
> > >>> Above in your formula, the semicolons (;) should be commas (,), right?
> > >>
> > >> The DATE function apparently uses semicolons as separators - see the
> > >> Helpfile under DATE function.
> > >
> > > David,
> > >
> > > The Help file for FMP3 3.0v5 on the Mac, uses and shows commas, not
> > > semicolons for the DATE function.
> > >
> > > I did try Howard's formula in the Replace dialog box with semicolons and
> > > it
> > > would not run. I then changed to commas in the formula, and the replace
> > > worked perfectly.
> > >
> > > Thanks for reading.
> >
> > Commas were used as delimiters in formulas through 6.
> >
> > They changed it to semicolons for 7+
>
> Interestingly, they're still supported. If you use commas instead of
> semi colons in your calculations, FM will convert them to semis
> (tested in FM9).
They probably have to be still supported for people still upgrading
older databases or re-using old formulas from the Internet and printed
material. :o)
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)