Populating a date field with contents of 3 other fields

[Brad DuTemple]

I have a database on our webserver that needs to collect a beginning date, and and ending date for a rental period. To try to make it foolproof, I have dynamic javascript pulldowns that only show the valid number of days for a given month in a given year. The database gets populated with these results.

I then need to populate a startdate field by concatenting the contents of the three date fields entered by the web user.

The three fields are yearstart, monthstart, and daystart. I have set the startdate field to be a calculated result, and of date format, with the following calculation;

monthstart & "/" & daystart & "/" & 2000 + yearstart.

No matter what the user enters into the 3 fields, the result of the calculation is invariably Jun 23, 0006.

What am I doing wrong ?

Thanks in advance.

Brad DuTemple

[ZuperZZ]

Hi,

Have you tried this?:

startdate = monthstart & "/" & daystart & "/" & (yearstart+2000)<< the result is a text

Then a field:

xxx=TextToDate(startdate)

Hope it works for you..

ZZ

[Brad DuTemple]

Thanks ZZ!

That _almost_ worked...

I've found that it works if I select the (new) startdatetext field (which is a text based field to hold the result of the concatenation prior to converting it to a date in the rentstart field, and select "Replace..." in the MODE menu.

But with the field set to an auto enter calculation of the same formula,

monthstart & "/" & daystart & "/" & (2000 + yearstart) , I end up with "//2000" rather than "06/22/2000".

I must be missing something obvious, I'm using Filemaker Pro 3, but this database will very soon move to our new G4 webserver which is running FileMaker Pro 5 Unlimited.

Thanks for your help ZZ!

Brad DuTemple

[This message has been edited by Brad DuTemple (edited June 22, 2000).]

[This message has been edited by Brad DuTemple (edited June 22, 2000).]

[filecrazy]

I've done this before using - you already have the Month field, Day field and Year field.

Create a calculation field to concatenate the three text fields:

Date (Text) = Month & "/" & Day & "/" & Year

Make sure the result is set to Text

Then create a second calculation field which will be the real date:

Real Date = TextToDate(Date)

Make sure the result is set to date

Good luck.

[Brad DuTemple]

I really thought this would be a trivial matter, but I think I have a few more gray hairs than I started out with this week.

This is what I have so far;

monthstart (number field, auto populated from web form)

Daystart (number field, auto populated from web form)

yearstart (number field, auto populated from web form)

startdatetext (calculation field, = monthstart & "/" & daystart & "/" & yearstart

startdatetext produces the expected value of 06/23/00

rentstart (date field, auto enter calculation = TextToDate(startdatetext) )

With the above calculation, the renstart field remains empty after the creation of a new record.

I really thought this would be simple...

[Brad DuTemple]

It works! It works!

Thanks for all your help guys. I finally noticed the pop-up menu in the Calculation dialog that says "Calculation result is..."

I set it to Text, since it was set to number, and now all works as I'd hoped.

Thanks again, whoohoo, now I can enjoy my weekend!

Brad DuTemple